 |
Web Hosting by Netfirms | Free Domain Names by Netfirms |
|
|
|
 Oracle
SQL*/PLSQL Examples |
|
This script generates another script that will include all the CREATE VIEW statements for those in the database (except for the SYS user). |
| crviews.sql We have discovered that the crviews.sql script provided with the book will not work as-is on SAP systems, and others that use column aliases. We have enhanced the script to handle column aliases. The following enhancements and bug fixed have also been made to the crviews.sql script: 1) Automatically support either Oracle7 or Oracle8 syntax. 2) Error traps have been modified. 3) Some Oracle8-specific owners are skipped. 4) Trailing linefeeds in the output text are removed. 5) The "SET TRIMSPOOL ON" and "SET RECSEP OFF" commands have been added. 6) The brkline function has been fixed to handle certain syntax. 7) The DEFINE character has been disabled in the output file. 8) Modified to add SPOOL statements in the created file. |
rem $DBA/crviews.sql rem rem This script generates another script that will include all the CREATE rem VIEW statements for those in the database (except for the SYS user). rem rem Restrictions: rem 1) This script has been tested for Oracle 7 and Oracle 8.0.4. rem 2) This script does not support all of the Oracle8 view syntax. rem 3) This script must be run by a user with the DBA role under Oracle 7.2 rem or later. rem 4) If running on Oracle 7.2, you must comment out the below two rem "set trimspool on" lines, since it is supported as of Oracle 7.3 and rem later. rem rem Last Change 10/09/98 by Brian Lomasky to support view column aliases, modify rem error traps, skip some Oracle8 owners, rem remove any trailing linefeeds. rem Last Change 08/13/98 by Brian Lomasky to add set trimspool on and set recsep rem off, fix brkline, add "set define off" rem to created script. rem Last Change 01/21/98 by Brian Lomasky to add SPOOL statements in created rem file. rem Created 10/21/96 by Brian Lomasky rem set verify off set feedback off set echo off set pagesize 0 set termout on select 'Creating view build script...' from dual; create table view_temp (lineno NUMBER, text VARCHAR2(80));
declare cursor view_cursor is select owner, view_name, text from sys.dba_views where owner != 'SYS' and owner != 'CTXSYS' and owner != 'MDSYS' and owner != 'OEMSYS' order by owner, view_name;
cursor col_cursor(my_owner in varchar2, my_table in varchar2) is select column_name from sys.dba_tab_columns where owner = my_owner and table_name = my_table order by column_id;
lv_owner sys.dba_views.owner%TYPE; lv_view_name sys.dba_views.view_name%TYPE; lv_text sys.dba_views.text%TYPE; lv_column_name sys.dba_tab_columns.column_name%TYPE; lv_lineno number; text_length number; backwards number; max_length number; real_len number; last_break number; dash_pos number; startp number; xchar number; break_pos number; lf_pos number; semi_pos number; offset number; out_start number; l number; bef_chars varchar2(2000); a_lin varchar2(2000); found_col boolean;
function wri(x_lin in varchar2, x_str in varchar2, x_force in number) return varchar2 is begin if length(x_lin) + length(x_str) > 80 then lv_lineno := lv_lineno + 1; insert into view_temp values (lv_lineno, x_lin); if x_force = 0 then return x_str; else lv_lineno := lv_lineno + 1; insert into view_temp values (lv_lineno, x_str); return ''; end if; else if x_force = 0 then return x_lin||x_str; else lv_lineno := lv_lineno + 1; insert into view_temp values ( lv_lineno, x_lin||x_str); return ''; end if; end if; end wri;
procedure brkline(x_str in varchar2) is
begin
text_length := nvl(length(x_str), 0);
-- Break long text field into smaller groups to print
if text_length = 0 then
return;
end if;
max_length := 80;
startp := 1;
while startp <= text_length
loop
--
-- See if line begins with a --
--
-- Calculate real length of line that is <= 80 chars
-- (taking into account that each tab will move to the
-- next multiple of 8)
offset := 0;
real_len := 0;
out_start := startp;
while out_start <= text_length
loop
if substr(x_str, out_start, 1) = chr(9) then
l := floor(real_len / 8) * 8 + 8 -
real_len;
else
l := 1;
end if;
if real_len + l > max_length then
real_len := out_start - startp;
exit;
end if;
if out_start = text_length then
real_len := text_length - startp + 1;
exit;
end if;
real_len := real_len + l;
out_start := out_start + 1;
end loop;
-- Break line at last space/tab, semi-colon, linefeed,
-- "--" comment, comma, tab, 80 chars, or end of string
break_pos := instr(substr(x_str, startp, real_len),
' '||chr(9), -1);
lf_pos := instr(substr(x_str, startp, real_len),
chr(10), -1);
dash_pos := instr(substr(x_str, startp, real_len),
chr(45) || chr(45), -1);
semi_pos := instr(substr(x_str, startp, real_len),
';', -1);
-- No break at space/tab if no characters come before it
if break_pos > 0 then
bef_chars := ltrim(substr(x_str, startp,
break_pos - startp + 1));
if bef_chars is null then
break_pos := 0;
end if;
end if;
-- See which break is encountered first
last_break := 0; -- Init position of last break
if lf_pos > last_break then
-- Store break right before the linefeed
last_break := lf_pos - 1;
offset := 1; -- Skip linefeed
end if;
-- Check for break at a comma
lf_pos := instr(substr(x_str, startp, real_len),
',', -1);
if lf_pos > last_break then
-- Store break right after the comma
last_break := lf_pos;
offset := 0;
end if;
if break_pos = 0 then
-- Check for break at a tab if no space/tab
-- found
lf_pos := instr(substr(x_str, startp,
real_len), chr(9), -1);
if lf_pos > last_break then
-- Store break right before the tab
last_break := lf_pos - 1;
offset := 0;
end if;
end if;
if semi_pos > last_break then
-- Store break right after the semicolon
last_break := semi_pos;
offset := 0;
else
-- If there is a break at a semi-colon, skip
-- this break at the tab
if break_pos > last_break then
-- Store break right after the space
last_break := break_pos;
-- Skip space part of the space/tab
offset := 1;
end if;
end if;
if dash_pos > last_break then
-- Do not break if remainder of line <= 80 chars
if text_length - startp >= real_len then
-- Store break right before the dashes
last_break := dash_pos - 1;
offset := 0;
end if;
end if;
-- No break if rest of line is <= 80 characters
if real_len = text_length - startp + 1 then
-- Set to break at end of line
last_break := real_len;
offset := 0;
end if;
-- Break at the last break that was encountered
break_pos := last_break;
-- See if no break position found
if break_pos = 0 then
-- Back up to first space
backwards := 1;
-- Start looking for break at line end
break_pos := real_len;
while backwards = 1
loop
-- See if no space found
if break_pos <= 1 then
-- Break at 80 chars
break_pos := real_len;
backwards := 0;
exit;
end if;
-- See if we found a space
if substr(x_str, startp +
break_pos - 1, 1) = ' '
then
backwards := 0;
exit;
end if;
break_pos := break_pos - 1;
end loop;
end if;
xchar := break_pos;
-- Print output line
if xchar = 0 then
if offset = 0 then
return;
end if;
else
-- Remove any trailing linefeeds
while substr(x_str, xchar, 1) = chr(10) and
xchar > 1
loop
xchar := xchar - 1;
offset := offset + 1;
end loop;
-- See if needing to wrap a long comment
if max_length = 78 then
a_lin := wri(a_lin,
chr(45) || chr(45) ||
substr(x_str, startp, xchar),
1);
else
a_lin := wri(a_lin,
substr(x_str, startp, xchar),
1);
end if;
end if;
-- See if we just found a comment line
if max_length = 80 then
if substr(ltrim(substr(x_str, startp, 132),
' ' || chr(9)), 1, 2) =
chr(45) || chr(45)
then
max_length := 78;
else
max_length := 80;
end if;
end if;
startp := startp + xchar + offset;
end loop;
end brkline;
begin
a_lin := '';
lv_lineno := 0;
open view_cursor;
loop
fetch view_cursor into
lv_owner,
lv_view_name,
lv_text;
exit when view_cursor%NOTFOUND;
a_lin := wri(a_lin, 'create view ', 0);
a_lin := wri(a_lin, lv_owner || '.' || lv_view_name, 0);
found_col := false;
open col_cursor(lv_owner, lv_view_name);
loop
fetch col_cursor into lv_column_name;
exit when col_cursor%NOTFOUND;
if not found_col then
a_lin := wri(a_lin, ' (', 0);
found_col := true;
else
a_lin := wri(a_lin, ', ', 0);
end if;
a_lin := wri(a_lin, lv_column_name, 0);
end loop;
close col_cursor;
if found_col then
a_lin := wri(a_lin, ')', 0);
end if;
a_lin := wri(a_lin, ' as ', 1);
brkline(lv_text);
a_lin := wri(a_lin, ';', 1);
end loop;
close view_cursor;
commit;
exception
when others then
rollback;
raise_application_error(-20000,
'Unexpected error on ' || lv_owner ||
'.' || lv_view_name || ':' || chr(10) ||
sqlerrm || chr(10) || 'Aborting...');
end;
/
set recsep off set trimspool on set termout off set heading off spool cr_view.sql select 'rem cr_view.sql' from dual; select 'rem' from dual; select 'rem ***** All views for database ' || name from v$database; select 'rem' from dual; select 'set define off' from dual; select 'set echo on' from dual; select 'set feedback off' from dual; select 'set termout on' from dual; select 'set trimspool on' from dual; select 'set verify off' from dual; select 'spool cr_view.lst' from dual; select text from view_temp order by lineno; select 'spool off' from dual; spool off drop table view_temp; set termout on select 'Created cr_view.sql...' from dual; set termout off exit
|
|