Solved

How to read a file in PL/SQL?

Posted on 2004-08-04
10
6,678 Views
Last Modified: 2012-05-05
I was trying to write a stored procedure to read a txt file. The procedure complied successfully. But it gave me the following error when I executed the procedure:
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 610
ORA-06512: at "CROSSIX_USER.TEST_READ", line 8
ORA-06512: at line 1

Both procedure and file are on SERVER (window2000 with oracle9i). The procedure I wrote is as follows:
////////////////////////////////////////////////////////////////////////////////////////////////////
CREATE OR REPLACE PROCEDURE test_read (inDirectory varchar2, inFileName varchar2)
IS
 file_handle UTL_FILE.FILE_TYPE;
 l_text varchar2(100);
BEGIN
 file_handle := UTL_FILE.FOPEN(inDirectory, inFileName, 'R');
 LOOP
   utl_file.get_line(file_handle, l_text);
   dbms_output.put_line(l_text);
 END LOOP;
 utl_file.fclose(file_handle);
end;
////////////////////////////////////////////////////////////////////////////////////////////////////

Can anyone help me out? Thanks.
0
Comment
Question by:holly2003
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 11715818
You have to define

UTL_FILE_DIR parameter in the INIT.ORA file.

and make sure oracle user have access to it.
0
 

Author Comment

by:holly2003
ID: 11716002
I did (1) add utl_file_dir=* to init.ora  and restart the DB (2) create a directory (3) grant the oracle user priviledge to read that directory

But the same error occurs.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11716061
are you in Oracle9i or 8i?
in 9i, you have to create a Oracle directory inside the database before you use UTL_FILE

Examples (UNIX-Specific)
Given the following:

SQL> CREATE OR REPLACE DIRECTORY test_dir AS '/temp';
SQL> GRANT READ ON DIRECTORY test TO <user_account>;



0
 

Author Comment

by:holly2003
ID: 11716163
my db is 9i. I also did what you mentioned in your last comment - create directory and grant read priviledge to the user...

If it were the privilege problem, the error would be' invalid operation' instead of 'No data found'...

more thoughts? Thanks a lot.
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 125 total points
ID: 11716215
that happens still because you don't catch the end of file exception and you are using a endless loop.


try to use this:


CREATE OR REPLACE PROCEDURE test_read (inDirectory varchar2, inFileName varchar2)
IS
file_handle UTL_FILE.FILE_TYPE;
l_text varchar2(100);
BEGIN
file_handle := UTL_FILE.FOPEN(inDirectory, inFileName, 'R');
LOOP
  utl_file.get_line(file_handle, l_text);
  dbms_output.put_line(l_text);
END LOOP;
EXCEPTION        
WHEN NO_DATA_FOUND        
THEN                
UTL_FILE.FCLOSE (file_handle);
end;
/
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 11716585
utl_file_dir=* not valid directory, must be valid pathname use something like this:
# Note: repeat for more than one directory:
utl_file_dir=/valid/directory1
utl_file_dir=/valid/directory2
utl_file_dir=/path2/directory0


0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 11716598
Ooops, sorry * is valid...
0
 
LVL 6

Expert Comment

by:chedgey
ID: 11716653
holly2003

I notice that the variable into which you read the firs tline of the file is only of size 100 - are you sure that is enough?

Also, take a look at the data file itself - it could be that your routine is not interpreting the line breaks correctly. Try creating a test file with a single line that is less than 100 characters long and see what the result is.

Regards

Chedgey
0
 

Author Comment

by:holly2003
ID: 11716816
seazodiac is right. I should catch the exceptions...
The problem solved.
Thanks you all you guys for kind help.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 11716819
In Oracle9i, you do not need "utl_file_dir" entries *IF* you have created directory objects in Oracle that you use for ASCII files with utl_file.  Make sure that the value you pass for the "in_directory" parameter is upper-case, that is mandatory (even in Oracle on non case-sensitive operating systems like Windows).

Seazodiak may have identified the real problem, your procedure would raise the no_data_found exception when it reaches the end of the data file, and you need to code for that as he suggested.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now