When I open a View source, the clause "NOFORCE" disappear

Question

I created a random View like this:

CREATE OR REPLACE NOFORCE VIEW TEMP_VIEW (first_name, age) AS
SELECT 'Gladiolus' first_name, 23 age
  FROM dual
/

And I check it out:

SELECT * FROM temp_view
/

FIRST_NAME        AGE
---------- ----------
Gladiolus          23

Now, when I open the source to edit it (with PLSQL Developer and SQL Navigator) I get this:

CREATE OR REPLACE VIEW TEMP_VIEW AS
SELECT 'Gladiolus' first_name, 23 age
  FROM dual;

The alias (first_name, age) is ommited because they are equal to the columns of the subquery.

But can you tell me why the clause NOFORCE is also not showed?

Thank you!!

Answer 1

Some keywords control how objects are created but they are not a part of the object definition. Oracle does not always care "how" an object was created and does not always store the options used to create it.

When Oracle and 3rd party tools create a DDL statement they must guess which of those options you would like to use. It's not uncommon for the tools to make different guesses, or to have small syntax differences.

(This is one of the many reasons to store all DDL in version-controlled text files instead of the database. What you put into the database will NOT be the same as what you get out.)

To use your example another way:

SQL> CREATE NOFORCE VIEW TEMP_VIEW (first_name, age) AS
  2  SELECT 'Gladiolus' first_name, 23 age
  3    FROM dual
  4  /

View created.

The view was created with NOFORCE and without an OR REPLACE. But the DDL generated by DBMS_METADATA.GET_DDL contains FORCE, OR REPLACE and other differences:

SQL> select dbms_metadata.get_ddl('VIEW', 'TEMP_VIEW') from dual;

DBMS_METADATA.GET_DDL('VIEW','TEMP_VIEW')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "JHELLER"."TEMP_VIEW" ("FIRST_NAME",
"AGE") AS
  SELECT 'Gladiolus' first_name, 23 age
  FROM dual

The real "source" of the object does not contain any of the CREATE options at all:

SQL> select text from dba_views where view_name = 'TEMP_VIEW';

TEXT
--------------------------------------------------------------------------------
SELECT 'Gladiolus' first_name, 23 age
  FROM dual

---

Here's an incomplete list of things that may be different when you regenerate code from an Oracle databases:

1. OR REPLACE
2. FORCE|NOFORCE
3. AND RESOLVE|AND COMPILE (for Java)
4. Whitespace
5. Case
6. Column Lists
7. Double Quotes
8. Terminators

Different DDL generators allow you to control some of those options, but in my experience none of them let you completely control all of them.