Hi Hrishy,
Forget what I send you earlier (which is using REF CURSOR in your 8.0.5).
Now I don't know if 8.0.5 supports REF CURSORs(someone else out there might
confirm this for us) but I changed your code to not use it and instead uses
a CURSOR FOR SELECT
This is working fine here and very fast.
note: utl_file.put was aslo changed to utl_file.putf

DECLARE
--type my_curs_type is REF CURSOR;
--curs my_curs_type;
--str varchar2(1000);
--t_name varchar2(50);
c_name varchar2(50);
--tab_name varchar2(2000);
cursor cur_tab_name is select distinct table_name as
tab_name from
user_tables where table_name like '%917';
query_txt varchar2(4000);
output utl_file.file_type;
error_code number;
error_msg varchar2(255);
BEGIN
output := utl_file.fopen( '/db/oracle/test','view.txt', 'w' );
FOR rr IN cur_tab_name LOOP
BEGIN
FOR yy IN (SELECT column_name FROM user_tab_columns WHERE
table_name=rr.tab_name and column_name
not in ('T$REFCNTD','T$REFCNTU'))LOOP
BEGIN
query_txt := 'create or replace view'||rr.tab_name||'_vw as select
';
query_txt := query_txt||yy.column_name||',';
END;
END LOOP;
query_txt := RTRIM(query_txt,',');
query_txt := query_txt||' FROM '||rr.tab_name||';';
utl_file.putf(output,query_txt);
utl_file.new_line(output);
utl_file.new_line(output);
END;
END LOOP;
utl_file.fclose( output );

EXCEPTION WHEN OTHERS THEN
BEGIN
error_code:=SQLCODE;
error_msg:=SQLERRM;
dbms_output.put_line('Error '||to_char(error_code)||' : '||error_msg);
END;
END;
/

Note: You might to review the construct of your create view statement as the
output is creating a view for each column in the table (I don't know if
that's what you want)
Regis

-----Original Message-----
From: hrishy [mailto:[Email Address Removed] Tuesday, April 30, 2002 10:56 AM
To: LazyDBA.com Discussion
Subject: RE: util file help required


Hi All

when i am running this programme i am getting an
exception after sometime.how do i handle this
exception ?

declare
type my_curs_type is REF CURSOR;
curs my_curs_type;
--str varchar2(1000);
--t_name varchar2(50);
c_name varchar2(50);
--tab_name varchar2(2000);
cursor cur_tab_name is select distinct table_name as
tab_name from
user_tables where table_name like '%917';
query_txt varchar2(4000);
output utl_file.file_type;
begin
output := utl_file.fopen( '/db/oracle/test',
'view.txt', 'w' );
for rr in cur_tab_name loop
OPEN curs FOR select column_name from
user_tab_columns
where table_name=rr.tab_name and column_name
not in ('T$REFCNTD','T$REFCNTU');
query_txt := 'create or replace view
'||rr.tab_name||'_vw as select ';
loop
fetch curs into c_name;
EXIT WHEN curs%NOTFOUND;
query_txt := query_txt||c_name||',';
end loop;
close curs;
query_txt := rtrim(query_txt,',');
query_txt := query_txt||' from '||rr.tab_name||
';';
utl_file.put(output,query_txt);
utl_file.new_line(output);
utl_file.new_line(output);
end loop;
utl_file.fclose( output );
end;

declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 29

regards
Hrishy

__________________________________________________
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com

--------
Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
*********************************************************************
This electronic transmission is strictly confidential and intended solely
for the addressee. It may contain information which is covered by legal,
professional or other privilege. If you are not the intended addressee,
you must not disclose, copy or take any action in reliance of this
transmission. If you have received this transmission in error,
please notify the sender as soon as possible.
**********************************************************************

Oracle LazyDBA home page