Solved

How to read a file in PL/SQL?

Posted on 2004-08-04
10
6,761 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
use lov values 2 72
oracle- set role and grant privileges 6 51
Oracle Query - Convert letters to numbers and display the difference 3 45
Checking for column width 8 40
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

733 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