5 Replies Latest reply on Mar 4, 2019 3:29 PM by User647459-Oracle

    How do I change max comments size duding ddl import.

    User647459-Oracle

      hi all,

      I found an issue trying to import a table into sql developer data modeler 18.0.4.

      what is the max size comments for an object duding ddl import?

       

      If I add the table then click on the table properties I can add the entire comment to the table.

       

      steps to recreate:

      file->import->ddl file

      I add a file: c:\sample.sql

      I select a database Oracle database 12cR2

      I get errors during the import and none of the comments are added.

      error is Nor recognized statements

       

      the content of sample.sql:

       

      CREATE TABLE VAT_EXPORT_STG
      (
        SEQ_NO                NUMBER (15)  NOT NULL ,
        VAT_REGION            NUMBER (4)  NOT NULL ,
        VAT_CODE              VARCHAR2 (6)  NOT NULL ,
        ACTIVE_DATE           DATE  NOT NULL ,
        ACTION_TYPE           VARCHAR2 (15)  NOT NULL ,
        BASE_EXTRACTED_IND    VARCHAR2 (1)  NOT NULL ,
        CUSTOM1_EXTRACTED_IND VARCHAR2 (1) ,
        CUSTOM2_EXTRACTED_IND VARCHAR2 (1) ,
        CUSTOM3_EXTRACTED_IND VARCHAR2 (1) ,
        CUSTOM4_EXTRACTED_IND VARCHAR2 (1) ,
        PROCESS_ID            NUMBER (15) ,
        TRANSACTION_DATETIME  DATE  NOT NULL
      )
        INITRANS 6
        TABLESPACE RETAIL_DATA
      /

      COMMENT ON TABLE VAT_EXPORT_STG is 'This is table is a staging table for vat records as they wait to be extracted. This table is automatically inserted into, as records are processed by Oracle Retail. This table will be scanned by a daily batch job to extract new, updated and deleted of approved sellable items.'
      /

      COMMENT ON COLUMN VAT_EXPORT_STG.SEQ_NO is 'The sequence in which the records are placed in the table.'
      /

      COMMENT ON COLUMN VAT_EXPORT_STG.VAT_REGION is 'Contains the unique identifying number for the VAT region in the system.'
      /

      COMMENT ON COLUMN VAT_EXPORT_STG.VAT_CODE is 'Contains the alphanumeric identification for the VAT code.'
      /

      COMMENT ON COLUMN VAT_EXPORT_STG.ACTIVE_DATE is 'The date on which the VAT rate becomes active.'
      /

      COMMENT ON COLUMN VAT_EXPORT_STG.ACTION_TYPE is 'Indicates if the vat record has been added, changed or deleted. Valid values are ''vatcre'', ''vatmod'', and ''vatdel''. '
      /

      COMMENT ON COLUMN VAT_EXPORT_STG.BASE_EXTRACTED_IND is 'Indicates whether the record has been extracted or not. Upon record insert, this will be set to ''N''. After running the batch job, this will then be set to ''Y''.'
      /

      COMMENT ON COLUMN VAT_EXPORT_STG.CUSTOM1_EXTRACTED_IND is 'Indicates whether the record has been extracted or not for a custom extraction.'
      /

      COMMENT ON COLUMN VAT_EXPORT_STG.CUSTOM2_EXTRACTED_IND is 'Indicates whether the record has been extracted or not for a custom extraction.'
      /

      COMMENT ON COLUMN VAT_EXPORT_STG.CUSTOM3_EXTRACTED_IND is 'Indicates whether the record has been extracted or not for a custom extraction.'
      /

      COMMENT ON COLUMN VAT_EXPORT_STG.CUSTOM4_EXTRACTED_IND is 'Indicates whether the record has been extracted or not for a custom extraction.'
      /

      COMMENT ON COLUMN VAT_EXPORT_STG.PROCESS_ID is 'Process ID where the record belongs to.'
      /

      COMMENT ON COLUMN VAT_EXPORT_STG.TRANSACTION_DATETIME is 'Time of record creation.'
      /

       

      The logging page has the following error

      SEVERE2305288oracle.dbtools.crest.util.logging.Logger

      Unable to find required Structured Type: approved

        • 1. Re: How do I change max comments size duding ddl import.
          Dave Schleis

          Hello.

           

          Using version 18.4.0.339, I copied the text you provided into a file and imported that file into a new relational model without error.

           

          This is not about the length of the comment.

           

          Can you get other DDL files to import?

          What if you remove the comments? Does it work then?

           

          I am not an Oracle employee and I not have knowledge of the inner workings of the file parser, but defining the boundaries of the issue cannot hurt.

          • 2. Re: How do I change max comments size duding ddl import.
            User647459-Oracle

            Hi Dave.

            Thanks for your reply!. I am also using the same version of the product.

            I am incredibly surprised that it worked for you. I actually went back and tested this same file with several versions and they all have the same result. even on version 4.1.3.

             

            To follow up on your question:

            yes the table imports just fine

             

            If I take out the extra comments on the table comment, from where the error is, I get no errors; meaning

            OLD:

            COMMENT ON TABLE VAT_EXPORT_STG is 'This is table is a staging table for vat records as they wait to be extracted. This table is automatically inserted into, as records are processed by Oracle Retail. This table will be scanned by a daily batch job to extract new, updated and deleted of approved sellable items.'

            /

            CHANGED:

            COMMENT ON TABLE VAT_EXPORT_STG is 'This is table is a staging table for vat records as they wait to be extracted. This table is automatically inserted into, as records are processed by Oracle Retail. This table will be scanned by a daily batch job to extract new, updated and deleted.'

            /

             

            Notice I removed  " of approved sellable items"

            if I do this the error goes away, however when I go into the table properties and click on the rdbms comments, it is empty.

            the rest of the column comments show up

             

            here is the error I get on the screen:

            Oracle SQL Developer Data Modeler 18.4.0.339.1532
            Oracle SQL Developer Data Modeler Import Log
            Date and Time: 2019-02-28 07:53:42 CST
            Design Name: Untitled_1
            RDBMS : Oracle Database 12cR2

              All Statements:   13
              Imported Statements:  1
              Failed Statements:   12

              COMMENT ON COLUMN VAT_EXPORT_STG.SEQ_NO is 'The sequence in which the records are placed in the table.'
              COMMENT ON COLUMN VAT_EXPORT_STG.VAT_REGION is 'Contains the unique identifying number for the VAT region in the system.'
              COMMENT ON COLUMN VAT_EXPORT_STG.VAT_CODE is 'Contains the alphanumeric identification for the VAT code.'
              COMMENT ON COLUMN VAT_EXPORT_STG.ACTIVE_DATE is 'The date on which the VAT rate becomes active.'
              COMMENT ON COLUMN VAT_EXPORT_STG.ACTION_TYPE is 'Indicates if the vat record has been added, changed or deleted. Valid values are ''vatcre'', ''vatmod'', and ''vatdel''. '
              COMMENT ON COLUMN VAT_EXPORT_STG.BASE_EXTRACTED_IND is 'Indicates whether the record has been extracted or not. Upon record insert, this will be set to ''N''. After running the batch job, this will then be set to ''Y''.'
              COMMENT ON COLUMN VAT_EXPORT_STG.CUSTOM1_EXTRACTED_IND is 'Indicates whether the record has been extracted or not for a custom extraction.'
              COMMENT ON COLUMN VAT_EXPORT_STG.CUSTOM2_EXTRACTED_IND is 'Indicates whether the record has been extracted or not for a custom extraction.'
              COMMENT ON COLUMN VAT_EXPORT_STG.CUSTOM3_EXTRACTED_IND is 'Indicates whether the record has been extracted or not for a custom extraction.'
              COMMENT ON COLUMN VAT_EXPORT_STG.CUSTOM4_EXTRACTED_IND is 'Indicates whether the record has been extracted or not for a custom extraction.'
              COMMENT ON COLUMN VAT_EXPORT_STG.PROCESS_ID is 'Process ID where the record belongs to.'
              COMMENT ON COLUMN VAT_EXPORT_STG.TRANSACTION_DATETIME is 'Time of record creation.'

              Not Recognized Statements:  0

            <<<<< ERRORS >>>>>
            Trying to set comment to a non-existent object: VAT_EXPORT_STG.SEQ_NO

            Trying to set comment to a non-existent object: VAT_EXPORT_STG.VAT_REGION

            Trying to set comment to a non-existent object: VAT_EXPORT_STG.VAT_CODE

            Trying to set comment to a non-existent object: VAT_EXPORT_STG.ACTIVE_DATE

            Trying to set comment to a non-existent object: VAT_EXPORT_STG.ACTION_TYPE

            Trying to set comment to a non-existent object: VAT_EXPORT_STG.BASE_EXTRACTED_IND

            Trying to set comment to a non-existent object: VAT_EXPORT_STG.CUSTOM1_EXTRACTED_IND

            Trying to set comment to a non-existent object: VAT_EXPORT_STG.CUSTOM2_EXTRACTED_IND

            Trying to set comment to a non-existent object: VAT_EXPORT_STG.CUSTOM3_EXTRACTED_IND

            Trying to set comment to a non-existent object: VAT_EXPORT_STG.CUSTOM4_EXTRACTED_IND

            Trying to set comment to a non-existent object: VAT_EXPORT_STG.PROCESS_ID

            Trying to set comment to a non-existent object: VAT_EXPORT_STG.TRANSACTION_DATETIME

            • 3. Re: How do I change max comments size duding ddl import.
              Philip Stoyanov-Oracle

              Hi,

               

              I cannot reproduce the problem. It works for me.

              import_large_comments.png

              Philip

              • 4. Re: How do I change max comments size duding ddl import.
                Dave Schleis

                This is a very unusual problem.

                With Phillip unable to reproduce the issue as well, the chances of finding the root cause of this issue are low.

                 

                You have obviously found a work-around for this particular instance, but in my estimation, in your installation, one of the files that support the functions of SDDM has been "corrupted" in some way.

                 

                I had a problem like this a while back. I had an issue that could not be reproduced.

                The only cure was a clean install, meaning that when installing a new version, when you are prompted to copy the settings from the previous version, you answer NO.

                Exporting my connections from the "broken" version and importing into the "clean" version made it less inconvenient, but it was still a bit of a hassle getting everything working the way I liked it again.

                I'm sure version 19.1 is coming soon, which will allow you this opportunity.

                 

                I wish that I could have provided more help.

                 

                --dave

                • 5. Re: How do I change max comments size duding ddl import.
                  User647459-Oracle

                  Philip, Dave!

                  Thank you very much for helping me with this issue.

                  After reading your feedback I actually did a bit of experimenting at my end

                   

                  Here is what I did to make it work at my end.

                   

                  I edited the file and I notice that my file has "/" to terminate the statement

                  I also notice that the create table has an extra space between the beginning of the line and the stmt terminator.

                  if I remove the space, such that the first and only character on the line is /.

                  --- Everything works -----

                  if I leave the space but change the terminator to a ":"  example " ;"

                  --- Everything works -----

                   

                  I decided just to keep everything as is and remove the extra space before the / terminator.

                   

                  Again thank you very much for the help!

                  -- Luis