Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

possible file lock error

Gyankr
Frequent Advisor

possible file lock error

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
7 REPLIES
Jeeshan
Honored Contributor

Re: possible file lock error

Hi Gyan

This is a user specified error message

I think you can contact with your application vendor.

This is mostly happen in application.
a warrior never quits
Eric Antunes
Honored Contributor

Re: possible file lock error

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
Each and every day is a good day to learn.
Gyankr
Frequent Advisor

Re: possible file lock error

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
Honored Contributor

Re: possible file lock error

hi,

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

can you confirm this?

revert
kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Gyankr
Frequent Advisor

Re: possible file lock error

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
Frequent Advisor

Re: possible file lock error

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
Honored Contributor

Re: possible file lock error

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



Each and every day is a good day to learn.