possible file lock error

[Gyankr]

Hi,
I am trying to create a file dynamically on a unix server using plsql.
When i run the procedure,i am getting the below error.
"ORA-20103: Invalid Operation"
I also created a temporary file (with permissions 777) and yet nothing was written in the file.
Please find the plsql procedure below:

CREATE OR REPLACE procedure schema.WriteProcedure
is
f UTL_FILE.FILE_TYPE;
var_id number(22);
var_intid varchar2(20);

cursor file_cur is select col1,col2 from schema.table;

begin


f := UTL_FILE.FOPEN('/home/gyan/tst','Dynamic-file-'|| to_char(sysdate, 'mm-dd-yyyy'),'W');

open file_cur;
loop
fetch file_cur into var_id,var_intid;
exit when file_cur%notfound;

UTL_FILE.PUT_LINE(f,var_id || '|' || var_intid );

end loop;
close file_cur;
UTL_FILE.FCLOSE(f);
UTL_FILE.FCLOSE_ALL;

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20102,'Invalid Filehandle');
WHEN UTL_FILE.READ_ERROR THEN
RAISE_APPLICATION_ERROR(-20104,'Read Error');
WHEN UTL_FILE.WRITE_ERROR THEN
RAISE_APPLICATION_ERROR(-20105,'Write Error');
WHEN UTL_FILE.INTERNAL_ERROR THEN
RAISE_APPLICATION_ERROR(-20106,'Internal Error');
WHEN VALUE_ERROR THEN
RAISE_APPLICATION_ERROR(-20108,'Value Error');
WHEN UTL_FILE.INVALID_OPERATION THEN
RAISE_APPLICATION_ERROR(-20103,'Invalid Operation ');
when others then
dbms_output.put_line('done');

end;
/

Regards,
Gyan

[Jeeshan]

Hi Gyan

This is a user specified error message

I think you can contact with your application vendor.

This is mostly happen in application.

[Eric Antunes]

Hi Gyan,

Some suggestions:

-- Before opening the files, check if they aren't already open:

if TEXT_IO.IS_OPEN(f) then
TEXT_IO.FCLOSE(f);
end if;

-- Check if you have write permissions all the way to /home/gyan/tst

-- Try the fopen command this way (added a slash after /home/gyan/tst and lowered the "W" case:

f := UTL_FILE.FOPEN('/home/gyan/tst/','Dynamic-file-'|| to_char(sysdate, 'mm-dd-yyyy'),'w');

Best Regards,

Eric Antunes

[Gyankr]

Hi Eric,

Before opening the files, check if they aren't already open:

I am dynamically creating the file(with timestamp),so this would not apply for my case

-- Check if you have write permissions all the way to /home/gyan/tst

I am logging in as user gyan and have write permissions(umask 022)

Try the fopen command this way (added a slash after /home/gyan/tst and lowered the "W" case:

I did the same but still no success.

I tried to remove all the exception statements and got the below error

ORA-29283: invalid file operation

Does the above exception mean i am having privilege problems to execute the procedure?

Regards,
Gyan

[Yogeeraj_1]

hi,

Normally, the oracle user should have read privileges on that directory at the OS level ..

can you confirm this?

revert
kind regards
yogeeraj

[Gyankr]

Hi Yogeeraj,
the db username (eaxee) is the owner of the procedure.I am logging in as user "gyan" to the server(unix).My database resides on the same unix server(not somewhere remotely).

Regards,
Gyan

[Gyankr]

To add to the above the oracle user (eaxee) has read,write privileges on the directory created.
And the unix user (gyan) can create/delete/modify files under /home/gyan/tst

Regards,
Gyan

[Eric Antunes]

Hi Gyan,

Try 3 more things:

1- Please recheck the write permissions as gyan (I'm insisting this because /home... is a root path and you should consider using a no root directory) with the touch command:

$touch /home/gyan/tst/x

2- Ok, utl_file.fopen has 4 possible parameters and the last one can be null (I was relying on Reports text_io.fopen). Retry the function without the slash in the end of location parameter, with an extension in the filename parameter and with a lowered "w" at the open_mode parameter. Also add some debugging to have an idea where is the exception (make sure SERVEROUTPUT is ON):

dbms_output.put_line('I am opening the following file: '||'/home/gyan/tst','Dynamic-file-'||to_char(sysdate, 'mm-dd-yyyy')||'.txt');

f := UTL_FILE.FOPEN('/home/gyan/tst','Dynamic-file-'||to_char(sysdate, 'mm-dd-yyyy')||'.txt','w');

dbms_output.put_line('I successfuly opened the following file: '||'/home/gyan/tst','Dynamic-file-'||to_char(sysdate, 'mm-dd-yyyy')||'.txt');

Best Regards,

Eric Antunes