Re: View source code

From: Eric Givler <egivler_at_flash.net>
Date: Thu, 16 Nov 2000 12:51:55 GMT
Message-ID: <LLQQ5.2842$6W1.181261_at_news.flash.net>

rem build_Dumpviews.sql
rem creates dumpviews.sql
SET NEWPAGE 0
SET PAGESIZE 0
SET FEEDBACK OFF
spool dumpviews.sql
select '_at_a_vueddl "' || owner || '" "' || view_name || '"'   from all_views
where owner=upper('&1')
ORDER BY VIEW_NAME;
SPOOL OFF

rem *******************************************************************
rem Name:     a_vueddl.sql
rem Author:   Chris Hamilton, SRA Technologies, Inc.
rem Date:     10-Jan-93, 20-Mar-93.  Added "or replace" option for
rem           ORACLE7, 25-Oct-93.
rem Purpose:  This script extracts a view definition FROM the data
rem           dictionary AND  spools it to an OS file.  This version
rem           runs against the ALL_x views - for users/developers.

rem Usage: sqlplus -s un/pw _at_$DBA/a_vueddl.sql

rem *******************************************************************

set space 1;
set verify off;
set numwidth 4;
set heading off;
set linesize 79;
set pagesize 0;
set feedback off;

set recsep off;
set long 12000;
set timing off;

ttitle off;
btitle off;

prompt What is the view owners name?
define vueowner = &1
prompt What is the view name?
define vuename = &2

column remarks format a79;
column col1 format a75;

SPOOL create_view_&&vueowner._&&vuename..sql; set termout off;

SELECT 'create or replace view ' || LOWER(owner) || '.' || view_name  col1
  FROM dba_views
 WHERE owner = UPPER('&&vueowner')
   AND view_name = UPPER('&&vuename')
/

SELECT decode(rownum , 1 , '(' , ',') || chr(9) || LOWER(column_name) col1

       /* || ' -- col #' || column_id col1 */   FROM dba_tab_columns
 WHERE owner = UPPER('&&vueowner')
   AND table_name = UPPER('&&vuename')
 ORDER BY column_id
/

SELECT ') as ' FROM dual
/

set linesize 900;
column col3 format a900
-- word_wrap;
set space 1;
set array 1;

SELECT text col3
  FROM dba_views
 WHERE owner = UPPER('&&vueowner')
   AND view_name = UPPER('&&vuename')
/

set linesize 1000;
SELECT '/' || chr(10) FROM dual
/

spool off;

undefine vueowner
undefine vuename
set termout on;

Buck Eskew <BEskew_at_gte.net> wrote in message news:ypDQ5.830$BN.336942_at_paloalto-snr1.gtei.net...
> Does anyone know how to extract the original SQL script that was used to
> create a view?
>
> Thanks in advance,
> Buck Eskew
>
>
>
Received on Thu Nov 16 2000 - 13:51:55 CET