This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Feb 26, 2013 10:10 AM by vlethakula Go to original post RSS
  • 15. Re: Extract DDL for all objects separately !
    User286067 Journeyer
    Currently Being Moderated
    riedelme wrote:
    The OP wants the DDL for every table in different files. How do you put the output from export, datapump, etc. in different files?

    SQL*Developer can of course save to different files but I would not want to do it manually one at a time for 3000 tables.
    I know what he wants, I read the thread, Have you used this option via sql developer? if you have, you will know that there are many many options one of which is one file per object. Not rocket science really, just choose the right option. If you use a single file option then yeah you will run into issues.


    Raj
  • 16. Re: Extract DDL for all objects separately !
    vlethakula Expert
    Currently Being Moderated
    Try something like below
    DATAPUMP_DIR is my directory name in dba_directories

    DECLARE
    CURSOR csr IS
    SELECT object_type
    , object_name ,owner
    FROM All_Objects
    WHERE object_type IN ('SEQUENCE',
    'PROCEDURE',
    'DATABASE LINK',
    'PACKAGE',
    'PACKAGE BODY',
    'MATERIALIZED VIEW',
    'TABLE',
    'INDEX',
    'VIEW',
    'FUNCTION')
    AND owner = 'SCOTT';
    out UTL_FILE.file_type;
    BEGIN
    FOR c IN csr LOOP
    out := UTL_FILE.fopen('DATAPUMP_DIR',c.object_name||'_'||c.object_type||'.sql','W');
    UTL_FILE.put_line(out, DBMS_METADATA.get_ddl(c.object_type, c.object_name,c.owner));
    UTL_FILE.fclose(out);
    END LOOP;
    END;

    Edited by: vlethakula on Feb 26, 2013 10:10 AM
1 2 Previous Next

Legend

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