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_han dle, l_text);
dbms_output.put_line(l_tex t);
END LOOP;
utl_file.fclose(file_handl e);
end;
////////////////////////// ////////// ////////// ////////// ////////// ////////// ////////// ////////// ////
Can anyone help me out? Thanks.
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
LOOP
utl_file.get_line(file_han
dbms_output.put_line(l_tex
END LOOP;
utl_file.fclose(file_handl
end;
//////////////////////////
Can anyone help me out? Thanks.
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.
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>;
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>;
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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/direct ory1
utl_file_dir=/valid/direct ory2
utl_file_dir=/path2/direct ory0
# Note: repeat for more than one directory:
utl_file_dir=/valid/direct
utl_file_dir=/valid/direct
utl_file_dir=/path2/direct
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
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
ASKER
seazodiac is right. I should catch the exceptions...
The problem solved.
Thanks you all you guys for kind help.
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.
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.
UTL_FILE_DIR parameter in the INIT.ORA file.
and make sure oracle user have access to it.