Home » SQL & PL/SQL » SQL & PL/SQL » Error while accessing files from PLSQL
Error while accessing files from PLSQL [message #218382] Wed, 07 February 2007 23:40 Go to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hello All,

I am using Oracle 9.

I have to access files using PLSQL.
I have set the initialization parameter utl_file_dir as follows:

SQL> select value from v$parameter where name='utl_file_dir';

VALUE
--------------------------------------------------------------------------------
/u08/reports/


I have written a small procedure to write into a file as follows:

create or replace procedure test_file(filedir in varchar2,filename in varchar2) as
FFile UTL_FILE.FILE_TYPE;
BEGIN
BEGIN
FFile := utl_file.fopen(filedir,fileName,'W');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('error');
END;
utl_file.put_line(ffile,'===========');
END test_file;

When I have called the above procedure using the below PLSQL block:

begin
test_file('/u08/reports','test.rpt');
end;

I got the error as:
ORA-29282: invalid file ID
ORA-06512: at "SYS.UTL_FILE", line 714
ORA-06512: at "GENEVA_ADMIN.TEST_FILE", line 18
ORA-06512: at line 5

Can anyone please let me know where the problem is?
Re: Error while accessing files from PLSQL [message #218395 is a reply to message #218382] Thu, 08 February 2007 00:52 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Cross check whether the file has been opened or not.
IF utl_file.is_open(ffile) THEN
  dbms_output.put_line('is open');
  utl_file.put_line(ffile,'===========');
  utl_file.fclose(ffile);
else
dbms_output.put_line('not open');
end if;
Edit: Changed close_all to close
By
Vamsi

[Updated on: Thu, 08 February 2007 01:20]

Report message to a moderator

Re: Error while accessing files from PLSQL [message #218407 is a reply to message #218395] Thu, 08 February 2007 01:46 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hi vamshi,

Thanks for the reply.

The file is not being created.

I have also changed the below statement
dbms_output.put_line('error');
as
dbms_output.put_line('error' || SQLERRM );

It has displayed the message as :
errorORA-29280: invalid directory path
not open

I am using Oracle 9.
The initialization parmeter is already set to the path as '/u08/reports/'. This directory 'reports' exists in the server.

Can you please suggest me what else that I need to take care so that the file gets created?
Re: Error while accessing files from PLSQL [message #218436 is a reply to message #218382] Thu, 08 February 2007 03:43 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
hi,
after the documentation consultation (it contains very useful information):
The location parameter to UTL_FILE.FOPEN is described here.
How to create the directory object is shown in this example.

Check the directory status by querying
select * from all_directories;
select * from user_tab_privs where privilege in ('READ','WRITE');
Re: Error while accessing files from PLSQL [message #218449 is a reply to message #218436] Thu, 08 February 2007 05:02 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

According to OP

Quote:
SQL> I have set the initialization parameter utl_file_dir as follows:

SQL> select value from v$parameter where name='utl_file_dir';

VALUE
--------------------------------------------------------------------------------
/u08/reports/


It seems he hasn't created any directories. He's doing it the old way which is obslete ?

Create directories and follow the guidelined which flyboy has provided.



[Updated on: Thu, 08 February 2007 05:18]

Report message to a moderator

Re: Error while accessing files from PLSQL [message #218458 is a reply to message #218449] Thu, 08 February 2007 05:37 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hi Flyboy/Tahpush,

I did'nt create the directories before.

Now, I have followed the steps as per the guidelines of fly boy.

1)I created a directory TEST_REPORTS in the path '/u10/users/ritesh' on the server.

2)Then I created a directory object as :

CREATE OR REPLACE DIRECTORY TEST_REPORTS AS '/u10/users/ritesh';
GRANT WRITE ON DIRECTORY TEST_REPORTS TO PUBLIC;
GRANT READ ON DIRECTORY TEST_REPORTS TO PUBLIC;

3) I have also even set the initialization parameter utl_file_dir as '/u10/users/ritesh/TEST_REPORTS' before the 1st step.

Now I called the fopen function as follows:

FFile := utl_file.fopen('/u10/users/ritesh/TEST_REPORTS','test','W',NULL);

This time I am getting the following error:

errorORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation

I have also used the two select statements mentioned by flyboy and I can able to see my directory name in the list.

Can you please guide me further to solve this error?


[Updated on: Thu, 08 February 2007 05:40]

Report message to a moderator

Re: Error while accessing files from PLSQL [message #218468 is a reply to message #218458] Thu, 08 February 2007 06:10 Go to previous message
prashas_d
Messages: 66
Registered: February 2007
Member
Flyboy/Tahpush,

Woow!!!! Thanks for the help.
It's working.

I have done a small mistake in the code.

I have replaced the following statement:
FFile := utl_file.fopen('/u10/users/ritesh/TEST_REPORTS','test','W',NULL);
to the below statement:
FFile := utl_file.fopen('TEST_REPORTS','test','W',NULL);

Then it is working.

Thanks a lot once again to Flyboy for knowing me the guidelines to access the files using PLSQL.

Bye,
Prashanth.
Previous Topic: rownum problem
Next Topic: ref cursor
Goto Forum:
  


Current Time: Thu Dec 08 22:33:25 CST 2016

Total time taken to generate the page: 0.15448 seconds