4 Replies Latest reply on Jan 30, 2015 5:23 PM by jtomasic

    SQLDeveloper Export does not produce output file with the same table script as in the Developer?

    jtomasic

      I am using SQL Developer Version 4.0.3.16 w/ Oracle dB 10.2 to export (Tools -> Database export -> Export DDL) a table script to an external file.  But the output file produced from the export does not include the same attributes as the table script in Developer.  Why?  Is this a bug or feature?  Is there a way I can produce an external file w/ the exact same DDL as the table script in Developer?  If there is, how?  See example below:

       

      The following is a part of the table script that I exported to an external file from Developer:

       

      CREATE TABLE "HMMS"."EMP"

         ( "EMP_ID" NUMBER(11,0) NOT NULL ENABLE,

        "NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE,

        "ASSIGNED_ORG_ID" VARCHAR2(7 BYTE) NOT NULL ENABLE,

        "RATE_OF_PAY" NUMBER(7,3) NOT NULL ENABLE,

        "LOC_ORG_ID" VARCHAR2(7 BYTE),

        "LABOR_CLASS_CODE" NUMBER(5,0),

      ..

       

      But, the contents of the output file produced from the export are:

      --------------------------------------------------------

      --  File created - Wednesday-January-28-2015  

      --------------------------------------------------------

      --------------------------------------------------------

      --  DDL for Table EMP

      --------------------------------------------------------

        CREATE TABLE "HMMS"."EMP"

         ( "EMP_ID" NUMBER(11,0),

        "NAME" VARCHAR2(50 BYTE),

        "ASSIGNED_ORG_ID" VARCHAR2(7 BYTE),

        "RATE_OF_PAY" NUMBER(7,3),

        "LOC_ORG_ID" VARCHAR2(7 BYTE),

        "LABOR_CLASS_CODE" NUMBER(5,0),

      ...

       

      Why the output file does not contain  "NOT NULL ENABLE" for the first 4 columns?  How can I save the table script to a file without using copy/paste?  Any/all the help on this would be greatly appreciated. 

        • 1. Re: SQLDeveloper Export does not produce output file with the same table script as in the Developer?

           

          I am using SQL Developer Version 4.0.3.16 w/ Oracle dB 10.2 to export (Tools -> Database export -> Export DDL) a table script to an external file.  But the output file produced from the export does not include the same attributes as the table script in Developer.  Why?  Is this a bug or feature?  Is there a way I can produce an external file w/ the exact same DDL as the table script in Developer?  If there is, how?  See example below:

           

          The following is a part of the table script that I exported to an external file from Developer:

           

          CREATE TABLE "HMMS"."EMP"

             ( "EMP_ID" NUMBER(11,0) NOT NULL ENABLE,

            "NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE,

            "ASSIGNED_ORG_ID" VARCHAR2(7 BYTE) NOT NULL ENABLE,

            "RATE_OF_PAY" NUMBER(7,3) NOT NULL ENABLE,

            "LOC_ORG_ID" VARCHAR2(7 BYTE),

            "LABOR_CLASS_CODE" NUMBER(5,0),

          ..

           

          But, the contents of the output file produced from the export are:

          --------------------------------------------------------

          --  File created - Wednesday-January-28-2015 

          --------------------------------------------------------

          --------------------------------------------------------

          --  DDL for Table EMP

          --------------------------------------------------------

            CREATE TABLE "HMMS"."EMP"

             ( "EMP_ID" NUMBER(11,0),

            "NAME" VARCHAR2(50 BYTE),

            "ASSIGNED_ORG_ID" VARCHAR2(7 BYTE),

            "RATE_OF_PAY" NUMBER(7,3),

            "LOC_ORG_ID" VARCHAR2(7 BYTE),

            "LABOR_CLASS_CODE" NUMBER(5,0),

          ...

           

          Why the output file does not contain  "NOT NULL ENABLE" for the first 4 columns?  How can I save the table script to a file without using copy/paste?  Any/all the help on this would be greatly appreciated.

          Likely because you only selected TABLE and did not also select CONSTRAINTS.

           

          If I export the table ddl from the nav tree the export includes the constraints. And if I export the way you did and select both TABLE and CONSTRAINTS I get EXACTLY the same output as from the nav tree:

          --------------------------------------------------------
          --  File created - Thursday-January-29-2015  
          --------------------------------------------------------
          --------------------------------------------------------
          --  DDL for Table EMP_FORUM
          --------------------------------------------------------

            CREATE TABLE "SCOTT"."EMP_FORUM"
             ( "EMP_ID" NUMBER(11,0),
          "NAME" VARCHAR2(50 BYTE),
          "ASSIGNED_ORG_ID" VARCHAR2(7 BYTE),
          "RATE_OF_PAY" NUMBER(7,3),
          "LOC_ORG_ID" VARCHAR2(7 BYTE),
          "LABOR_CLASS_CODE" NUMBER(5,0)
             ) SEGMENT CREATION DEFERRED
            PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
            TABLESPACE "USERS" ;
          --------------------------------------------------------
          --  Constraints for Table EMP_FORUM
          --------------------------------------------------------

            ALTER TABLE "SCOTT"."EMP_FORUM" MODIFY ("EMP_ID" NOT NULL ENABLE);

            ALTER TABLE "SCOTT"."EMP_FORUM" MODIFY ("NAME" NOT NULL ENABLE);

            ALTER TABLE "SCOTT"."EMP_FORUM" MODIFY ("ASSIGNED_ORG_ID" NOT NULL ENABLE);

            ALTER TABLE "SCOTT"."EMP_FORUM" MODIFY ("RATE_OF_PAY" NOT NULL ENABLE);

          • 2. Re: SQLDeveloper Export does not produce output file with the same table script as in the Developer?
            jtomasic

            Thank you very much for your timely response. My output file did included the Constraints section for the exported table but it did not include "NOT NULL ENABLE" attributes for those columns as expected.  Just to be sure, I ran the export again after checking the box for Constraints as Alters(only for viewing) in Tools -> Preferences -> Database -> utilities -> Export. But, still received the same results below(not the same results as shown in your response):


            {code}

            --  DDL for Table EMP

            --------------------------------------------------------

              CREATE TABLE "HMMS"."EMP"

               ( "EMP_ID" NUMBER(11,0),

              "NAME" VARCHAR2(50 BYTE),

              "ASSIGNED_ORG_ID" VARCHAR2(7 BYTE),

              "RATE_OF_PAY" NUMBER(7,3),

              "LOC_ORG_ID" VARCHAR2(7 BYTE),

              "LABOR_CLASS_CODE" NUMBER(5,0),

            ...

               ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

              STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

              PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

              TABLESPACE "HMMS_DATA" ;

            ...

               COMMENT ON TABLE "HMMS"."EMP"  IS 'Employee information.  Altered 5/7/13 for start and stop time.  Added ''No longer used.'' to the comment of the leave columns.';

            --------------------------------------------------------

            --  DDL for Index EMP_PK

            --------------------------------------------------------

             

              CREATE UNIQUE INDEX "HMMS"."EMP_PK" ON "HMMS"."EMP" ("EMP_ID")

              PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS

              STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

              PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

              TABLESPACE "HMMS_INDX" ;

            --------------------------------------------------------

            --  Constraints for Table EMP

            --------------------------------------------------------

             

              ALTER TABLE "HMMS"."EMP" ADD CONSTRAINT "EMP_PK" PRIMARY KEY ("EMP_ID")

              USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS

              STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

              PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

              TABLESPACE "HMMS_INDX"  ENABLE;

            --------------------------------------------------------

            --  Ref Constraints for Table EMP

            --------------------------------------------------------

             

              ALTER TABLE "HMMS"."EMP" ADD CONSTRAINT "EMP_ORG_FK" FOREIGN KEY ("ASSIGNED_ORG_ID")

               REFERENCES "HMMS"."ORG" ("ORG_ID") ENABLE;

             

            {code}

             

            Perhaps the developer util does not work for Oracle instant client... Any thoughts, suggestions, or other tips on this.

            • 3. Re: SQLDeveloper Export does not produce output file with the same table script as in the Developer?

              The results I posted were for version 4.0.2.15.21 using the thin driver.

               


              • 4. Re: SQLDeveloper Export does not produce output file with the same table script as in the Developer?
                jtomasic

                Apparently, the export util produces different results based on the Developer version used.  Hopefully, someone out there w/ version 4.0.3 will shed light on this.  Thanks.