SQL Developer: Database Export Wizard to export DDL and Data as DML

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;