OraFAQ Forum: SQL & PL/SQL » UTL_FILE problem with session

Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE problem with session

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

UTL_FILE problem with session [message #473748]

Tue, 31 August 2010 11:09

pradeepsakti
Messages: 37
Registered: May 2009

Member

Dears,

can anyone please help me out by finding this reason.

I am doing some file operations using CLOB and I mean exactly "there are some 100+ files on a unix directory and I am reading each one them file by file and doing CLOB operations with the contents of the files, say one clob object for one file".

But exactly after 46 or 47 file reads I get the error

ORA-29283: invalid file operation
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449

If I logoff and logon then it does 46 files and again error.

Please help me out to find this.

Thanks a lot in advance,
Pradeep.

Report message to a moderator

Re: UTL_FILE problem with session [message #473750 is a reply to message #473748]

Tue, 31 August 2010 11:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

29283, 00000, "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.

Are OS owner & permissions EXACTLY the same for all files involved?

Report message to a moderator

Re: UTL_FILE problem with session [message #473753 is a reply to message #473750]

Tue, 31 August 2010 11:24

pradeepsakti
Messages: 37
Registered: May 2009

Member

Thx for the reply.

And there are no problem with directory path and permsions, filenames.

As all the files under same directory with same permissions more over the file which was failed in this session will be read in the next.

Report message to a moderator

Re: UTL_FILE problem with session [message #473755 is a reply to message #473753]

Tue, 31 August 2010 11:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>And there are no problem with directory path and permsions, filenames.
>As all the files under same directory with same permissions more over the file which was failed in this session will be read in the next.

Either you are mistaken or Oracle is.
If you are confident that all is OK in your environment, then submit a bug report to Oracle.

How can we reproduce what you report?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Tue, 31 August 2010 11:28]

Report message to a moderator

Re: UTL_FILE problem with session [message #473761 is a reply to message #473755]

Tue, 31 August 2010 11:36

pradeepsakti
Messages: 37
Registered: May 2009

Member

neither me nor oracle is mistaken I think.
May be some thing with session memory or kind of that is the issue as I am using CLOBS and exactly 46 files the error comes..

may be a DBA ll help me?

Report message to a moderator

Re: UTL_FILE problem with session [message #473763 is a reply to message #473761]

Tue, 31 August 2010 11:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>May be some thing with session memory or kind of that is the issue as I am using CLOBS and exactly 46 files the error comes..
We do not know your Operating System name or version.
We do not know Oracle version to 4 decimal places.
Problem may be related to number of OS file.
Problem may be related to sum of OS file size.
Problem may be related to phase of the moon.
Other than number 46 or 47, we have NO facts upon which to make ANY guess or conclusion.

Report message to a moderator

Re: UTL_FILE problem with session [message #473768 is a reply to message #473761]

Tue, 31 August 2010 11:53

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Maybe or maybe not if you don't give him more information than you give us.

Regards
Michel

Report message to a moderator

Re: UTL_FILE problem with session [message #473770 is a reply to message #473768]

Tue, 31 August 2010 11:56

pradeepsakti
Messages: 37
Registered: May 2009

Member

I use oracle 9i and the files are on HP UNIX server
I dont have any infor on db file sizes

any other info required?

Report message to a moderator

Re: UTL_FILE problem with session [message #473771 is a reply to message #473770]

Tue, 31 August 2010 11:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What happens if you rename the first 40 files into a new directory & re-run code?
Does it succeed?
Does it fail? If fails, at what point does it fail? Fail with same error?

Report message to a moderator

Re: UTL_FILE problem with session [message #473833 is a reply to message #473748]

Wed, 01 September 2010 01:13

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Does all the files have WRITE permission ?

chmod 777 mydir

This link may be helpful

Regards
Ved

Report message to a moderator

Re: UTL_FILE problem with session [message #473836 is a reply to message #473833]

Wed, 01 September 2010 01:25

_jum
Messages: 577
Registered: February 2008

Senior Member

You seem to open the file_handles and forget to close them, this gives an ORA-29283 after some (OS depend) times, so use

UTL_FILE.fclose(filehandler);

in your LOOP;

Report message to a moderator

Re: UTL_FILE problem with session [message #473884 is a reply to message #473836]

Wed, 01 September 2010 07:05

pradeepsakti
Messages: 37
Registered: May 2009

Member

Dear Jum thanks a lot for the reply, yes there is a loop in which I dint close the file.

And Dear Black Swan there is a reason for the NUMBER 48..

FOPEN Function

This function opens a file. You can specify the maximum line size and have a maximum of 50 files open simultaneously.

So the reason I got the particular error now its solved anyways.

Report message to a moderator

Previous Topic:	Part Number and Manufacturer Combination Must be Unique
Next Topic:	Using correlated values in nested subquery

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 01 11:32:03 CDT 2025