This discussion is archived
2 Replies Latest reply: Apr 8, 2013 9:05 AM by JoyceScapicchio RSS

Statement terminator problems when exporting data with SQL Developer 3.2

860923 Newbie
Currently Being Moderated
I've ran across what appears to be a bug with SQL Developer 3.2.20.09. If someone can let me know if this is indeed a bug, or just something I need to configure differently, please let me know.

The problem is related to exporting a database as a SQL script, and terminator characters at the end of "create" statements, especially when columns have comments, and problems that occur when using SQLPlus to run the export script to create the tables. With the old SQL Developer 1.5.4, with the "Terminator" and "Pretty Print" options checked, statements like the following are generated:

--------------------------------------------------------
-- DDL for Type NUM_ARRAY
--------------------------------------------------------

CREATE OR REPLACE TYPE "NUM_ARRAY"
IS TABLE OF NUMBER(20)


/

--------------------------------------------------------
-- DDL for Sequence MYTABLE_SEQ
--------------------------------------------------------

CREATE SEQUENCE "MYTABLE_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;

--------------------------------------------------------
-- DDL for Table MYTABLE
--------------------------------------------------------

CREATE TABLE "MYTABLE"
(     "MYTABLE_ID" NUMBER,
     "COL2" NUMBER,
     "COL3" NUMBER
) ;

--------------------------------------------------------
-- DDL for Table ANOTHERTABLE
--------------------------------------------------------

CREATE TABLE "ANOTHERTABLE"
(     "ANOTHERTABLE_ID" NUMBER,
     "COL2" VARCHAR2(1024),
     "COL3" VARCHAR2(1024)
) ;


COMMENT ON COLUMN "ANOTHERTABLE"."ANOTHERTABLE_ID" IS 'This is a comment.';


When I then run the script using SQLPlus, everything works fine. However, with SQL Developer 3.2.20.09, with the same options enabled, the same statements are generated like this:

--------------------------------------------------------
-- DDL for Type NUM_ARRAY
--------------------------------------------------------

CREATE OR REPLACE TYPE "NUM_ARRAY"
IS TABLE OF NUMBER(20)
/

/

--------------------------------------------------------
-- DDL for Sequence MYTABLE_SEQ
--------------------------------------------------------

CREATE SEQUENCE "MYTABLE_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;
/

--------------------------------------------------------
-- DDL for Table MYTABLE
--------------------------------------------------------

CREATE TABLE "MYTABLE"
(     "MYTABLE_ID" NUMBER,
     "COL2" NUMBER,
     "COL3" NUMBER
) ;
/

--------------------------------------------------------
-- DDL for Table ANOTHERTABLE
--------------------------------------------------------

CREATE TABLE "ANOTHERTABLE"
(     "ANOTHERTABLE_ID" NUMBER,
     "COL2" VARCHAR2(1024),
     "COL3" VARCHAR2(1024)
) ;


COMMENT ON COLUMN "ANOTHERTABLE"."ANOTHERTABLE_ID" IS 'This is a comment.';
/

Notice all of the extra slashes in there. If a slash is not used as a statement terminator, SQLPlus treats slashes as a command to repeat the last SQL statement, which causes many errors about tables or sequences already existing. So, I tried removing the "Terminator" flag from the export options. This lead to statements that looked a bit more promising:

--------------------------------------------------------
-- DDL for Type NUM_ARRAY
--------------------------------------------------------

CREATE OR REPLACE TYPE "NUM_ARRAY"
IS TABLE OF NUMBER(20)
/

--------------------------------------------------------
-- DDL for Sequence MYTABLE_SEQ
--------------------------------------------------------

CREATE SEQUENCE "MYTABLE_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE
/

--------------------------------------------------------
-- DDL for Table MYTABLE
--------------------------------------------------------

CREATE TABLE "MYTABLE"
(     "MYTABLE_ID" NUMBER,
     "COL2" NUMBER,
     "COL3" NUMBER
)
/

--------------------------------------------------------
-- DDL for Table ANOTHERTABLE
--------------------------------------------------------

CREATE TABLE "ANOTHERTABLE"
(     "ANOTHERTABLE_ID" NUMBER,
     "COL2" VARCHAR2(1024),
     "COL3" VARCHAR2(1024)
)


COMMENT ON COLUMN "ANOTHERTABLE"."ANOTHERTABLE_ID" IS 'This is a comment.'
/

The big problem, though, is in the statement for the table with a comment. Notice that there are two statements, but there is not a semicolon after either of them. This unfortunately causes the "COMMENT" statement to be appended to the "CREATE TABLE" statement before being executed, which causes the table to not be created (which causes even more errors later on when the script attempts to populate the table with data).

So, it would appear that this is a bug, but I'm not sure. Is there a way I can configure the export options to make SQL Developer export these statements like it used to in older versions?

Thanks,

-Bill

Legend

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