2 Replies Latest reply on Jul 21, 2015 10:05 PM by rp0428

    View DDL force option wrong on SQL tab

    themarman

      If I create a view

       

      create view X as select * from dual;

       

      the default for FORCE/NO FORCE should be NO FORCE https://docs.oracle.com/database/121/SQLRF/statements_8004.htm

       

      but when I create this view and open it in 4.1.19.59 the SQL tab always includes FORCE. The export utility also defaults to FORCE but is at least configurable. I have not been able to find a preference to control this behavior.

        • 1. Re: View DDL force option wrong on SQL tab
          Gary Graham-Oracle

          Well, that behavior is not a regression specific to 4.1, it has worked that way at least since 2.1 as can be seen by reading through this old discussion:

          SQL Developer - how do I display View SQL DDL in stored format

           

          And if you check the new Statement tab in the Log pane, you will see that is exactly what DBMS_METADATA.GET_DDL returns to us.

          • 2. Re: View DDL force option wrong on SQL tab

            the default for FORCE/NO FORCE should be NO FORCE https://docs.oracle.com/database/121/SQLRF/statements_8004.htm

            The default is, indeed, NOFORCE even though the doc section doesn't explicitly state that. You can see that is the default if you try to create your  view using the nonexistent 'dual1' table. You will get an exception if you don't use the FORCE keyword.

            but when I create this view and open it in 4.1.19.59 the SQL tab always includes FORCE. The export utility also defaults to FORCE but is at least configurable. I have not been able to find a preference to control this behavior.

            Correct - the purpose of the FORCE/NOFORCE keywords is to tell Oracle whether the view should be created in the data dictionary even if there are errors due to privileges or other issues.

             

            For views Oracle does NOT store the DDL that you use when you create a view. Since a view is only a stored query it only stores the query (in the TEXT column of the SYS.VIEW$ tablel).

             

            There is nothing stored in the data dictionary to indicate whether you used the FORCE or NOFORCE keywords when you actually executed the query.

             

            Therefore there is no way that ANY TOOL, including Sql Developer, can determine what a user actually used.

             

            Oracle will ALWAYS include the word FORCE when the metadata is extracted since that is the ONLY option that makes sense. When you extract metadata from the dictionary then the dictionary MUST, by definition, already include the object (a view in this case). So it only makes sense to generate DDL using the FORCE keyword so that if you execute that DDL it will GUARANTEE that the dictionary will contain the object even if there are errors.

             

            Although many people seem to think otherwise Oracle is NOT a version controlled repository for your metadata. In particular Oracle does NOT necessarily copy source DDL and insert it, unmodified, into some internal table.

             

            The primary source of metadata for your database objects should ALWAYS be a version control system that your organization maintains for that purpose.

             

            .