| ORA-29283: invalid file operation [message #585122] |
Wed, 22 May 2013 06:13  |
 |
arulsaran
Messages: 26
Registered: May 2013
Location: bangalore
|
Junior Member |
|
|
Hi,
My Application is running on the unix server - user oracle.
My DB is in different unix box. I had created the folder called OUT with the permission of 777 in DB server..
I created ORacle Directory DMPDIR for that folder /OUT. and executed grant read/write to that schema.
1) when i use the below code in sqlplus its writing the file into that folder.
DECLARE
vInHandle utl_file.file_type;
vNewLine VARCHAR2(4000);
BEGIN
vInHandle := utl_file.fopen('DMPDIR', 'text.txt', 'R',32767);
BEGIN
utl_file.get_line(vInHandle, vNewLine);
dbms_output.put_line(vNewLine);
EXCEPTION
WHEN OTHERS THEN
null;
END;
END fopen;
But, when the same piece of code is executed from the package ( which called from the application)its throwing the ORA-29283: invalid file operation.
even i am able to copy the files into that folder with the user oracle.
Any idea, wat i am missing ?.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ORA-29283: invalid file operation [message #585128 is a reply to message #585127] |
Wed, 22 May 2013 06:49  |
 |
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
What did you add to "capture error message"? You capture nothing, but pretend that everything is OK.
Oracle
ORA-29283: 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.
[Updated on: Wed, 22 May 2013 06:49] Report message to a moderator |
|
|
|
| Re: ORA-29283: invalid file operation [message #585129 is a reply to message #585128] |
Wed, 22 May 2013 06:54 |
|
arulsaran
Messages: 26
Registered: May 2013
Location: bangalore
|
Junior Member |
|
|
Thanks.
When you execute it from pl/sql developer , the pop up will come and say the error code when the exception is not defined.
Yes , all the access are there.
the folder has the option of chmode 777. from sqlplus the file is getting generated. But file open is returning the invalid file opearation error when it called from the package..
the below is the piece of cod which used in the package...
FUNCTION xxxx(p_msg_body IN CLOB,
p_file_name IN VARCHAR2,
p_file_path IN VARCHAR2,
p_Err_Code IN OUT VARCHAR2,
p_Err_Prms IN OUT VARCHAR2) RETURN BOOLEAN AS
l_file_type UTL_FILE.FILE_TYPE;
buffer VARCHAR2(32767);
amount BINARY_INTEGER;
offset NUMBER(38);
l_write boolean := true;
BEGIN
IF UTL_FILE.is_open(l_file_type) THEN
UTL_FILE.FCLOSE(l_file_type);
END IF;
begin
l_file_type := UTL_FILE.FOPEN('DMPDIR', p_file_name, 'W', 32767);
exception
WHEN OTHERS THEN
l_write := false;
end;
if l_write then
WHILE amount >= buffer_size LOOP
DBMS_LOB.READ(lob_loc => p_msg_body,
amount => amount,
offset => offset,
buffer => buffer);
offset := offset + amount;
UTL_FILE.PUT(file => l_file_type, buffer => buffer);
UTL_FILE.FFLUSH(file => l_file_type);
END LOOP;
end if;
g_file_cnt := g_file_cnt + 1;
utl_file.fclose(l_file_type);
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END xxxx;
[Updated on: Wed, 22 May 2013 06:55] Report message to a moderator |
|
|
|
|
|
| Re: ORA-29283: invalid file operation [message #585131 is a reply to message #585130] |
Wed, 22 May 2013 07:04 |
|
arulsaran
Messages: 26
Registered: May 2013
Location: bangalore
|
Junior Member |
|
|
Sorry about the formatting.
1)
DECLARE
vInHandle utl_file.file_type;
vNewLine VARCHAR2(4000);
v_sql varchar2(3000);
BEGIN
vInHandle := utl_file.fopen('DMPDIR', 'text.txt', 'W', 32767);
utl_file.put_line(vInHandle, 'ARULSARAN');
dbms_output.put_line(vNewLine);
EXCEPTION
WHEN OTHERS THEN
v_sql := substr(sqlerrm,1,3000);
dbms_output.put_line (v_sql);
END;
2)
FUNCTION xxxx(p_msg_body IN CLOB,
p_file_name IN VARCHAR2,
p_file_path IN VARCHAR2,
p_Err_Code IN OUT VARCHAR2,
p_Err_Prms IN OUT VARCHAR2) RETURN BOOLEAN AS
l_file_type UTL_FILE.FILE_TYPE;
buffer VARCHAR2(32767);
amount BINARY_INTEGER;
offset NUMBER(38);
l_write boolean := true;
v_sql varchar2(3000);
BEGIN
IF UTL_FILE.is_open(l_file_type) THEN
UTL_FILE.FCLOSE(l_file_type);
END IF;
begin
l_file_type := UTL_FILE.FOPEN('DMPDIR', p_file_name, 'W', 32767);
exception
WHEN OTHERS THEN
v_sql := substr(sqlerrm, 1, 3000);
dbms_output.put_line(v_sql);
l_write := false;
end;
if l_write then
WHILE amount >= buffer_size LOOP
DBMS_LOB.READ(lob_loc => p_msg_body,
amount => amount,
offset => offset,
buffer => buffer);
offset := offset + amount;
UTL_FILE.PUT(file => l_file_type, buffer => buffer);
UTL_FILE.FFLUSH(file => l_file_type);
END LOOP;
end if;
g_file_cnt := g_file_cnt + 1;
utl_file.fclose(l_file_type);
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
v_sql := substr(sqlerrm, 1, 3000);
dbms_output.put_line(v_sql);
RETURN FALSE;
END xxxx;
[Updated on: Wed, 22 May 2013 07:04] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: ORA-29283: invalid file operation [message #585137 is a reply to message #585136] |
Wed, 22 May 2013 07:26 |
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
arulsaran wrote on Wed, 22 May 2013 13:12question , what it cause the problem , what makes the different between running in sql plus and in package, why it cause the problem ?.
cookiemonster wrote on Wed, 22 May 2013 13:00
3) The function writes files. All the other code you've posted reads files. The fact that you can read a file doesn't prove you can write it. If you're trying to write to a file that already exists you should probably be using an open_mode of a (append).
Or to put it more simply (though I really shouldn't need to): the package code and the sqlplus code do different things
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ORA-29283: invalid file operation [message #585145 is a reply to message #585138] |
Wed, 22 May 2013 08:11 |
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
arulsaran wrote on Wed, 22 May 2013 13:37
2) i connect through sqlplus/pl-sql developer and ran the stuff which has the same line. file got created..
3) no matter what i am writing into that file.
l_file_type := UTL_FILE.FOPEN('DMPDIR', 'sample.txt', 'W', 32767);
Not according to all your previous posts you didn't.
1st post:
utl_file.fopen('DMPDIR', 'text.txt', 'R',32767);
2nd post:
utl_file.fopen('DMPDIR', 'text.txt', 'R',32767);
3rd post:
utl_file.fopen('DMPDIR', 'text.txt', 'R',32767);
See those 'R' - they're not the same as 'W' are they?
You need to start paying attention to what you write.
|
|
|
|
|
|
| Re: ORA-29283: invalid file operation [message #585209 is a reply to message #585190] |
Thu, 23 May 2013 00:34 |
|
arulsaran
Messages: 26
Registered: May 2013
Location: bangalore
|
Junior Member |
|
|
Littlefoot, Don't thing all will do what u do.. forget it. ( that mistake is happened because i was trying whether i am able to read the file from the folder ).
@Michel Cadot , Please see the out puts.. The thing is that the problem is in the production and i am supporting from the offshore.
1)
SQL> set serveroutput on;
SQL> DECLARE
2 vInHandle utl_file.file_type;
3 vNewLine VARCHAR2(4000);
4 v_sql varchar2(3000);
5 BEGIN
6 vInHandle := utl_file.fopen('DMPDIR', 'text.txt', 'W', 32767);
7 utl_file.put_line(vInHandle, 'ARULSARAN');
8 dbms_output.put_line(vNewLine);
9 EXCEPTION
10 WHEN OTHERS THEN
11 v_sql := substr(sqlerrm,1,3000);
12 dbms_output.put_line (v_sql);
13 END;
14 /
PL/SQL procedure successfully completed.
SQL>
File created in the Out directory
2)
the below function is present in the package i.e ( Pkg_write ).
FUNCTION fn_write_file(p_msg_body IN varchar2,
p_file_name IN VARCHAR2,
p_file_path IN VARCHAR2,
p_Err_Code IN OUT VARCHAR2,
p_Err_Prms IN OUT VARCHAR2) RETURN BOOLEAN AS
l_file_type UTL_FILE.FILE_TYPE;
buffer VARCHAR2(32767);
amount BINARY_INTEGER;
offset NUMBER(38);
l_write boolean := true;
v_sql varchar2(3000);
BEGIN
IF UTL_FILE.is_open(l_file_type) THEN
UTL_FILE.FCLOSE(l_file_type);
END IF;
begin
l_file_type := UTL_FILE.FOPEN('DMPDIR', p_file_name, 'W', 32767);
exception
WHEN OTHERS THEN
v_sql := substr(sqlerrm, 1, 3000);
dbms_output.put_line(v_sql);
l_write := false;
end;
utl_file.put_line (l_file_type , 'ARULSARAN');
utl_file.fclose(l_file_type);
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
v_sql := substr(sqlerrm, 1, 3000);
dbms_output.put_line(v_sql);
RETURN FALSE;
END fn_write_file;
----------
2) when i call this function from sqlplus its working fine.
SQL> ed
Wrote file afiedt.buf
1 declare
2 p_msg_body varchar2(100) :='ARULSARAN';
3 p_file_name varchar2(100) :='text1.txt';
4 p_file_path varchar2(100) :='DMPDIR';
5 p_Err_Code varchar2(100);
6 p_Err_Prms varchar2(100);
7 l_ret boolean;
8 begin
9 l_ret := Pkg_write.fn_write_file (
10 p_msg_body ,
11 p_file_name ,
12 p_file_path ,
13 p_Err_Code ,
14 p_Err_Prms );
15* end;
16 /
PL/SQL procedure successfully completed.
SQL>
3) but the same function is called via the front end application
its giving the below error.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ORA-29283: invalid file operation [message #585237 is a reply to message #585230] |
Thu, 23 May 2013 02:52 |
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Either you've rewritten bits of the function before posting it here or the function makes no sense.
The function has 5 parameters. The code uses 1 of them.
I suspect your real problem is that when the function is called from the application it isn't passing DMPDIR as the directory.
|
|
|
|
|
|
|
|
|
|
| Re: ORA-29283: invalid file operation [message #585258 is a reply to message #585255] |
Thu, 23 May 2013 03:55 |
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Or maybe the other code you left out is what's causing the problem.
Bottom line: if the function works from sqlplus then there shouldn't be any reason why it would fail when called from the application with the same parameters.
|
|
|
|
|
|
|
|
|
|
| Re: ORA-29283: invalid file operation [message #585274 is a reply to message #585271] |
Thu, 23 May 2013 06:51 |
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I'd double check that if I was you.
If you have a function that writes a file to a specific directory it'll work the same no matter where it's called from.
The only sensible thing to do is find out all the parameters the app is passing to the function then try calling the function from sqlplus with the exact same set of parameters.
|
|
|
|
|
|
| Re: ORA-29283: invalid file operation [message #585276 is a reply to message #585275] |
Thu, 23 May 2013 07:15 |
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
arulsaran wrote on Thu, 23 May 2013 12:57
('/u002fs/debug/OUT'
The above is not an oracle directory object name. So if it's not translated into a directory object name before calling utl_file that'll cause your error. If it is translated then I suggest you double check what it is translated into. I rather suspect it's not DMPDIR.
Everything you've posted so far points towards the problem being due to the application not writing the file you think it is.
OS_USER is irrelevant to this.
|
|
|
|
| Re: ORA-29283: invalid file operation [message #585277 is a reply to message #585276] |
Thu, 23 May 2013 07:28 |
|
arulsaran
Messages: 26
Registered: May 2013
Location: bangalore
|
Junior Member |
|
|
Hi,
I did the below checks before i post it.
1) folder owner is oracle
2) ch mode 777
3) login through putty/winscp with the oracle user , i am able to create/delete/change the files
4) i logged into the appserver with the oracle user
connected through sqlplus ran the pl/sql code with the mentioned parameters , file get genareted
5) repeated point no 4 from db server. works well
6) Directory dmpdir granted read /write to schema user.
7) we modified that function for reading the file,it gave the same error.
8) *** if os_user returns null, what might be the file owner , i am not good at unix.
App server is running in different UNIX box and DB server in different UNIX box.
Both app and db is running in the oracle user ( this is confirmed by the DBA's).
'/u002fs/debug/OUT' is maintained in the table, and initially i thought the path might be the problem.
i had changed it to the directory with the grants.. for checking purpose ,i hard coded the directory in the function.
This function call is made from java. i did the double check, this call is not made from the oracle jobs.
path / filename everything i gave it what i mentioned in the previous post. Still failing.
[CM - disabled smilies]
[Updated on: Thu, 23 May 2013 07:47] by Moderator Report message to a moderator |
|
|
|
| Re: ORA-29283: invalid file operation [message #585279 is a reply to message #585277] |
Thu, 23 May 2013 07:47 |
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
arulsaran wrote on Thu, 23 May 2013 13:28
8) *** if os_user returns null, what might be the file owner , i am not good at unix.
oracle. All files written by utl_file are actual written by the oracle processes, which all run under the oracle user.
The os_user of the client (sqlplus or the application), which is what sys_context gives, is irrelevant.
arulsaran wrote on Thu, 23 May 2013 13:28
'/u002fs/debug/OUT' is maintained in the table, and initially i thought the path might be the problem.
As I said before, unless that path is converted to the name of an oracle directory object before calling utl_file it's definitely a problem.
Only two users are relevant here:
1) the os user of oracle itself on the DB server - which should be called oracle. It needs read/write on the directory on the db server.
2) The oracle db user you're logged in as. That user needs read/write on the oracle directory object that you are trying to use.
Are you sure java is logged in as the same user you are using in sqlplus?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.. this piece i extracted and modified from the original package code in the understandable manner.