Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Aug 2004
    Posts
    24

    Unanswered: using dbms_metadata package in oracle

    I want to extract ddl's for all objects(procedures, functions, triggers, packages) on a separate file.

    There are about 300 procedures, 400 functions. I assume, I can use dbms_metadata pkg, but I will have to hard code all the procedure names / function names in order to get in a separate file.

    for ex:
    1) spool c:\procedures\set_rank.sql

    select dbms_metadata.get_ddl('PROCEDURE','SET_RANK','ABC' ) from dual;

    spool off;
    2) spool c:\procedures\set_max_rank.sql

    select dbms_metadata.get_ddl('PROCEDURE','SET_MAX_RANK',' ABC') from dual;

    spool off;


    Is there any other way can I do this in ORACLE?

    Thanks in advance, Madhavi

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You want to do some "SQL from SQL":

    1) Create this script called get_proc.sql:
    Code:
    spool &1..sql
    select dbms_metadata.get_ddl('PROCEDURE','&1','ABC' ) from dual;
    spool off;
    2) Run this:
    Code:
    set pagesize 0 verify off feedback off
    
    spool bigscript.sql
    
    select '@getproc ' || object_name
    from user_objects
    where object_type = 'PROCEDURE';
    
    spool off
    3) Run this:
    Code:
    @bigscript

  3. #3
    Join Date
    Aug 2004
    Posts
    24
    Cooooooooooooool.....

    Thanks a lot.... Madhavi

  4. #4
    Join Date
    Aug 2004
    Posts
    24
    This is working for one schema and how can I do this for all schema's?

    Thanks, Madhavi
    Last edited by madhavi_m; 09-15-04 at 16:25.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Use ALL_OBJECTS instead of USER_OBJECTS, and pass the OWNER as the second parameter.

  6. #6
    Join Date
    Aug 2004
    Posts
    24
    I tried this (pasted the code below) and it was success. But, it is creating 5 to 6 copies of duplicate code for each procedure in extracted file. What is the problem?

    1) getproc.sql as follows:
    ******************************************
    spool &1..sql

    select dbms_metadata.get_ddl('PROCEDURE','&1','&2') from all_objects where object_type = 'PROCEDURE';

    spool off;
    ******************************************
    2) then ran this:
    ******************************************
    spool bigscript.sql

    select '@getproc '|| object_name, owner from all_objects where object_type = 'PROCEDURE' and owner not in ('SYS', 'SYSTEM');
    spool off;
    ********************************************

    3)@bigscript.sql

    Is there any wrong with the code?

    I appreciate your time, Madhavi
    Last edited by madhavi_m; 09-15-04 at 17:12.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I would have included the schema name in the final spool filename to diffentiate OWNER_X.TABLE_A from OWNER_Y.TABLE_A.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This line:

    select dbms_metadata.get_ddl('PROCEDURE','&1','&2') from all_objects where object_type = 'PROCEDURE';

    should be:

    select dbms_metadata.get_ddl('PROCEDURE','&1','&2') from DUAL;

  9. #9
    Join Date
    Aug 2004
    Posts
    24
    Thanks Andrew...

    Yes, I just figured out and It is working perfect. I could get all 300 procedures to a folder on my local directory.

    I will be doing the same thing for Functions, Packages and Triggers

    1. getFunc.sql / getPkg.sql / getTrigg.sql
    2. spool bigscript and then run the bigscript

    Now, I want to run this whole process for Procedures, Functions, Packages, Triggers from the command line. As I have to launch this via the command line using some (TAJ - The Argent Job) scheduler.

    How can I achieve this?

    I really appreciate your response.
    Last edited by madhavi_m; 09-16-04 at 10:13.

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Rather than have separate scripts for functions, etc., you can make it more general using object_type as 3rd parameter:
    Code:
    spool &1..sql
    select dbms_metadata.get_ddl('&3','&1','&2' ) from dual;
    spool off;
    Then:
    Code:
    select '@getobject ' || object_name || ' ' || object_owner || ' ' || object_type
    from user_objects
    where object_type in ('PROCEDURE','FUNCITON','TRIGGER');
    I don't know what TAJ is, but the command line version would be:
    Code:
    sqlplus username/password @bigscript

  11. #11
    Join Date
    Aug 2004
    Posts
    24
    That's working perfect.

    The refined code is as follows:
    *****************************************
    1) Create the script called getObject.sql:
    Code:

    Spool &2..&3..&1..sql

    Select dbms_metadata.get_ddl('&3','&1','&2' ) from dual;

    Spool off;

    ******************************************
    Then, Ran this Code:

    Spool bigscript.sql

    SELECT '@getobject ' || OBJECT_NAME || ' ' || OWNER || ' ' || OBJECT_TYPE
    FROM ALL_OBJECTS
    WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION','TRIGGER',’PACKAGE’) AND OWNER NOT IN (‘SYS’,’SYSTEM’);

    Spool off;

    ******************************************

    Ran the following code via command line:

    SQLPLUS username/password @SID @bigscript
    *******************************************

    On the command line, its giving me an error: unable to open the file. How can make the file available?

    Thanks, Madhavi

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Maybe you need the path:

    SQLPLUS username/password @SID @/usr/me/mystuff/bigscript

    (something like that)

  13. #13
    Join Date
    Aug 2004
    Posts
    24
    I tried that, now it is not able open 'getObject' file in the bigscript.
    I gave the absolute path for getObject even then it is giving the error: unable to open the file.

    Thanks, Madhavi

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Use @@ instead of @ in bigscript.sql - that will look for a file in the same directory as bigscript.sql

  15. #15
    Join Date
    Aug 2004
    Posts
    24
    Thanks Andrew.....for giving quick answers to my questions

    Any idea on extracting ddl's for each object on a separate file locally in SQL Server?
    Does SQL Server has any API's like Oracle's dbms_metadata?

    Or can we use SQL enterprise manager to do this job?

    Thanks, Madhavi.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •