Home » SQL & PL/SQL » SQL & PL/SQL » UTL File problem
icon8.gif  UTL File problem [message #111704] Fri, 18 March 2005 12:47 Go to next message
nathamar81
Messages: 3
Registered: March 2005
Location: Howrah
Junior Member

dear sir/madam..

i have written your UTL file code in the sql plus as mentioned in the Frequently asked questions "Can one read/write files from PL/SQL? "

But I am getting the error after running the last portion

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

I cannot understand why this is happening. I have tried a lot but unable
to get success. Can you please look after this? And give me a some idea about

UTL FILE and how to write or read in a UTL File..

Thank you

Amarnath

Re: UTL File problem [message #111706 is a reply to message #111704] Fri, 18 March 2005 12:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
did you create the directory/ grant permissions?
what is your oracle version?
Re: UTL File problem [message #111766 is a reply to message #111706] Sat, 19 March 2005 09:31 Go to previous messageGo to next message
nathamar81
Messages: 3
Registered: March 2005
Location: Howrah
Junior Member

Dear Mahesh...
I have pasted the code in the oracle which is given in the ORAFAQ.com site .....


CONNECT / AS SYSDBA
CREATE OR REPLACE DIRECTORY mydir AS '/tmp';
GRANT read, write ON DIRECTORY mydir TO scott;

//This part runs successfully...
//Problem is in the code in the bottom

CONNECT SCOTT/TIGER

DECLARE
fHandler UTL_FILE.FILE_TYPE;
BEGIN
fHandler := UTL_FILE.FOPEN('MYDIR', 'myfile', 'w');
UTL_FILE.PUTF(fHandler, 'Look ma, I''m writing to a file!!!n');
UTL_FILE.FCLOSE(fHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000,
'ERROR: Invalid path. Create directory or set UTL_FILE_DIR.');
END;
/


After typing this code i have got the error...

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



I cannot understand this problem. And i donot know much about the UTL file... Please provide me the solution.
My oracle version is 9i personal edition.

Thank you

Waiting for reply..

Amarnath


Re: UTL File problem [message #111770 is a reply to message #111766] Sat, 19 March 2005 14:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
Does the /tmp directory exist on the operating system? When you use the code "create or replace directory", it actually just creates an Oracle directory object that accepts any string without validation at the time of creation. It does not actually create a directory on the operating system. When you try to run your code, if the directory object that you have created points to a non-existent directory, it will produce the error that you are receiving. Please see the demonstration below that shows that, by reproducing your error, then creating the directory on the operating system, then re-running the code without error. Also, the directory must be on the server, not the client, and Oracle must have permission to write to it.

-- reproduction of your error:
scott@ORA92> CONNECT SYS AS SYSDBA
Connected.
scott@ORA92> @ LOGIN
scott@ORA92> SET ECHO OFF

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
sys@ORA92

sys@ORA92> CREATE OR REPLACE DIRECTORY mydir AS '/tmp'
  2  /

Directory created.

sys@ORA92> GRANT read, write ON DIRECTORY mydir TO scott
  2  /

Grant succeeded.

sys@ORA92> CONNECT SCOTT/TIGER
Connected.
sys@ORA92> @ LOGIN
sys@ORA92> SET ECHO OFF

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
scott@ORA92

scott@ORA92> DECLARE
  2    fHandler UTL_FILE.FILE_TYPE;
  3  BEGIN
  4    fHandler := UTL_FILE.FOPEN('MYDIR', 'myfile.txt', 'w');
  5    UTL_FILE.PUTF(fHandler, 'Look ma, I''m writing to a file!!!n');
  6    UTL_FILE.FCLOSE(fHandler);
  7  EXCEPTION
  8    WHEN utl_file.invalid_path THEN
  9    raise_application_error(-20000,
 10    'ERROR: Invalid path. Create directory or set UTL_FILE_DIR.');
 11  END;
 12  /
DECLARE
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation
ORA-06512: at line 4


-- create /tmp directory (your drive letter may be C):
scott@ORA92> HOST MD D:\tmp


-- now the code runs without error:
scott@ORA92> DECLARE
  2    fHandler UTL_FILE.FILE_TYPE;
  3  BEGIN
  4    fHandler := UTL_FILE.FOPEN('MYDIR', 'myfile.txt', 'w');
  5    UTL_FILE.PUTF(fHandler, 'Look ma, I''m writing to a file!!!n');
  6    UTL_FILE.FCLOSE(fHandler);
  7  EXCEPTION
  8    WHEN utl_file.invalid_path THEN
  9    raise_application_error(-20000,
 10    'ERROR: Invalid path. Create directory or set UTL_FILE_DIR.');
 11  END;
 12  /

PL/SQL procedure successfully completed.

scott@ORA92>


[Updated on: Sat, 19 March 2005 14:42]

Report message to a moderator

Re: UTL File problem [message #112378 is a reply to message #111770] Thu, 24 March 2005 13:47 Go to previous messageGo to next message
nathamar81
Messages: 3
Registered: March 2005
Location: Howrah
Junior Member

Dear Madam....
Thank you very much for your solution. Now i have no problem...
Hope i will get more help from this forum...

One information: Brainbench is giving the opportunity to give free test in their website. I request all the members to see that site. It may be helpfull.


Amarnath
Re: UTL File problem [message #332828 is a reply to message #111770] Wed, 09 July 2008 17:58 Go to previous messageGo to next message
live2learn
Messages: 34
Registered: February 2006
Member
Hi,

I wanted to know where this file and directory are stored exactly.

Because,

15:57:04 SQL> HOST RM C:\SAMPLE

15:57:16 SQL> declare
15:57:29 2 f utl_file.file_type;
15:57:29 3 s varchar2(200);
15:57:29 4 begin
15:57:29 5 f := utl_file.fopen('SAMPLE','test','R');
15:57:29 6 utl_file.get_line(f,s);
15:57:29 7 utl_file.fclose(f);
15:57:29 8 dbms_output.put_line(s);
15:57:29 9 end;
15:57:29 10 /
Look ma, I'm writing to a file!!!n

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
15:57:30 SQL>

--> I removed the directory.
--> Still I can view the contents of file.

Thanks,
Saritha
Re: UTL File problem [message #332829 is a reply to message #111704] Wed, 09 July 2008 18:05 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>--> I removed the directory.
I don't believe you.
1) since when is rm a valid command for Windoze?
2) since when can you delete a directory with a file in it?

>--> Still I can view the contents of file.
Expected behavior
Re: UTL File problem [message #379842 is a reply to message #111770] Thu, 08 January 2009 02:10 Go to previous messageGo to next message
cheehongjb
Messages: 7
Registered: January 2009
Location: Kuala Lumpur - MALAYSIA
Junior Member

Dear Barbara,

below is what i already done.

SQL> conn / as sysdba
Connected.
SQL> CREATE OR REPLACE DIRECTORY REPORT_DIR AS '/home/online/reports/xml';

Directory created.

SQL> GRANT read, write on DIRECTORY REPORT_DIR TO ONLINEDBS;

Grant succeeded.

but when i try to the below command it return me this error.

SQL> HOST dir REPORT_DIR
dir: REPORT_DIR: No such file or directory
Re: UTL File problem [message #379848 is a reply to message #379842] Thu, 08 January 2009 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
REPORT_DIR is Oracle directory name
'/home/online/reports/xml' is associated OS directory name
HOST calls OS command, so refers to OS object not Oracle one.

Regards
Michel
Re: UTL File problem [message #441621 is a reply to message #111704] Tue, 02 February 2010 04:27 Go to previous messageGo to next message
kevser86
Messages: 1
Registered: February 2010
Junior Member
Hi,

I create table in my local from a xml by this method. But when i click right this table and i get warning : "edit table" , "One or more UTF8 fields contain non-UTF8 data,editing might give unexpected results"

Table contains Turkish letters and I set my nls_lang to TURKISH_TURKEY.AL32UTF8.

How can I fix this warning ?

Re: UTL File problem [message #441622 is a reply to message #441621] Tue, 02 February 2010 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't see what is the relation between your question and the current topic.
Can you explain?

Regards
Michel
Re: UTL File problem [message #444064 is a reply to message #441622] Thu, 18 February 2010 10:42 Go to previous messageGo to next message
nastradl
Messages: 9
Registered: February 2010
Junior Member
Hi,

Still along the same lines...

I've done this example and it compiles fine, runs fine. But when I go to my C:/TMP folder and open the myfile.txt, it is empty. Can you think of any reason why this happens?

Thank you
Re: UTL File problem [message #444066 is a reply to message #444064] Thu, 18 February 2010 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
UTL_FILE read LOCAL database server files not client one.

Regards
Michel
Re: UTL File problem [message #444070 is a reply to message #444066] Thu, 18 February 2010 10:59 Go to previous messageGo to next message
nastradl
Messages: 9
Registered: February 2010
Junior Member
Hi again,

fHandler := UTL_FILE.FOPEN('MYDIR', 'myfile.txt', 'w');
UTL_FILE.PUTF(fHandler, 'Look ma, I''m writing to a file!!!n');
UTL_FILE.FCLOSE(fHandler);

Doesn't this mean I'm opening the myfile.txt (on the client system) and writing 'look ma..etc' into it? I thought this example shows how to write to file from the database using PL/SQL?

Sorry if i'm mucking things up. I'm actually trying to write to file a blob from the database into a folder on my system and this example seemed very similar (albeit more basic) but i couldn't even get this to work the way i wanted it to.

Thanks again for the quick reply
Re: UTL File problem [message #444071 is a reply to message #444070] Thu, 18 February 2010 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Doesn't this mean I'm opening the myfile.txt (on the client system)

Quote:
UTL_FILE read/write LOCAL database server files not REMOTE client one.


What is not clear in my answer?

Regards
Michel
Re: UTL File problem [message #444073 is a reply to message #444070] Thu, 18 February 2010 11:04 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
> I thought this example shows how to write to file from the database using PL/SQL?
PL/SQL runs inside the database which is on the DB server system & therefore can only access files local to DB server!
Re: UTL File problem [message #444076 is a reply to message #444073] Thu, 18 February 2010 11:11 Go to previous messageGo to next message
nastradl
Messages: 9
Registered: February 2010
Junior Member
I think I understand now. I'm just learning PL/SQL so forgive me if i seem a bit thick. Could you recommend a thread or website where I could read up on how to write data from a database unto the remote client? (specifically retrieve a blob from a table and save it in a folder on a windows OS machine)?
Re: UTL File problem [message #444078 is a reply to message #444076] Thu, 18 February 2010 11:15 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
What technology stack allows the PC client to "talk" to Oracle RDBMS?
Please enumerate all intervening components.
Re: UTL File problem [message #444080 is a reply to message #444078] Thu, 18 February 2010 11:23 Go to previous messageGo to next message
nastradl
Messages: 9
Registered: February 2010
Junior Member
Maybe If I explain what i'm actually trying to do:

I have a database with a table containing 1000's of fingerprints. I want to write a script to backup all the blobs in the table on a hard-disk in a specified format where each fingerprint is saved with a different name in it's own folder.

Tall order for someone who just started using Databases but it's what I need to do. I was adviced that PL/SQL was the way to go so I started reading up and practicing it. I thought this example was a leading in the right direction so was trying it out.
Re: UTL File problem [message #444082 is a reply to message #444080] Thu, 18 February 2010 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you use export or data pump depending on your version?

Regards
Michel
Re: UTL File problem [message #444084 is a reply to message #444082] Thu, 18 February 2010 11:36 Go to previous messageGo to next message
nastradl
Messages: 9
Registered: February 2010
Junior Member
I'm using Oracle Client 10g.

I hadn't considered using Data pump/export. I didn't know that could be used as part of a script. The idea was to have a One-click solution for any Tom, Jack or Daniella to run every month and have all the blobs backed up on a hard-disk.
Re: UTL File problem [message #444094 is a reply to message #444084] Thu, 18 February 2010 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can schedule Data Pump with a job.

I advice you to hire a senior DBA for one day that can make this for you.

Regards
Michel
Re: UTL File problem [message #444164 is a reply to message #444084] Fri, 19 February 2010 00:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What is the primary goal? To have a backup of the data, or to have the contents stored in the specific format and have copies of that?
If it is the latter, datapump will not help you here.
Re: UTL File problem [message #444180 is a reply to message #444164] Fri, 19 February 2010 02:21 Go to previous messageGo to next message
nastradl
Messages: 9
Registered: February 2010
Junior Member
"What is the primary goal? To have a backup of the data, or to have the contents stored in the specific format and have copies of that?
If it is the latter, datapump will not help you here."

It is the latter. I will see if I can export the data and then have a batch script that rearranges it and changes the format. Somehow, i'm starting to think I've bit off more than I can chew..
Re: UTL File problem [message #444183 is a reply to message #444180] Fri, 19 February 2010 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If this is the latter case, then I think you could write a small application (VB?) to make these copies.
I bet you will find some on Google.

Regards
Michel
Re: UTL File problem [message #444229 is a reply to message #111704] Fri, 19 February 2010 08:41 Go to previous messageGo to next message
nastradl
Messages: 9
Registered: February 2010
Junior Member
Thanks for all replies on this. Really appreciate it. Will see what I can find online.
Re: UTL File problem [message #467580 is a reply to message #444071] Mon, 26 July 2010 02:29 Go to previous messageGo to next message
edistizu
Messages: 4
Registered: July 2010
Location: Bosnia and Hercegovina
Junior Member

Is there really no way tu use UTL_FILE to read/write files on the client machine?

I am only asking because i heard someone saying that it could be possible if you share the folders on the client machine so that the server can see them . . .

Anyone ever tried this?
Re: UTL File problem [message #467587 is a reply to message #467580] Mon, 26 July 2010 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you try yourself and post the answer?

Regards
Michel
Re: UTL File problem [message #467615 is a reply to message #467587] Mon, 26 July 2010 04:39 Go to previous messageGo to next message
edistizu
Messages: 4
Registered: July 2010
Location: Bosnia and Hercegovina
Junior Member
Well, this is what i tried:

I shared the folders on the client.
The folders are:
c:iveb\ip
c:iveb\is
c:iveb\ki
c:iveb\kp

Then i created the references and gave the read/write permissions:

CREATE OR REPLACE DIRECTORY iveb_ip AS '\\stjepan\iveb\ip';
CREATE OR REPLACE DIRECTORY iveb_is AS '\\stjepan\iveb\is';
CREATE OR REPLACE DIRECTORY iveb_ki AS '\\stjepan\iveb\ki';
CREATE OR REPLACE DIRECTORY iveb_kp AS '\\stjepan\iveb\kp';

--STJEPAN IS THE NAME OF THE COMP (CLIENT)

GRANT READ,WRITE ON DIRECTORY iveb_ip TO PUBLIC;
GRANT READ,WRITE ON DIRECTORY iveb_is TO PUBLIC;
GRANT READ,WRITE ON DIRECTORY iveb_ki TO PUBLIC;
GRANT READ,WRITE ON DIRECTORY iveb_kp TO PUBLIC;

This is an example of one of my procedures:
declaration:
infile UTL_FILE.file_type;
.
.
.
infile:=UTL_FILE.fopen('IVEB_IS',imedat,'w',max_linesize);
.
.(imedat is a variable that has the file name 'stored')
.
UTL_FILE.PUT(infile,RTRIM(slog));
UTL_FILE.NEW_LINE(infile);
.
.
UTL_FILE.fclose(infile);

SQL NAVIGATOR RETURNS NO ERRORS WHEN I TEST IT, BUT PRODUCTION IS ANOTHER THING!

I get the ORA-29283 WHICH BASICALY MEANS that either there is no file or directory, or if there is, i don't have access to it.
I read somewhere that the INIT.ORA file needs to be edited?
Anyone tried this already?
Any help is much appreciated.

Hope i am being understood, it's been a while since i have used English language. . .
Re: UTL File problem [message #467616 is a reply to message #467615] Mon, 26 July 2010 04:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Most of the time the owner of the instance (the user that starts the service) is local system which has no privilege on network including share drives.

Regards
Michel
Re: UTL File problem [message #467619 is a reply to message #467616] Mon, 26 July 2010 05:07 Go to previous messageGo to next message
edistizu
Messages: 4
Registered: July 2010
Location: Bosnia and Hercegovina
Junior Member
So if i understand correctly, even though the user of the comp is trying tu use directories
that exist on his filesystem, since he is trying to access them 'indirectly' through the server,
he needs to be granted access rights by the DBA . . .

Will try, thanks for the reply . . .
Re: UTL File problem [message #467627 is a reply to message #467619] Mon, 26 July 2010 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, the OS account that started the instance/service must have network privileges in Windows meaning, nothin related to Oracle privileges.

Regards
Michel
Re: UTL File problem [message #467631 is a reply to message #467627] Mon, 26 July 2010 05:54 Go to previous messageGo to next message
edistizu
Messages: 4
Registered: July 2010
Location: Bosnia and Hercegovina
Junior Member
OK Michel,
understood.

Thanks again,
i won't stop 'till it works, will let you know when it does.
Re: UTL File problem [message #467648 is a reply to message #467631] Mon, 26 July 2010 07:30 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I might be easier the other way around, though:

Create a directory on the SERVER, then share that directory so that clients can write files to that directory, and then access that directory from the database.

That way you don't have to configure every client to share a directory.
Re: UTL File problem [message #467656 is a reply to message #467648] Mon, 26 July 2010 07:51 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That seems to be the more logical way to do it, the (database) server is the (file) server and the (database) client is the (file) client. Smile

Regards
Michel
Previous Topic: schema
Next Topic: Package - Warning on Compile
Goto Forum:
  


Current Time: Mon Dec 05 08:47:47 CST 2016

Total time taken to generate the page: 0.16201 seconds