1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

I am having issues with when SELECTing “PROCEDURE”s in Oracle 10g

Discussion in 'General' started by lakshven74, Mar 28, 2012.

  1. lakshven74

    lakshven74 Guest

    “I am able to get results for only non-catalogName procedures in Oracle 10g (i.e. catalogName = <null>):

    (*** There work only for 'GET_OG_NBR_2')
    SELECT * FROM USER_SOURCE WHERE TYPE IN ('PROCEDURE');
    SELECT DBMS_METADATA.GET_DDL('PROCEDURE','GET_OG_NBR_2') FROM USER_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE';
    SELECT DBMS_METADATA.GET_DDL('PROCEDURE','GET_OG_NBR_2','ECOMUSER') FROM USER_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE';
    SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE';

    Ultimately, what I need is the source (i.e. the CREATE statement) of all PROCEDUREs in a schema(s). The PROCEDURES I cannot get the source to are where the ‘catalogName’ is NOT <null>. Take a look at the screen shots I attached – WORKS.png is the only PROCEDURE I can query (i.e. get the CREATE statement). DOES_NOT_WORK.png is just an example of a PROCEDURE that is not returned when running the queries above.

    The goal is to export all TABLES, VIEWS, PROCEDURES, TRIGGERS, SEQUENCES, PACKAGES, etc. from a remote database to a DDL file (.sql file) and then import into a local database.”
     

    Attached Files:

  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your queries of DBMS_METADATA.GET_DDL do not need any table except DUAL:

    SELECT DBMS_METADATA.GET_DDL('PROCEDURE','GET_OG_NBR_2') FROM dual;
    SELECT DBMS_METADATA.GET_DDL('PROCEDURE','GET_OG_NBR_2',' ECOMUSER') FROM dual;

    There is no need to involve USER_OBJECTS or any other table.