topic Re: possible file lock error in Operating System - HP-UX

possible file lock error

Gyankr — Sun, 06 Jan 2008 17:42:42 GMT

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

Re: possible file lock error

Jeeshan — Sun, 06 Jan 2008 17:59:14 GMT

Hi Gyan

This is a user specified error message

I think you can contact with your application vendor.

This is mostly happen in application.

Re: possible file lock error

Eric Antunes — Mon, 07 Jan 2008 10:43:32 GMT

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

Re: possible file lock error

Gyankr — Mon, 07 Jan 2008 16:44:27 GMT

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

Re: possible file lock error

Yogeeraj_1 — Mon, 07 Jan 2008 17:54:38 GMT

hi,

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

can you confirm this?

revert
kind regards
yogeeraj

Re: possible file lock error

Gyankr — Mon, 07 Jan 2008 18:21:14 GMT

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

Re: possible file lock error

Gyankr — Mon, 07 Jan 2008 18:46:46 GMT

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

Re: possible file lock error

Eric Antunes — Tue, 08 Jan 2008 09:42:09 GMT

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