2 Replies Latest reply: Aug 1, 2014 4:43 PM by eoin62 RSS

    Issues when reverse engineering views containing sub-queries and parenthesis

    eoin62

      I am running into issues when using Data Modeler to reverse engineer Oracle views.  One issue occurs if the view SQL contains a sub-query, another issue if the view SQL contains more parentheses than are required.  Undesired results include: invalid views, main query being removed from the view leaving just the sub-query, schema name being stripped from the SQL and generating an error when testing the query, and the view's SQL being completely removed/ignored.

       

      Thanks in advance for any suggestions.

       

      Environment:

      SQL Developer Data Modeler, Version 4.0.2.840, Build 840

      Running on Windows 7 Professional, SP1

      Oracle database 11.2.0.4, running on Oracle Exadata

       

      Steps to replicate:

      1. Setup for the test:

      1a. Create table for the views

      CREATE TABLE test

        ( col1 VARCHAR2(10)

        , col2 NUMBER

        , col3 DATE

        );

       

      1b. Create a view with a sub-query and "extra" parentheses

      CREATE OR REPLACE VIEW test1a

      AS

        (SELECT * FROM biuser2.test WHERE col3 =

          (SELECT MAX(test.col3) FROM biuser2.test

          )

        );

       

      1c. Create a view with a sub-query, without the "extra" parentheses

      CREATE OR REPLACE VIEW test1b

      AS

        SELECT * FROM biuser2.test WHERE col3 =

          (SELECT MAX(test.col3) FROM biuser2.test

          )

        ;

       

      1d. Create a view specifying different column names, with "extra" parentheses

      CREATE OR REPLACE VIEW test2a (vcol31, vcol32, vcol33)

      AS

        (SELECT col1, col2, col3 FROM biuser2.test

        );

       

      1e. Create a view specifying different column names, without the "extra" parentheses

      CREATE OR REPLACE VIEW test2b (vcol31, vcol32, vcol33)

      AS

        SELECT col1, col2, col3 FROM biuser2.test

        ;

       

      2. Run Data Modeler (stand alone, though the SQL Developer integrated Data Modeler seems to work the same)

      Import from data dictionary, include the test table and all 4 test views.

      All import, all 4 views have yellow triangle ! icon indicating that the views need to be parsed, Parse Older Style Views.

       

      3. Right-click and select "Parse Older Style Views" for each of the 4 views.

      Status of views are now:

      TEST1A: red triangle ! icon

      TEST1B: icon cleared

      TEST2A: still has yellow triangle ! icon

      TEST2B: icon cleared

       

      4a. TEST1A

      Right-click, Validate selected views has no effect.

      Look at TEST1A properties

      Look at the Query Builder query

      Warning window appears: Parsing error: Failed to parse SQL query. Invalid SELECT statement. Unexpected token ")" at line 2, pos 7

      Click ok, query shown is:

      SELECT MAX(test.col3) FROM biuser2.test

            )

        )

      Result of "Test Query"

      SQL:

      SELECT MAX(test.col3) FROM biuser2.test

            )

            ^

        )

      Error at line 2, column 6:

      ORA-00933: SQL command not properly ended

       

      4b. TEST1B

      Look at TEST1B properties

      Look at Query Builder query

      query shown is:

      SELECT

        TEST.COL1,

        TEST.COL2,

        TEST.COL3

      FROM

        TEST

      WHERE

        TEST.COL3 =

        (

          SELECT

            MAX(TEST.COL3)

          FROM

            TEST

        )

      Results of "Test Query":

      SQL:

      SELECT TEST.COL1,

        TEST.COL2,

        TEST.COL3

      FROM TEST

           ^

      WHERE TEST.COL3 = (SELECT Max(TEST.COL3) FROM TEST)

      Error at line 4, column 5:

      ORA-00942: table or view does not exist

      Preferences for Data Modeler: DDL: Include Schema in DDL is set

      Note that BIUSER2 schema name has been stripped from the SQL

       

      4c. TEST2A

      Look at TEST2A properties

      Look at query builder query

      No SQL is displayed

      Results of "Test Query":

      Nothing, if using "Test Query" from the view properties window.

      Query executed successfully, if clicking the green check icon from the Query Builder window

       

      4d. TEST2B

      Look at TEST2B properties

      Look at query builder query

      query shown is:

      SELECT

        TEST.COL1,

        TEST.COL2,

        TEST.COL3

      FROM

        TEST

      Results of "Test Query":

      SQL:

      SELECT TEST.COL1,

        TEST.COL2,

        TEST.COL3

      FROM TEST

           ^

      Error at line 4, column 5:

      ORA-00942: table or view does not exist