Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE
UTL_FILE [message #234214] Mon, 30 April 2007 08:26 Go to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
i want to read a table and put its contents into a file.
iam using the following code:
1CREATE OR REPLACE procedure p_write_file
2 is
3 cursor cur_all_dates is
4 select * from all_dates;
5 fileHandler UTL_FILE.FILE_TYPE;
6 begin
7 dbms_output.put_line('hi');
8 fileHandler := UTL_FILE.FOPEN('MYDIR', 'myfile', 'w');
9 for cur_rec in cur_all_dates loop
10 UTL_FILE.PUTF(fileHandler,cur_rec);
11 end loop;
12 UTL_FILE.FCLOSE(fileHandler);
13 EXCEPTION
14 WHEN utl_file.invalid_path THEN
15 raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
16
17 when others then
18 dbms_output.put_line('error');
19 end;
/
iam getting error at line 10.Can any one help me in this regard.Thanks in advance.

[Updated on: Mon, 30 April 2007 08:46]

Report message to a moderator

Re: UTL_FILE [message #234227 is a reply to message #234214] Mon, 30 April 2007 09:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What is the error that you are getting?
If the ALL_DATES table has more than one column, then trying to pass the whole row to UTL_FILE at once will certainly cause an error.
Re: UTL_FILE [message #234231 is a reply to message #234227] Mon, 30 April 2007 09:26 Go to previous messageGo to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
u r right.i modified the code by giving the individual column names and it is working.Thanks for your reply.
Table type [message #234872 is a reply to message #234227] Thu, 03 May 2007 06:46 Go to previous messageGo to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
Hi,
The following code is giving this error at the line dbms_output.put_line.
CREATE OR REPLACE procedure test as
cursor abc is
select Day d
from all_dates;
type xyz_type is table of abc%rowtype
index by binary_integer;
xyz xyz_type;
begin
open abc;
fetch abc bulk collect into xyz;
for i in 1..10 loop
dbms_output.put_line(to_char(xyz(i)));
end loop;
end;
/

can any one help?
Thanks,
Jana
Re: Table type [message #234878 is a reply to message #234872] Thu, 03 May 2007 06:55 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See my answer in your other post.
Don't multipost.
Format your post.
Give your Oracle version.
Give the error message.
...

Regards
Michel
Previous Topic: Qeury on Rownum.
Next Topic: Granting permission to schema
Goto Forum:
  


Current Time: Thu Dec 08 14:10:20 CST 2016

Total time taken to generate the page: 0.09105 seconds