?
Solved

How to read a file in PL/SQL?

Posted on 2004-08-04
10
Medium Priority
?
6,858 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 500 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
 
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 35

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

764 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