Home » SQL & PL/SQL » SQL & PL/SQL » Create files from Oracle Procedure (Oracle 10.2)
Create files from Oracle Procedure [message #359271] Fri, 14 November 2008 08:26 Go to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hi All,

I have written a code for creating files from Procedure. But when I am executing it is giving me an error as
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at "PTTDEV01.TEST_FILE", line 5
ORA-06512: at line 1

This is what I have done:

1) Created a directory in server and gave full permissions to it.
drwxrwxrwx 3 pdumpala dvlpmnt 247 Nov 14 06:02 pras

asdo0002% cd pras
asdo0002% pwd
/tmp/pras

2) Created a directory object on that directory with permissions from user "pttdev01".

CREATE OR REPLACE DIRECTORY TEST_REPORTS AS '/tmp/pras';

GRANT READ ON DIRECTORY TEST_REPORTS TO PUBLIC;

GRANT WRITE ON DIRECTORY TEST_REPORTS TO PUBLIC;

SQL> select table_name, grantor, grantee, privilege from user_tab_privs where privilege in ('READ','WRITE');

TABLE_NAME                     GRANTOR                        GRANTEE                        PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ----------------------------------------
TEST_REPORTS                   PTTDEV01                       PUBLIC                         WRITE
TEST_REPORTS                   PTTDEV01                       PUBLIC                         READ
TEST_REPORTS                   SYS                            PTTDEV01                       WRITE
TEST_REPORTS                   SYS                            PTTDEV01                       READ



3) Now here is my procedure to create file:

create or replace procedure test_file as
    FFile UTL_FILE.FILE_TYPE;
BEGIN
    FFile := utl_file.fopen('TEST_REPORTS','test','w',NULL);
    utl_file.put_line(ffile,'===========');
END test_file;


4) When I call the procedure, I am getting below error:

SQL> begin test_file; end;
  2  /

begin test_file; end;

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at "PTTDEV01.TEST_FILE", line 5
ORA-06512: at line 1



The procedure is created and called by same user "pttdev01".

Can someone please help me to get out of this error? I think I am missing something here.

Thanks in advance.

prashas_d
Re: Create files from Oracle Procedure [message #359275 is a reply to message #359271] Fri, 14 November 2008 08:55 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Have you verified that the oracle user can read and write to it ?

[Updated on: Fri, 14 November 2008 08:57]

Report message to a moderator

Re: Create files from Oracle Procedure [message #359280 is a reply to message #359275] Fri, 14 November 2008 09:03 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
@tahpush,

hmm... The directory has the full permissions(777) and the directory object also has the read and write permission to it as shown in my previous post.

Can you please let me know what else I need to provide or check? Might be I did not understand your question correctly, if thats the case then can you please let me know how to check the same?

prashas_d
Re: Create files from Oracle Procedure [message #359281 is a reply to message #359275] Fri, 14 November 2008 09:04 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Quote:
FFile := utl_file.fopen('TEST_REPORTS','test','w',NULL);



UTL_FILE.FOPEN (
   location     IN VARCHAR2,
   filename     IN VARCHAR2,
   open_mode    IN VARCHAR2,
   max_linesize IN BINARY_INTEGER) 
  RETURN file_type;


Since you are using max_linesize

Quote:
The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.


Skip max_linesize if you dont need it.

[Updated on: Fri, 14 November 2008 09:07]

Report message to a moderator

Re: Create files from Oracle Procedure [message #359282 is a reply to message #359281] Fri, 14 November 2008 09:10 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
No luck Sad

I replaced it to the following

FFile := utl_file.fopen('TEST_REPORTS','test','w');

But still I am getting the same "ORA-29283: invalid file operation" error.

prashas_d.
Re: Create files from Oracle Procedure [message #359285 is a reply to message #359282] Fri, 14 November 2008 09:19 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Log on as the oracle user on the server and type

touch /tmp/pras/tmp.txt


If it that doesn't work you have a permission issue

Re: Create files from Oracle Procedure [message #359329 is a reply to message #359271] Fri, 14 November 2008 17:34 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
ok.

Does a file get created in your directory at all? Does it have anything in it?

UTL_FILE will write files as oracle with almost no permissions to anyone else, you need to bear that in mind.

Can Oracle see that particular mount point on your box?

[Updated on: Fri, 14 November 2008 17:35]

Report message to a moderator

Previous Topic: How to build a function in order to be called in SQL
Next Topic: Indexing
Goto Forum:
  


Current Time: Sun Dec 04 04:38:53 CST 2016

Total time taken to generate the page: 0.08518 seconds