3 Replies Latest reply on Sep 10, 2015 4:42 PM by thatJeffSmith-Oracle

    Exporting DDL - Numeric column precision is * (asterisk)

    user557583

      When I export the DDL for a table the precision for the number columns are showing up as *. For example, NUMBER(*,0) even though they are declared with a value of 38 - NUMBER(38,0). How do I override the option so it prints the actual precision?

       

      I need to export the DDL to compare with an model in Erwin and the compare fails due to the mismatch in precision.

       

      Thank you for your help.

       

      ~arvind.

        • 1. Re: Exporting DDL - Numeric column precision is * (asterisk)
          thatJeffSmith-Oracle

          How are you exporting the DDL exactly - there's more than 1 way in SQLDev to generate DDL.

           

          Here's how SQLDev 4.1.1 generates the table DDL in a 12.1.0.2 db when using Tools > Export

           

          DROP TABLE "HR"."TABLE38" cascade constraints;

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

          --  DDL for Table TABLE38

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

            CREATE TABLE "HR"."TABLE38"

             ( "COLUMN1" NUMBER(38,0)

             ) SEGMENT CREATION DEFERRED

            PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

          NOCOMPRESS LOGGING

            TABLESPACE "USERS" ;

          • 2. Re: Exporting DDL - Numeric column precision is * (asterisk)
            user557583

            That is how I use Tools->Export

             

            Mine is a 4.0.2.15 on 11.2.0.1.0. I am getting it as follows:

             

            "BILLING_ALLOCATION_ID" NUMBER(*,0),

              "BILLING_EVENT_ID" NUMBER(*,0),

            • 3. Re: Exporting DDL - Numeric column precision is * (asterisk)
              thatJeffSmith-Oracle

              I can spin up 11gR2 if I have to, but can you get a more recent copy of the tool to test?

               

              Also, what does this spit out:

              select DBMS_METADATA.GET_DDL('TABLE',OBJECT_NAME,OWNER) FROM Dba_objects where owner = :OWNER and object_name = :NAME and object_type = 'TABLE'

                union all

                select dbms_metadata.GET_DEPENDENT_DDL ('COMMENT', TABLE_NAME, OWNER ) FROM

                (select table_name,owner from Dba_col_comments where owner = :OWNER and table_name = :NAME and comments is not null

                union 

                select table_name,owner from sys.Dba_TAB_comments where owner = :OWNER and table_name = :NAME and comments is not null)

                union all

                select DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME, OWNER) FROM

                (select index_name, owner from sys.Dba_indexes where table_owner = :OWNER and table_name = :NAME and generated = 'N'

                minus select index_name, owner from sys.Dba_constraints where owner = :OWNER and table_name = :NAME)

                union all

                select dbms_metadata.GET_DDL ('TRIGGER', trigger_name ,owner ) from Dba_triggers where table_owner = :OWNER and table_name = :NAME

               

              That's what we run to spit out DDL - so the database is responsible for all of this that you see. You can just set session preferences to shape HOW the ddl is generated.