[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6951
  • Last Modified:

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.
0
holly2003
Asked:
holly2003
  • 3
  • 3
  • 2
  • +2
1 Solution
 
seazodiacCommented:
You have to define

UTL_FILE_DIR parameter in the INIT.ORA file.

and make sure oracle user have access to it.
0
 
holly2003Author Commented:
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
 
seazodiacCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
holly2003Author Commented:
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
 
seazodiacCommented:
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
 
MikeOM_DBACommented:
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
 
MikeOM_DBACommented:
Ooops, sorry * is valid...
0
 
chedgeyCommented:
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
 
holly2003Author Commented:
seazodiac is right. I should catch the exceptions...
The problem solved.
Thanks you all you guys for kind help.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now