Feb 272010
 

Sometimes, you would want to generate a script that you would like to run repeatedly in many environments for many objects. In such situations, use of Database Export wizard helps you generate DDL and DML (INSERT) script; – you can select object types, specific objects and filter out or restrict the data exported.

  • Step1: Source / Destination – You can choose SQL file location, database connection, and DDL options.

  • Step 2: Types to Export – You can select the type of objects you want to explore.

  • Step 3: Specify Objects – You can select the specific object that you need to export.

  • Step 4: Specify Data – You can apply conditions to data by using a select or filter criteria.

  • Step 5: Export Summary – At this stage you can review and verify the information list on the summary screen. Once you hit Finish, the SQL script is generated and loaded into SQL Worksheet, ready for you to run on any schema.

Everything looks great, simple and most importantly the tool is free of cost – sample script generated by the wizard is below:

--------------------------------------------------------
--  File created - Friday-February-26-2010
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table T1
--------------------------------------------------------

  CREATE TABLE "T1"
   (	"ID" NUMBER
   ) ;

---------------------------------------------------
--   DATA FOR TABLE T1
--   FILTER = none used
---------------------------------------------------
REM INSERTING into T1
Insert into T1 (ID) values (1);
Insert into T1 (ID) values (2);
Insert into T1 (ID) values (3);
Insert into T1 (ID) values (4);
Insert into T1 (ID) values (5);
Insert into T1 (ID) values (6);
Insert into T1 (ID) values (7);
Insert into T1 (ID) values (8);
Insert into T1 (ID) values (9);
Insert into T1 (ID) values (10);

---------------------------------------------------
--   END DATA FOR TABLE T1
---------------------------------------------------
--------------------------------------------------------
--  Constraints for Table T1
--------------------------------------------------------

ALTER TABLE "T1" ADD CONSTRAINT "T1_ID_PK" PRIMARY KEY ("ID") ENABLE;
--------------------------------------------------------
--  DDL for Index T1_ID_PK
--------------------------------------------------------

CREATE UNIQUE INDEX "T1_ID_PK" ON "T1" ("ID")
;

The DDL is not error free, to illustrate the issue, I selected only table T1 to export from my schema. If you run the sample script above as-is, you will receive an error “ORA-00955: name is already used by an existing object”. It is because, the database wizard (SQL Developer Version 2.1.0.63, Build MAIN-63.73) , simply produces one section of DDL statements for constraints and another set for indexes within the script file.

SQL> --------------------------------------------------------
SQL> --  DDL for Index T1_ID_PK
SQL> --------------------------------------------------------
SQL>
SQL>   CREATE UNIQUE INDEX "T1_ID_PK" ON "T1" ("ID")
  2    ;
  CREATE UNIQUE INDEX "T1_ID_PK" ON "T1" ("ID")
                      *
ERROR at line 1:
ORA-00955: name is already used by an existing object

You will have to do a clean-up to make the script error free.

Update: October 24, 2011

I ran this test again on a newer version of SQL Developer (Version 3.0.04 Build MAIN-04.34). You will notice that the script generated by this version places the Indexes section prior to Constraints, which eliminates the above-mentioned error—ORA-00955: name is already used by an existing object—and you do not have perform a clean-up.


--------------------------------------------------------
--  File created - Monday-October-24-2011  
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table T1
--------------------------------------------------------

  CREATE TABLE "T1"
   ( "ID" NUMBER
   ) ;
REM INSERTING into T1
Insert into T1 (ID) values (1);
Insert into T1 (ID) values (2);
Insert into T1 (ID) values (3);
Insert into T1 (ID) values (4);
Insert into T1 (ID) values (5);
Insert into T1 (ID) values (6);
Insert into T1 (ID) values (7);
Insert into T1 (ID) values (8);
Insert into T1 (ID) values (9);
Insert into T1 (ID) values (10);
--------------------------------------------------------
--  DDL for Index T1_ID_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "T1_ID_PK" ON "T1" ("ID")
  ;
--------------------------------------------------------
--  Constraints for Table T1
--------------------------------------------------------

  ALTER TABLE "T1" ADD CONSTRAINT "T1_ID_PK" PRIMARY KEY ("ID") ENABLE;

  9 Responses to “SQL Developer: Database Export Wizard to export DDL and Data as DML”

Comments (9)
  1. Where is this option in sql developer 3?

     
  2. Your second screenshot is where I get stuck. The window is not showing the connections that are available to me. Is there a reason why this is the case. If I try to leave it unselected I get a validation failed error, telling me that the connection is required.

     
    • If you click on Database Export without establishing a connection then the “connection” drop down will be empty. You need to connect to a database first before opening the Database Export Wizard. In the most recent version of SQL Developer, Database Export (or Database Unload) option remains disabled (i.e. grayed out) if you have not established a connection to a database. You can seek additional help at forum: http://forums.oracle.com/forums/forum.jspa?forumID=260 Hope this helps.

       
  3. I’m using Database Unload in SQL developer 3 as you mentioned above. CLOB columns are not included when exporting. Do you know of any way to export CLOBs?

     
  4. Thanks so much for pubishing these easy instructions to extract a DDL from SQL Developer. I just started using this tool and made my day a lot easier.

    Regards,
    Suzanne O’Connor
    Oracle Certified Database Administrator
    SOMS Project – Strategic Offender Management System
    HP Enterprise Services

     
  5. Too bad this does not work with CLOB data

     

 Leave a Reply

(required)

(required)


(Required)

© 2014 Beautiful Data