This discussion is archived
6 Replies Latest reply: Oct 30, 2012 9:31 AM by grumpygeologist RSS

tables DBMSHP_FUNCTION_INFO, DBMSHP_PARENT_CHILD_INFO, DBMSHP_RUNS missing

grumpygeologist Newbie
Currently Being Moderated
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);
  • 1. Re: tables DBMSHP_FUNCTION_INFO, DBMSHP_PARENT_CHILD_INFO, DBMSHP_RUNS missing
    rp0428 Guru
    Currently Being Moderated
    >
    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.
    >
    Since this has nothing to do with sql developer please mark the question ANSWERED. Then, if the below does not answer your question repost in the database - general forum
    General Questions

    The tables mentioned in the subject are part of Oracle's hierarchical profiler. They are NOT part of the standard Oracle installation. So when you moved everything to your own pc and installed Oracle the tables did not get created.
    >
    1) how do I get these tables installed on my local stand alone computer and
    >
    You need to run the /rdbms/admin/dbmshptab.sql script to create them.

    See this Oracle-base article for information about the profiler, the script and tables and how they are used.
    http://www.oracle-base.com/articles/11g/plsql-hierarchical-profiler-11gr1.php
    >
    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?
    >
    sql developer doesn't need them at all. If you create profiles that reference these tables then those profiles will be invalid unless the tables are available.

    Why are you 'clicking' on profiles if you aren't using them?
    >
    3) why wasnt there an appropriate error message or visible warning.
    >
    There was - you were told the tables are missing.

    Repost in the appropriate forum if you need more help after that.
  • 2. Re: tables DBMSHP_FUNCTION_INFO, DBMSHP_PARENT_CHILD_INFO, DBMSHP_RUNS missing
    grumpygeologist Newbie
    Currently Being Moderated
    Thank you for your answer

    Why was I looking in profiles? - because I looked everywhere else because no error or warning of any kind was displayed and all components compiled successfully. Had I not poked around I would never have had the information to ask this question

    Why did I put the question here? - because the only thing that changed between the last time I did this and now was the installation of the latest SQL Developer version

    Lastly - I never used or knew about the profiler when I developed this program four years ago - so why all of a sudden did it come into play now? - see comment 2 above.

    Anyway - thank you for your help
  • 3. Re: tables DBMSHP_FUNCTION_INFO, DBMSHP_PARENT_CHILD_INFO, DBMSHP_RUNS missing
    grumpygeologist Newbie
    Currently Being Moderated
    The real answer to my question should have simply been - the concat function is not allowed in 11gR2 - LISTAGG s the appropriate replacement. Seems our server is only 10g and that's why it still works there. My local testbed is 11gR2
  • 4. Re: tables DBMSHP_FUNCTION_INFO, DBMSHP_PARENT_CHILD_INFO, DBMSHP_RUNS missing
    Gary Graham Expert
    Currently Being Moderated
    Great that you got it worked out. If you would have included a bit more detail in your code, specifically a WM_CONCAT reference, you might have gotten a better reply here:

    Re: ORA-00904 w/"RMAN Backup/Recovery"->"Backup Sets" in 11gR2 - WM_CONCAT
  • 5. Re: tables DBMSHP_FUNCTION_INFO, DBMSHP_PARENT_CHILD_INFO, DBMSHP_RUNS missing
    rp0428 Guru
    Currently Being Moderated
    >
    The real answer to my question should have simply been - the concat function is not allowed in 11gR2 - LISTAGG s the appropriate replacement. Seems our server is only 10g and that's why it still works there. My local testbed is 11gR2
    >
    Those comments are so far off base I can't even imagine how you arrived at those conclusions.

    1. You never mentioned a 10g system - you talked about a mainframe
    >
    This system is up on our mainframe and has run perfectly for several years
    >

    2. The Oracle concat function is still alive and well in 11g - nothing you posted shows you are using that function
    http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions033.htm#i77004
    >
    Purpose

    CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is in the same character set as char1. Its data type depends on the data types of the arguments.
    >
    3. The CONCAT_ALL function that you posted is YOUR function, not Oracle's. It is a custom aggregate function written using the Data Cartridge extensions. That function will still work in 11g.
    The concat function:
    create or replace
    FUNCTION "CONCAT_ALL"(
    ctx IN concat_expr)
    RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE AGGREGATE USING concat_all_ot
    ;
    Your questions were these
    >
    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.
    >
    Providing an answer of 'the concat function is not allowed in 11gR2' to those questions would have been absurd! The concat function has NOTHING to do with your missing tables.

    I told you the reason your tables are missing. They are only created when you run the script I referred you to. Someone ran them on your current system and created hierarchical profiles that used those tables. You DID NOT run that script on 11g so the tables were not created but you tried to reference the profiles that used them and got the error message about the tables being missing.

    All of your questions were answered and the answers have NOTHING to do with concatenation of any type.
  • 6. Re: tables DBMSHP_FUNCTION_INFO, DBMSHP_PARENT_CHILD_INFO, DBMSHP_RUNS missing
    grumpygeologist Newbie
    Currently Being Moderated
    I appreciate the help offered by all

    1. sorry, but I was of the impression our mainframe was 11g - My fault - I should have double checked

    2. I provided all the code that was involved

    3. I ran the script to build the 3 "missing" tables. I logged on as sys and found them there - all three were empty. The point is, I know they are there - however, SQLDEVELOPER STILL shows all 3 as still missing. This is on my local machine

    4. although my concat_all function works with 10g it didn't produce results with 11gR2 - MY web searches indicated that whatever I was doing didn't work with 11g and that Listagg was the way to go. That's what I did and it produced the results I needed. I also know that LISTAGG, being a new feature, will not work with 10g.

    Anyway, I'm done here.

    rp0428: Perhaps you should remember that you are HELPING? users that are probably not as smart or as wise as you - so perhaps more gentle suggestions and ratchet down the sarcasm just a notch. You sound like some IT people we have to deal with - and they wonder why we hate to use them!!! WHATEVER. Good Bye

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points