Link to home
Start Free TrialLog in
Avatar of holly2003
holly2003

asked on

How to read a file in PL/SQL?

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.
Avatar of seazodiac
seazodiac
Flag of United States of America image

You have to define

UTL_FILE_DIR parameter in the INIT.ORA file.

and make sure oracle user have access to it.
Avatar of holly2003
holly2003

ASKER

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.
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>;



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.
ASKER CERTIFIED SOLUTION
Avatar of seazodiac
seazodiac
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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


Ooops, sorry * is valid...
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
seazodiac is right. I should catch the exceptions...
The problem solved.
Thanks you all you guys for kind help.
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.