Forum Stats

  • 3,767,809 Users
  • 2,252,719 Discussions
  • 7,874,333 Comments

Discussions

SQL Developer Data Modeler issues using double-quoted names for columns and tables

Hello. I'm designing a model for a database that will have an application requiring mixed case database columns and table names.

I'm running into issues where the generated DDL for the model is randomly adding additional double quotes, making columns all lower-case in the foreign key, sometimes defaulting to using double-quotes, etc.

I am using a fresh installation of SQL Developer data modeler version 21.2.0.183, build 183.1957 on windows. Database version is 19.9.

Any advice on settings, add-ins, custom scripts, , tutorials, etc. appreciated.


Chris

Best Answer

  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,352 Employee
    Accepted Answer

    Hello,

    how did you create that model?

    You should never put double quotes in the name when you are editing/creating objects in Data Modeler - that makes the name invalid and it's surrounded with additional double quotes. The product doesn't expect such names

    There is an option in preferences - "Preferences>Data Modeler>DDL" - "Use Quoted Identifiers" - if checked the names will be surrounded by double quotes and case will be preserved as it is.

    If names are in mixed case in database they will be imported properly and later generated with mixed case (quoted).

    The same for import of DDL script - quoted names are treated as quoted in the model but quotes never appear in dialogs. See the picture below - the DDL in "DDL preview" window is used in import and the same DDL is generated (quoted) but name doesn't have quotes in dialog.

    Philip


