0 Replies Latest reply: Apr 12, 2012 12:25 PM by 930268 RSS

    Invalid DDL generated from existing database objects

    930268
      I am in the process of importing scripts for objects from an existing database into an Oracle Database Project, using the Generate Script functionality within the Server Explorer in Visual Studio to extract the DDL scripts.

      My specs:

      Windows 7 Enterprise SP1, 32 bit
      Visual Studio Premium 2010 SP1
      Oracle Developer Tools for Visual Studio 11.2.0.3.0
      ODP.NET 11.2.0.3.0
      Oracle Client 11.2.0.3.0
      Oracle Database 10.2.0.4.0

      I'm encountering a few cases where the generated output script contains invalid SQL.


      1. When a view has WITH READ ONLY clause, the generated script adds an additional WITH READ ONLY clause at the end when it is deemed neccessary (the user does not have insert privs on the underlying table). E.g:

      Create a test view:

      create view testview (object_name)
      as
      select object_name from user_objects
      with read only
      /

      Choose 'Generate script' from Server explorer:

      CREATE NOFORCE VIEW "TESTVIEW"
      AS select object_name from user_objects
      with read only[NUL]
      WITH READ ONLY
      /


      Another problem here is that a NULL character is inserted in the line before the additional WITH READ ONLY clause (replaced by [NUL] symbol for demonstration purposes). This results in an error when selecting 'Generate script to project'.


      2. View column names do not appear in generated scripts. See e.g. in the example above. This is a big problem for me, since the majority of our views do not give a specific column alias in the view query, and the view often has either different column names than appear in the query itself, or sometimes functions (e.g. nvl()).


      3. Tables with self-referencing foreign key constraints generate incorrect ddl script. Example:

      Create test table:

      create table test_table (test_id number not null, parent_test_id number, test_value varchar2(100));

      alter table test_table add constraint test_pk
      primary key (test_id);

      alter table test_table add constraint test_parent_fk
      foreign key (parent_test_id) references test_table (test_id);


      Choose 'Generate script' from Server explorer:

      CREATE TABLE "TEST_TABLE" (
      "TEST_ID" NUMBER NOT NULL,
      "PARENT_TEST_ID" NUMBER NULL,
      "TEST_VALUE" VARCHAR2(100 CHAR) NULL,
      CONSTRAINT "TEST_PARENT_FK"
      FOREIGN KEY ( "PARENT_TEST_ID")
      REFERENCES "TEST_TABLE" ( "TEST_ID")
      ENABLE
      VALIDATE)
      /
      CREATE UNIQUE INDEX "TEST_PK"
      ON "TEST_TABLE" (
      "TEST_ID")
      /
      ALTER TABLE "TEST_TABLE" ADD (
      CONSTRAINT "TEST_PK"
      PRIMARY KEY ( "TEST_ID")
      USING INDEX "TEST_PK"
      ENABLE
      VALIDATE )
      /


      Running this gives "ORA-02270: no matching unique or primary key for this column-list" since the foreign key is being added before the primary key/unique index.

      Designing this table from the server explorer in Visual Studio does however give correct SQL:

      CREATE TABLE "TEST_TABLE" (
      "TEST_ID" NUMBER NOT NULL,
      "TEST_PARENT_ID" NUMBER NULL,
      "TEST_VALUE" VARCHAR2(20) NULL,
      CONSTRAINT "TEST_PK"
      PRIMARY KEY ( "TEST_ID")
      ENABLE
      VALIDATE,
      CONSTRAINT "TEST_PARENT_FK"
      FOREIGN KEY ( "TEST_PARENT_ID")
      REFERENCES "TEST_TABLE" ( "TEST_ID")
      ENABLE
      VALIDATE);


      Are any of these issues already known and/or being fixed in the next version? For now I will have to manually edit the generated ddl, but it would be nice if this would be fixed. Currently it is a pain and unneccessary overhead to always check and fix the generated ddl.