| Create files from Oracle Procedure [message #359271] |
Fri, 14 November 2008 08:26  |
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 #359280 is a reply to message #359275] |
Fri, 14 November 2008 09:03  |
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 |
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 |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
