Home » SQL & PL/SQL » SQL & PL/SQL » txt
txt [message #36578] Wed, 05 December 2001 06:38 Go to next message
adelia
Messages: 45
Registered: April 2001
Member
Anyone has a script to read a txt file using utl_file ?

----------------------------------------------------------------------
Re: txt [message #36579 is a reply to message #36578] Wed, 05 December 2001 07:58 Go to previous messageGo to next message
Vince
Messages: 11
Registered: December 2000
Junior Member
try this:

str varchar2(100);
fp := UTL_FILE.FOPEN(location,filename,'r');
LOOP
BEGIN
UTL_FILE.GET_LINE(fp,str);
........
EXCEPTION WHEN no_data_found THEN
EXIT;
END;
END LOOP;

----------------------------------------------------------------------
Re: txt [message #36581 is a reply to message #36579] Wed, 05 December 2001 08:45 Go to previous messageGo to next message
adelia
Messages: 45
Registered: April 2001
Member
I tried this, and received the error below :

declare
str varchar2(100);
fp varchar2(100);
begin
fp := UTL_FILE.FOPEN('D:','ag.txt','r');
LOOP
BEGIN
UTL_FILE.GET_LINE(fp,str);
EXCEPTION WHEN no_data_found THEN
EXIT;
END;
END LOOP;
exception
when others then
dbms_output.put_line('ero');
end;
/

Error :

ERROR at line 5:
ORA-06550: line 5, column 7:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
ORA-06550: line 8, column 5:
PLS-00306: wrong number or types of arguments in call to 'GET_LINE'
ORA-06550: line 8, column 5:
PL/SQL: Statement ignored

You known , what is it ?

----------------------------------------------------------------------
Re: txt [message #36598 is a reply to message #36581] Wed, 05 December 2001 21:58 Go to previous messageGo to next message
tinel
Messages: 42
Registered: November 2001
Member
Hi
you have to declare fp like this:
fp utl_file.file_type;
you also have to set the utl_file_dir parameter with the path to your file in the init.ora file, and then use the same path in fopen function, even if that path is on a remote computer on your network.
this sould work.
Bye

----------------------------------------------------------------------
Re: txt [message #37093 is a reply to message #36578] Wed, 16 January 2002 22:14 Go to previous message
R.Muthu
Messages: 1
Registered: January 2002
Junior Member
U have to declare the file pointer (fp) as following
fp utl_file.file_type;
Previous Topic: enable means?
Next Topic: using sequence in default clause for a column
Goto Forum:
  


Current Time: Tue Mar 31 05:39:18 CDT 2020