OraFAQ Forum: SQL & PL/SQL » Error while accessing files from PLSQL

Home » SQL & PL/SQL » SQL & PL/SQL » Error while accessing files from PLSQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error while accessing files from PLSQL [message #218382]

Wed, 07 February 2007 23:40

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?

Report message to a moderator

Re: Error while accessing files from PLSQL [message #218395 is a reply to message #218382]

Thu, 08 February 2007 00:52

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

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

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?

Report message to a moderator

Re: Error while accessing files from PLSQL [message #218436 is a reply to message #218382]

Thu, 08 February 2007 03:43

flyboy
Messages: 1903
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');

Report message to a moderator

Re: Error while accessing files from PLSQL [message #218449 is a reply to message #218436]

Thu, 08 February 2007 05:02

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

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

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.

Report message to a moderator

Previous Topic:	rownum problem
Next Topic:	ref cursor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 23:50:36 CST 2024