1 2 Previous Next 16 Replies Latest reply: Feb 26, 2013 12:10 PM by vlethakula Go to original post RSS
      • 15. Re: Extract DDL for all objects separately !
        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.

        • 16. Re: Extract DDL for all objects separately !
          Try something like below
          DATAPUMP_DIR is my directory name in dba_directories

          CURSOR csr IS
          SELECT object_type
          , object_name ,owner
          FROM All_Objects
          WHERE object_type IN ('SEQUENCE',
          'DATABASE LINK',
          'PACKAGE BODY',
          AND owner = 'SCOTT';
          out UTL_FILE.file_type;
          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));
          END LOOP;

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