tables DBMSHP_FUNCTION_INFO, DBMSHP_PARENT_CHILD_INFO, DBMSHP_RUNS missing
Background:
In sqldeveloper 3.1.07 I have a number of views that concatenate the data from several fields to reduce multiple rows to a single row of comma delimited numbers. For example 10 records for Item #1 might contain a list of 10 different assessment files. I reduce them down to A12345, A22637, A32597, A25896, etc. I do the same with other fields, such as mineralization or whatever..
This system is up on our mainframe and has run perfectly for several years and there has not been a problem generating this report till now. The powers that be have decided to lock down the system and now our report tables cannot be deleted or created as necessary. (The views gather the data, the final result is a table) To modify anything has become a tiresome political issue. However, since I have access to the data and MY code (I was the author and maintainer till now), I exported all the data and code and installed it on my local machine - where it was originally developed and where sqldeveloper was used to generate all the views, etc.
The Problem:
To cut to the chase: my concatenation views work perfectly on the mainframe but when I try to run them on my local machine the views are blank. They create properly - no errors, the concatenation function and types compile perfectly - no errors thrown - no messages - no visible warnings - and NO DATA IN THE VIEWS.
When I finally cllicked on Profiles I found the following WARNING
*"Required tables DBMSHP_FUNCTION_INFO,DBMSHP_PARENT_CHILD_INFO,DBMSHP_RUNS missing"*
So my question is -
1) how do I get these tables installed on my local stand alone computer and
2) why does this new version of SQLDEVELOPER need them - or to put it in a different way - why is this the first time in 4 years that I need these tables?
3) why wasnt there an appropriate error message or visible warning.
Any help would be appreciated. Thank you
More Background:
The code for the function and type were borrowed from "somewhere" 4 years ago - the rest is pretty simple. No changes are necessary for any of this as it works perfectly - it is only presented for further information. Database is 11gR2
The code for the view:
CREATE OR REPLACE FORCE VIEW "MDD"."Q_AFILES" ("MDD_NO", "AFILE")
AS
( SELECT mdd_no ,
CONCAT_ALL(CONCAT_EXPR(afile_no, ', ')) AFILE
FROM mdd_afmddcr
GROUP BY mdd_no
) ;
The concat function:
create or replace
FUNCTION "CONCAT_ALL"(
ctx IN concat_expr)
RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE AGGREGATE USING concat_all_ot
;
The type:
create or replace
TYPE "CONCAT_ALL_OT" AS OBJECT (
str VARCHAR2 (4000),
del VARCHAR2 (4000),
STATIC FUNCTION odciaggregateinitialize (
sctx IN OUT concat_all_ot)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT concat_all_ot,
ctx IN concat_expr)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate (
SELF IN concat_all_ot,
returnvalue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT concat_all_ot,
ctx2 concat_all_ot)
RETURN NUMBER);
the Type expression:
create or replace
TYPE "CONCAT_EXPR" AS OBJECT (
str VARCHAR2 (4000),
del VARCHAR2 (4000),
MAP MEMBER FUNCTION f_map RETURN VARCHAR2);