Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE.FOPEN got ORA-29283 [merged and dupe removed]
UTL_FILE.FOPEN got ORA-29283 [merged and dupe removed] [message #362877] Thu, 04 December 2008 06:37 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #362894 is a reply to message #362892] Thu, 04 December 2008 07:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Also, please don't multipost
Re: UTL_FILE.FOPEN got ORA-29283 [message #363035 is a reply to message #362892] Thu, 04 December 2008 21:26 Go to previous messageGo to next message
asayida
Messages: 5
Registered: December 2008
Location: Indonesia
Junior Member

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


Re: UTL_FILE.FOPEN got ORA-29283 [message #363182 is a reply to message #363035] Fri, 05 December 2008 05:25 Go to previous message
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 !
Previous Topic: Inserting the Comma Separated Strings into Table
Next Topic: DBMS_job/scheduler
Goto Forum:
  


Current Time: Tue Nov 05 12:57:39 CST 2024