Spool view source in oracle
Hi,
I need to spool source code of all the views in my schema. I am in oracle 7, 8.
I am trying to spool it using the following code:
set serveroutput on size 1000000
spool view_source.txt
set line 100
set long 1000000
declare
n number:=1;
view_len number;
begin
for c in (select view_name, text from user_views)
loop
select length(c.text) into view_len from dual;
DBMS_OUTPUT.PUT('CREATE OR REPLACE VIEW ' ||c.view_name ||' AS ');
dbms_output.put(c.text);
--while (n<view_len)
--loop
--DBMS_OUTPUT.PUT_LINE(SUBSTR(c.text, n, 154));
dbms_output.put(c.text);
-- n:=n+154;
--end loop;
end loop;
end;
/
spool off;
This block throws an error saying: ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
begin
for c in (select view_name, text from user_views)
loop
select length(c.text) into view_len from dual;
DBMS_OUTPUT.PUT('CREATE OR REPLACE VIEW ' ||c.view_name ||' AS ');
while (n<view_len)
loop
DBMS_OUTPUT.PUT_LINE(SUBSTR(c.text, n, 154));
dbms_output.put(c.text);
n:=n+154;
end loop;
end loop;
end;
This block of code runs fine. But I will not be able to use the spooled source to create the same view.
Please suggest something.
thnks.