Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Spool view source in oracle

Dibyendu ChakrabortyJun 26 2009 — edited Jun 29 2009
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.
This post has been answered by db-geek on Jun 26 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 27 2009
Added on Jun 26 2009
9 comments
2,485 views