7.6 How do I…Re-create CREATE VIEW statements?

Problem

I need to re-create the CREATE VIEW statements used to develop some of the views in our system. I know that we use SQL*Plus and the data dictionary to rebuild other DDL statements throughout this book. How do I re-create CREATE VIEW statements using the data dictionary?

Technique

The query used to rebuild CREATE VIEW statements uses the ALL_VIEWS data dictionary view. The TEXT column contains the query used by the view to return rows. The TEXT column is of the LONG datatype, which makes it harder to work with in SQL*Plus. Many operators and functions cannot be used with columns of the LONG datatype.

Steps

1. Run SQL*Plus and connect as the WAITE user account. CHP7_18.SQL, shown in Figure 7.10, depicts a query of the TEXT column in the ALL_VIEWS data dictionary view.

The first three commands set SQL*Plus system variables to turn the heading off, set the long data display to 2,000 characters, and turn feedback off. This formats the output of the query and removes the query heading and the feedback displayed when the query completes. Line 4 suppresses the verification of substitution variables. The query retrieves the TEXT column from the ALL_VIEWS data dictionary view.

2. Run the CHP7_18.SQL script. Substitute the &OWNER substitution variable with SYS and the &VIEW_NAME substitution variable with DBA_CONSTRAINTS.

SQL> START CHP7_18

Enter value for owner: SYS

Enter value for view_name: DBA_CONSTRAINTS

select ou.name, oc.name,

decode(c.type#, 1, ‘C’, 2, ‘P’, 3, ‘U’,

4, ‘R’, 5, ‘V’, 6, ‘O’, 7,’C’, ‘?’),

o.name, c.condition, ru.name, rc.name,

decode(c.type#, 4,

decode(c.refact, 1, ‘CASCADE’, ‘NO ACTION’), NULL),

decode(c.type#, 5, ‘ENABLED’,

decode(c.enabled, NULL, ‘DISABLED’,

decode(bitand(c.defer, 4), 4, ‘ENABLED’,

‘ENFORCED’))),

decode(bitand(c.defer, 1), 1, ‘DEFERRABLE’, ‘NOT

DEFERRABLE’),

decode(bitand(c.defer, 2), 2, ‘DEFERRED’, ‘IMMEDIATE’),

decode(bitand(c.defer, 4), 4, ‘VALIDATED’, ‘NOT

VALIDATED’),

decode(bitand(c.defer, 8), 8, ‘GENERATED NAME’, ‘USER

NAME’),

decode(bitand(c.defer,16),16, ‘BAD’, null),

c.mtime

from sys.con$ oc, sys.con$ rc, sys.user$ ou, sys.user$ ru,

sys.obj$ o, sys.cdef$ c

where oc.owner# = ou.user#

and oc.con# = c.con#

and c.obj# = o.obj#

and c.type# != 8 /* don’t include hash expressions */

and c.rcon# = rc.con#(+)

and rc.owner# = ru.user#(+)

SQL>

3. The CHP7_19.SQL file, shown in Figure 7.11, includes the CREATE OR REPLACE VIEW keywords. The keywords are not concatenated to the TEXT column because the column is of the LONG datatype. LONG columns cannot be concatenated to other columns and cannot use any of the character functions.

4. Spool the output of the query and run CHP7_19.SQL. Replace the &OWNER substitution variable with SYS and the &VIEW_NAME substitution variable with DBA_CONSTRAINTS.

SQL> SPOOL VIEWS07.sql

SQL> START CHP7_19.sql

Enter value for owner: SYS

Enter value for view_name: DBA_CONSTRAINTS

CREATE OR REPLACE TABLE DBA_CONSTRAINTS AS

select ou.name, oc.name,

decode(c.type#, 1, ‘C’, 2, ‘P’, 3, ‘U’,

4, ‘R’, 5, ‘V’, 6, ‘O’, 7,’C’, ‘?’),

o.name, c.condition, ru.name, rc.name,

decode(c.type#, 4,

decode(c.refact, 1, ‘CASCADE’, ‘NO ACTION’), NULL),

decode(c.type#, 5, ‘ENABLED’,

decode(c.enabled, NULL, ‘DISABLED’,

decode(bitand(c.defer, 4), 4, ‘ENABLED’,

‘ENFORCED’))),

decode(bitand(c.defer, 1), 1, ‘DEFERRABLE’, ‘NOT

DEFERRABLE’),

decode(bitand(c.defer, 2), 2, ‘DEFERRED’, ‘IMMEDIATE’),

decode(bitand(c.defer, 4), 4, ‘VALIDATED’, ‘NOT

VALIDATED’),

decode(bitand(c.defer, 8), 8, ‘GENERATED NAME’, ‘USER

NAME’),

decode(bitand(c.defer,16),16, ‘BAD’, null),

c.mtime

from sys.con$ oc, sys.con$ rc, sys.user$ ou, sys.user$ ru,

sys.obj$ o, sys.cdef$ c

where oc.owner# = ou.user#

and oc.con# = c.con#

and c.obj# = o.obj#

and c.type# != 8 /* don’t include hash expressions */

and c.rcon# = rc.con#(+)

and rc.owner# = ru.user#(+)

5. Stop spooling the output file. The output file contains a CREATE OR REPLACE VIEW statement that can build the view.

SQL> SPOOL OFF

SQL>

When the output file is closed, it can be run as a SQL*Plus command file to rebuild the view queried from the data dictionary.

How It Works

A CREATE VIEW statement can be rebuilt by querying the ALL_VIEWS data dictionary view. Steps 1 and 2 return the query used by a view from the ALL_VIEWS data dictionary view. The value of the LONG system variable was changed in order to display the entire value of the LONG column. Steps 3 and 4 build a CREATE OR REPLACE VIEW statement by adding the keywords to the query. Step 5 spools the output to a file that can run as an SQL script and then runs the query to build the statements.

Comments

Once again, the data dictionary is used to rebuild a DDL statement. The LONG column in the ALL_TABLES view causes some problems in SQL*Plus. The LONG system variable in SQL*Plus is used to set the size of long data queried from the view. The LONG datatype prevents many operations from being performed on the column.