4 Replies Latest reply: May 3, 2012 4:37 AM by David Last-Oracle RSS

    Failure to quote view name on dictionary import

    KevinDavis
      11g:

      SQL> create view "a" as select 1 a from dual;


      View created.

      SQL>

      Using SDDM 3.1.1: Dictionary import view "a"
      Review physical properties and the resulting definition is:

      CREATE OR REPLACE VIEW a
      AS
      select 1 a from dual

      w/o a quoted name

      My preferences: Data Modeler DDL: Use Quoted Identifiers is selected
        • 1. Re: Failure to quote view name on dictionary import
          David Last-Oracle
          Hi Kevin,

          When I tried this in Data Modeler 3.1.1, it generated

          CREATE OR REPLACE VIEW "SCOTT"."a" AS
          SELECT 1 a FROM dual ;

          Are you sure "Use Quoted Identifiers" was selected in your DDL preferences?

          David
          • 2. Re: Failure to quote view name on dictionary import
            KevinDavis
            David,

            I have repeated the exercise and I do obtain from DDL Preview:

            CREATE OR REPLACE VIEW "KDAVIS"."a" AS
            select 1 a from dual ;

            when I right click on the view in the relational model -- similar to your result.


            But when I reprise my "Review physical properties" (in Browser / Designs / Untitled_1 / Relational Models / Physical Models / Oracle Database 11g / Views / KDAVIS.a I obtain as before:
            CREATE OR REPLACE VIEW a
            AS
            select 1 a from dual

            Now I wouldn't be too concerned except this is the fountainhead of the view definition. I should be able to copy this definition and use it, say in SQLplus, as well as using preview DDL or export DDL.

            What I haven't yet conveyed is that I have managed somehow, from time to time, to break my builds from exported DDL due to this difference -- some sequence of merges that have necessiated my going into the physical view properties and manually quoting the view name. I haven't yet had time to distill this scenario.

            -- Kevin
            • 3. Re: Failure to quote view name on dictionary import
              KevinDavis
              P.S.

              Re: "Are you sure "Use Quoted Identifiers" was selected in your DDL preferences?

              /cygdrive/c/Documents and Settings/kdavis1/Application Data/Oracle SQL Developer Data Modeler/system3.1.1.703/o.datamodeler/product-preferences.xml
              ...
              <value n="use_quoted_identifiers" v="true"/>
              • 4. Re: Failure to quote view name on dictionary import
                David Last-Oracle
                Hi Kevin,

                Thanks. I understand now.

                The Definition property in the Physical properties for the View contains the view definition as imported from the database.

                When you do a DDL generation or a DDL Preview, the quotes should be added, provided the "Use Quoted Identifiers" option in the Data Modeler/DDL Preferences is set.
                The appropriate schema-name (i.e. KDAVIS in your case) is also added at this time, if relevant.

                David