UTL_FILE.FOPEN got ORA-29283 [merged and dupe removed] [message #362877] |
Thu, 04 December 2008 06:37 |
asayida
Messages: 5 Registered: December 2008 Location: Indonesia
|
Junior Member |
|
|
Hello,
I've tried to create a new text file using UTL_FILE, the test file can't be produced and I got errors below when i was executing or debugging my procedure, which occured in line :
"v_file := UTL_FILE.FOPEN('MPI_INT_ORD_MGMT',v_name,'W',32767);"
ORA-29283: invalid file operation
ORA-06512 : at "SYS.UTL_FILE", line 449
ORA-29283 : invalid file operation
ORA-06512 : at "APPS.MII_DROPPING1", line 9
ORA-06512 : at line 2
The procedure & directory:
CREATE OR REPLACE PROCEDURE APPS.mii_dropping1
is
v_file UTL_FILE.FILE_TYPE;
v_name VARCHAR2(128) := 'utl_file_untuned.txt';
v_lines PLS_INTEGER := 0;
BEGIN
v_file := UTL_FILE.FOPEN('MPI_INT_ORD_MGMT',v_name,'W',32767)
FOR r IN (SELECT 'x' || ',' || 'y' || ',' || 'z' AS csv
FROM dual)
LOOP
UTL_FILE.PUT_LINE(v_file, r.csv);
v_lines := v_lines + 1;
END LOOP;
UTL_FILE.FCLOSE(v_file);
END mii_dropping1;
/
CREATE OR REPLACE DIRECTORY
MPI_INT_ORD_MGMT AS
'/oracle/PROD/prodappl/ont/11.5.0/int/out';
GRANT READ, WRITE ON DIRECTORY SYS.MPI_INT_ORD_MGMT TO PUBLIC WITH GRANT OPTION;
I've googling about ORA-29283
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.
so checked the path (which is correct and chmod 777 them), no file exists in the path after execution
OS : SunOS 5.10
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
Can you help me with things I missed...?
[Updated on: Thu, 04 December 2008 08:04] by Moderator Report message to a moderator
|
|
|
Re: UTL_FILE.FOPEN got ORA-29283 [message #362892 is a reply to message #362877] |
Thu, 04 December 2008 07:23 |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
How about copy & paste the execution of your pl/sql proc from sql*plus prompt and also the the permission of the directory from unix ? It's not we are not trusting you but it will be ideal to see all those steps you have executed. So it includes a) checking the directory exists in the os with right permissions
b) Create the directory object
c) Grant the directory object to the respective user
d) Execute the proc.
Also tell us how do you connect to the database. Do you go by listener or do you connect directly. Is listener running under the same user as the Oracle Instance or is it different. To find out execute the following command
ps -ef|grep pmon_$ORACLE_SID
ps -ef|grep tnslsnr
Last but not least please format your code. For how to format your code read the forum guidelines.
Regards
Raj
|
|
|
Re: UTL_FILE.FOPEN got ORA-29283 [message #362893 is a reply to message #362877] |
Thu, 04 December 2008 07:24 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I might be a bit careful about making direcotries in your Oracle HOme on your production box have a permission of 777 if I were you.
Can you efinitely go to the specified directory as the Oracle user and create a file in tha directory. Don't assume you can - actually do it now.
Are you sure this is the code you're running - I had to add a missing semi-colon onto the FOPEN line to get it to run. Pleae post the exact code (cut and paste, not retyped).
This code works fine for me, but I've got a 10g box to test it on, not 9i.
|
|
|
|
|
Re: UTL_FILE.FOPEN got ORA-29283 [message #363182 is a reply to message #363035] |
Fri, 05 December 2008 05:25 |
asayida
Messages: 5 Registered: December 2008 Location: Indonesia
|
Junior Member |
|
|
asayida wrote on Fri, 05 December 2008 10:26 | S.Rajaram wrote on Thu, 04 December 2008 20:23 | How about copy & paste the execution of your pl/sql proc from sql*plus prompt and also the the permission of the directory from unix ? It's not we are not trusting you but it will be ideal to see all those steps you have executed. So it includes a) checking the directory exists in the os with right permissions
b) Create the directory object
c) Grant the directory object to the respective user
d) Execute the proc.
Also tell us how do you connect to the database. Do you go by listener or do you connect directly. Is listener running under the same user as the Oracle Instance or is it different. To find out execute the following command
ps -ef|grep pmon_$ORACLE_SID
ps -ef|grep tnslsnr
Last but not least please format your code. For how to format your code read the forum guidelines.
Regards
Raj
|
Raj, the result I posted previously was executed the proc from sql+ in the apps server
This make me wonder, whether oracle directory is specified to path in DB server or APPS server (different IP). I defined the oracle directory as existed in the APPS server.
before I execute the procedure, I execute the corresponding environment (.env) files, each time whether in APPS server or DB server. I'm not sure is this means that I run by listener? because echo $ORACLE_SID in APPS server return nothing but execute sqlplus user/pass (without @) in APPS server is fine
==ps -ef|grep pmon==
run from APPS server : no result but i can execute sqlplus without explicitly state the SID e.g: sqlplus apps/apps [ENTER]
run from DB server, result:
oraprod 3838 1 0 Nov 25 ? 5:54 ora_pmon_PROD
==ps -ef|grep tnslsnr==
run from APPS server :
applprod 20168 1 0 Nov 25 ? 0:01 /oracle/PROD/prodora/8.0.6/bin/tnslsnr APPS_PROD -inherit
run from DB server :
oraprod 3880 1 0 Nov 25 ? 0:44 /oracle/PROD/proddb/9.2.0/bin/tnslsnr PROD -inherit
waiting your kind respond
Thanks Raj,
Ryan
|
Dear all..
I've found the root cause. It is in the oracle directory which is defined to the path existed in APPS server. So I changed to another path in DB server, and my problem is solved.
Thanks folks !
|
|
|