invalid file operation at "SYS.UTL_FILE ERROR

[zalesam]

Hi,
In my procedure I am trying to read a table and write the content to a
text file and store it in a directory. But I am getting this error.

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at line 14

create or replace PACKAGE BODY pkg_test AS
DUMP_DIR CONSTANT VARCHAR2(30) := 'TEST_TEMP';
PROCEDURE dump_test(Filename IN VARCHAR2) IS
c sys_refcursor;
fout utl_file.file_type;

BEGIN

------
-----
----
fout := utl_file.fopen(DUMP_DIR, Filename, 'w', 32767);
---
----
END dump_test;
END pkg_test;

I have created this directory using the following command.

CREATE OR REPLACE DIRECTORY test_temp AS '/home/Sam/test_temp'

output
---------
CREATE OR REPLACE DIRECTORY succeeded.


on my file system I created test_temp (/home/Sam/)and gave read,write
and execute Privileges.

In sql Developer I can see the directory created. But I am not sure
what is going wrong.
Please help.

Thanks,
Sam

[ddf]

You do have a package specification for this body? You don't show any
code to create one.

Modifying your code slightly (to provide a package spec and use
Windows rather than Linux/UNIX) produces this:

SQL> CREATE OR REPLACE DIRECTORY test_temp AS 'c:\test_temp';

Directory created.

SQL>
SQL> create or replace PACKAGE pkg_test AS
2
3 PROCEDURE dump_test(Filename IN VARCHAR2);
4
5 END pkg_test;
6 /

Package created.

SQL>
SQL> create or replace PACKAGE BODY pkg_test AS
2
3 DUMP_DIR CONSTANT VARCHAR2(30) := 'TEST_TEMP';
4
5 PROCEDURE dump_test(Filename IN VARCHAR2) IS
6 c sys_refcursor;
7 fout utl_file.file_type;
8
9 BEGIN
10
11
12 ------
13 -----
14 ----
15 fout := utl_file.fopen(DUMP_DIR, Filename, 'w',
32767);
16 ---
17 ----
18 END dump_test;
19
20 END pkg_test;
21 /

Package body created.

SQL>
SQL> exec pkg_test.dump_test('yarg')

PL/SQL procedure successfully completed.

SQL> $ls -l c:\test_temp
total 0
-rw-rw-rw- 1 user group 0 Jan 6 11:27 yarg

SQL>

I can't get it to fail. You need to provide more information before
anyone can see what you're doing incorrectly.



David Fitzjarrell

[zalesam]

do I have to set any parameters in the init.ora file, if so what are
those and where can I find init.ora file ? I did exactly the same as
you mentioned and still getting the same error.

Thanks,
Sam

[ddf]

> > David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

First you need to report which release of Oracle you're using, and
report all four to five numbers (10.2.0.3.0, 9.2.0.8, etc.) as '9i',
'10g', '11g' don't provide any useful information. When you've
provided that information, along with the exact steps you're executing
when this error occurs, then someone can answer your question.


David Fitzjarrell