Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Below is the code i use to extract data from a table to a flat file.

BEGIN
    DECLARE
        file_name VARCHAR2(50);
        file_handle utl_file.file_type;
BEGIN
    file_name := 'table.txt';
    file_handle := utl_file.fopen('SEND',file_name,'W');
FOR rec in(
    SELECT            column 1
                ||'~'||column 2
                ||'~'||column 3 out_line
    FROM table1)LOOP
UTL_FILE.PUT_LINE(file_handle,rec.out_line);
UTL_FILE.FFLUSH(file_handle);
END LOOP;
UTL_FILE.FCLOSE(file_handle);
END;
end;

This code is working fine in our development database but its throwing the below error if i execute in a new DB.

Error starting at line 1 in command:
    BEGIN
    DECLARE
        file_name VARCHAR2(50);
        file_handle utl_file.file_type;
BEGIN
    file_name := 'table.txt';
    file_handle := utl_file.fopen('SEND',file_name,'W');
FOR rec in(
    SELECT            column 1
                ||'~'||column 2
                ||'~'||column 3 out_line
    FROM table1)LOOP
UTL_FILE.PUT_LINE(file_handle,rec.out_line);
UTL_FILE.FFLUSH(file_handle);
END LOOP;
UTL_FILE.FCLOSE(file_handle);
END;
end;

Error report:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 7
29283. 00000 -  "invalid file operation"
*Cause:    An attempt was made to read from a file or directory that does
           not exist, or file or directory access was denied by the
           operating system.
*Action:   Verify file and directory access privileges on the file system,
           and if reading, verify that the file exists.

Oracle directory 'SEND' points to some UNIX directory which has rights as 
       'rwxrwsr-x' (Octal 2775)
Oracle Version:11g

Please help me to solve this issue.

Guys please do let me know if you require more data from me to solve this question.

like image 650
Vivek Avatar asked May 16 '11 08:05

Vivek


2 Answers

So, @Vivek has got the solution to the problem through a dialogue in the Comments rather than through an actual answer.

"The file is being created by user oracle just noticed this in our development database. i'm getting this error because, the directory where i try to create the file doesn't have write access for others and user oracle comes under others category. "

Who says SO is a Q&A site not a forum? Er, me, amongst others. Anyway, in the absence of an accepted answer to this question I proffer a link to an answer of mine on the topic of UTL_FILE.FOPEN(). Find it here.

P.S. I'm marking this answer Community Wiki, because it's not a proper answer to this question, just a redirect to somewhere else.

like image 135
3 revs, 2 users 94% Avatar answered Nov 02 '22 02:11

3 revs, 2 users 94%


Assume file is already created in the predefined directory with name "table.txt"

  • 1) change the ownership for file :

    sudo chown username:username table.txt
    
  • 2) change the mode of the file

    sudo chmod 777 table.txt
    

Now, try it should work!

like image 27
kuldeep Avatar answered Nov 02 '22 02:11

kuldeep