Oracle provides views to allow you to hide the structures of under lying tables, to combine multiple tables into a single logical structure, and to reduce the complexity of SQL being generated against a database. Views allow the developer the flexibility to give users the look and feel of the database they desire, while maintaining a normalized structure under the covers. In this article we will review the SQL to generate a view’s DDL for review or transport.
1. Connect to your Oracle database server and logon to SQL*PLUS.
MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jul 20 23:51:33 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
2. Logon as the user SCOTT and generate the view TEST_VIEW to demonstrate the DDL extraction.
SQL> grant create view to scott;
Grant succeeded.
SQL> connect scott/tiger
Connected.
SQL> create view TEST_VIEW as
2 select emp.ename as employee, dept.dname as department, bonus.sal as salary
from
3 dept, emp, bonus where dept.deptno=emp.deptno and emp.ename=bonus.ename;
View created.
SQL>
3. Execute the following PL/SQL block to generate your DDL.
set serveroutput on
declare
v_ct number;
v_owner varchar2(30):=’SCOTT’;
begin
for v_view in(select view_name, text from dba_views where owner=v_owner)
loop
dbms_output.put_line(‘create view ‘||v_view.view_name||’ as’);
dbms_output.put_line(v_view.text||’;’);
end loop;
end;
/
SQL> connect / as sysdba
Connected.
SQL>
SQL> set serveroutput on
SQL> declare
2
3 v_ct number;
4 v_owner varchar2(30):=’SCOTT’;
5
6 begin
7
8 for v_view in(select view_name, text from dba_views where owner=v_owner)
9 loop
10
11 dbms_output.put_line(‘create view ‘||v_view.view_name||’ as’);
12 dbms_output.put_line(v_view.text||’;’);
13
14 end loop;
15 end;
16 /
create view TEST_VIEW as
select emp.ename as employee, dept.dname as department, bonus.sal as salary from
dept, emp, bonus where dept.deptno=emp.deptno and emp.ename=bonus.ename;
PL/SQL procedure successfully completed.
SQL>
SQL>
Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com