Answers

  • lead1111
    lead1111 Member Posts: 19 Bronze Badge

    Example:

    CREATE TABLE PARIS_DEV paris_dev"PublicationAuthors" 

      ( 

       ""PublicationAuthorsID""   NUMBER (9) NOT NULL , 

       ""PublicationPublicationId"" NUMBER (10) NOT NULL , 

       ""AuthorsAuthorId""     NUMBER (9) NOT NULL 

      ) 

    ;


    COMMENT ON TABLE PARIS_DEV."PublicationAuthors" IS 'Publication Authors'

    ;

    CREATE UNIQUE INDEX PARIS_DEV."PublicationAuthors_PK" ON PARIS_DEV."PublicationAuthors" 

      ( 

       ""PublicationPublicationId"" ASC , "" authorsauthorid "" asc) 

    ;


    ALTER TABLE paris_dev."PublicationAuthors" 

      ADD CONSTRAINT "PublicationAuthors_PK" PRIMARY KEY("" publicationauthorsid "") ;


    ALTER TABLE paris_dev."PublicationAuthors" 

      ADD CONSTRAINT "PublicationAuthorsAuth_FK" FOREIGN KEY 

      ( 

       ""AuthorsAuthorId""

      )

      references paris_dev."Authors"("AuthorId");


    ALTER TABLE paris_dev."PublicationAuthors" 

      ADD CONSTRAINT "PublicationAuthorsPub_FK" FOREIGN KEY 

      ( 

       ""PublicationPublicationId""

      )

      references paris_dev."Publication"("PublicationId");


    CREATE SEQUENCE PARIS_DEV paris_dev"PublicationAuthors_"Publicatio" 

    START WITH 1 

      NOCACHE 

      ORDER ;


    CREATE OR REPLACE TRIGGER PARIS_DEV."PublicationAuthors_"Publicatio" 

    BEFORE INSERT ON PARIS_DEV."PublicationAuthors" 

    FOR EACH ROW 

    WHEN (NEW.""PublicationAuthorsID"" IS NULL) begin :new."" publicationauthorsid "" := PARIS_DEV." publicationauthors_ "Publicatio".

    NEXTVAL; 

    END;

    /

  • lead1111
    lead1111 Member Posts: 19 Bronze Badge

    I went back to SQL Developer Data Modeler 18.2.

    Opened the same model and added a column quoted and not quoted. Did a DDL Preview.

    Results:

    Not Quoted:

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

    CREATE TABLE paris_dev."PublicationAuthors" (

      "PublicationPublicationId"  NUMBER(10) NOT NULL,

      "AuthorsAuthorId"      NUMBER(9) NOT NULL,

      publicationauthorsid     NUMBER(9) NOT NULL

    );


    COMMENT ON TABLE paris_dev."PublicationAuthors" IS

      'Publication Authors';


    CREATE UNIQUE INDEX paris_dev."PublicationAuthors_PK" ON

      paris_dev."PublicationAuthors" (

        "PublicationPublicationId"

      ASC,

        "AuthorsAuthorId"

      ASC );


    ALTER TABLE paris_dev."PublicationAuthors" ADD CONSTRAINT "PublicationAuthors_PK" PRIMARY KEY ( publicationauthorsid );


    ALTER TABLE paris_dev."PublicationAuthors" ADD CONSTRAINT publicationauthors__un UNIQUE ( "PublicationPublicationId",

                                                 "AuthorsAuthorId" );


    ALTER TABLE paris_dev."PublicationAuthors"

      ADD CONSTRAINT "PublicationAuthorsAuth_FK" FOREIGN KEY ( "AuthorsAuthorId" )

        REFERENCES paris_dev."Authors" ( "AuthorId" );


    ALTER TABLE paris_dev."PublicationAuthors"

      ADD CONSTRAINT "PublicationAuthorsPub_FK" FOREIGN KEY ( "PublicationPublicationId" )

        REFERENCES paris_dev."Publication" ( "PublicationId" );


    Quoted:

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

    CREATE TABLE paris_dev."PublicationAuthors" 

      (

      "PublicationPublicationId"  NUMBER(10) NOT NULL,

      "AuthorsAuthorId"      NUMBER(9) NOT NULL,

      ""              publicationauthorsid""  NUMBER (9) NOT NULL 

      ) 

    ;


    COMMENT ON TABLE PARIS_DEV."PublicationAuthors" IS 'Publication Authors'

    ;


    CREATE UNIQUE INDEX PARIS_DEV."PublicationAuthors_PK" ON PARIS_DEV."PublicationAuthors" 

      ( 

       "PublicationPublicationId" ASC , 

       "AuthorsAuthorId" ASC 

      ) 

    ;


    ALTER TABLE PARIS_DEV."PublicationAuthors" add constraint "PublicationAuthors_PK" primary key ( "" publicationauthorsid "" ) ;

    ALTER TABLE paris_dev."PublicationAuthors" ADD CONSTRAINT publicationauthors__un UNIQUE ( "PublicationPublicationId",

                                                 "AuthorsAuthorId" );


    ALTER TABLE paris_dev."PublicationAuthors"

      ADD CONSTRAINT "PublicationAuthorsAuth_FK" FOREIGN KEY ( "AuthorsAuthorId" )

        REFERENCES paris_dev."Authors" ( "AuthorId" );


    ALTER TABLE paris_dev."PublicationAuthors"

      ADD CONSTRAINT "PublicationAuthorsPub_FK" FOREIGN KEY ( "PublicationPublicationId" )

        REFERENCES paris_dev."Publication" ( "PublicationId" );


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

    Better than 20.2 but some quirky behavior still.

  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,352 Employee
    Accepted Answer

    Hello,

    how did you create that model?

    You should never put double quotes in the name when you are editing/creating objects in Data Modeler - that makes the name invalid and it's surrounded with additional double quotes. The product doesn't expect such names

    There is an option in preferences - "Preferences>Data Modeler>DDL" - "Use Quoted Identifiers" - if checked the names will be surrounded by double quotes and case will be preserved as it is.

    If names are in mixed case in database they will be imported properly and later generated with mixed case (quoted).

    The same for import of DDL script - quoted names are treated as quoted in the model but quotes never appear in dialogs. See the picture below - the DDL in "DDL preview" window is used in import and the same DDL is generated (quoted) but name doesn't have quotes in dialog.

    Philip


  • lead1111
    lead1111 Member Posts: 19 Bronze Badge

    Thanks. I'll try that.