Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011
    Posts
    25

    Unanswered: Issue regarding export of DDL scripts of tables in seperate files

    Hey Guys,

    Need some quick help !

    I have 1000 tables of whose DDL scripts (create table) need to be maintained in clearcase (version control & management tool). But getting the scripts of each table seperately in seperate *.sql file is hectic task.

    I'm using SQL Developer tool ,in which I tried to export the DDL scripts of all tables but it is producing one single file containing all scripts.

    Can anybody tell me approach how to get these individual scripts in seperate files with minimum effort so that I can check in them in Clearcase.

    Your effort shall be greatly appreciated.

    Best Regards,
    Vikrant Bishnoi

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's one example - combining DBMS_METADATA (which fetches CREATE TABLE statement) with UTL_FILE (which creates files, one per table).

    First, create a directory (Oracle object) which points to the database server's directory which will contain "export" files. As I'm running the database on my computer, "database server" = "my computer" in this case. In your case, this will probably not be the case so - if you are not a DBA - you'll need to ask him/her to do this part of the job for you.
    Code:
    SQL> show user
    USER is "SYS"
    SQL> create directory my_ddl_dir as 'c:\temp';
    
    Directory created.
    
    SQL> grant read, write on directory my_ddl_dir to scott;
    
    Grant succeeded.
    
    SQL>
    Here's the PL/SQL script (I'm not posting it as a copy/paste from SQL*Plus session as you'd have difficulties in using it for your purposes) (it is stored in the P.SQL file):
    Code:
    declare
      l_file_name    varchar2(50);
      l_ddl          varchar2(10000);
      l_file_handler utl_file.file_type;
    begin
      -- Some intial settings -------------------------------------------
      -- - adjusted Michel Cadot's options posted on OraFAQ:
      -- http://www.orafaq.com/forum/mv/msg/182691/560231/72104/#msg_560231
      
      -- Do you want segment attributes or not?
      dbms_metadata.set_transform_param (dbms_metadata.session_transform, 
                                         'SEGMENT_ATTRIBUTES', false);
      -- Do you want storage parameters or not?
      dbms_metadata.set_transform_param (dbms_metadata.session_transform,
                                         'STORAGE', false);
      -- Do you want tablespace name or not?
      dbms_metadata.set_transform_param (dbms_metadata.session_transform, 
                                         'TABLESPACE', false);
      -- Are constraints inside CREATE table or after in ALTER table statements?
      dbms_metadata.set_transform_param (dbms_metadata.session_transform, 
                                         'CONSTRAINTS_AS_ALTER', true);
      -- Do you want sql terminators (';') or not?
      dbms_metadata.set_transform_param (dbms_metadata.session_transform, 
                                         'SQLTERMINATOR', true);
      -- In the end, a useful one to be always TRUE
      dbms_metadata.set_transform_param (dbms_metadata.session_transform, 
                                         'PRETTY', true);
    									 
      -- OK, let's get started ------------------------------------------
      -- For this example, I'm "exporting" only two tables; you'd omit WHERE clause
      -- (or, possibly, set your own)
      for cur_r in (select table_name from user_tables
                    where table_name in ('EMP', 'DEPT'))
      loop				
        l_file_name := 'ddl_' || cur_r.table_name || '.sql';
      
        l_file_handler := utl_file.fopen('MY_DDL_DIR', l_file_name, 'W');
      
        l_ddl := dbms_metadata.get_ddl('TABLE', cur_r.table_name);
    	
        utl_file.putf(l_file_handler, l_ddl);
        utl_file.fclose(l_file_handler);
      end loop;	
      
    exception
      when utl_file.invalid_path then
         raise_application_error(-20001, 'Invalid PATH for file');
    end;
    /
    Execution:
    Code:
    SQL> @p
    
    PL/SQL procedure successfully completed.
    
    SQL> $type c:\temp\ddl_dept.sql
    
      CREATE TABLE "SCOTT"."DEPT"
       (    "DEPTNO" NUMBER(2,0),
            "DNAME" VARCHAR2(14),
            "LOC" VARCHAR2(13)
       ) ;
    
    SQL> $type c:\temp\ddl_emp.sql
    
      CREATE TABLE "SCOTT"."EMP"
       (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
            "ENAME" VARCHAR2(10),
            "JOB" VARCHAR2(9),
            "MGR" NUMBER(4,0),
            "HIREDATE" DATE,
            "SAL" NUMBER(7,2),
            "COMM" NUMBER(7,2),
            "DEPTNO" NUMBER(2,0)
       ) ;
    
    SQL>
    Depending on DBMS_METADATA settings, you can get different information. Have a look at documentation for more options and their usage.

Posting Permissions

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