|
|
|
|
|
|
| Re: UTL File problem [message #111770 is a reply to message #111766] |
Sat, 19 March 2005 14:40   |
 |
Barbara Boehmer
Messages: 9090
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 #332828 is a reply to message #111770] |
Wed, 09 July 2008 17:58 |
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 #441621 is a reply to message #111704] |
Tue, 02 February 2010 04:27 |
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 #444064 is a reply to message #441622] |
Thu, 18 February 2010 10:42 |
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 #444070 is a reply to message #444066] |
Thu, 18 February 2010 10:59 |
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 #444076 is a reply to message #444073] |
Thu, 18 February 2010 11:11 |
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 #444080 is a reply to message #444078] |
Thu, 18 February 2010 11:23 |
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 #444084 is a reply to message #444082] |
Thu, 18 February 2010 11:36 |
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 #444164 is a reply to message #444084] |
Fri, 19 February 2010 00:57 |
Frank
Messages: 7901
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 |
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 #467580 is a reply to message #444071] |
Mon, 26 July 2010 02:29 |
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 #467615 is a reply to message #467587] |
Mon, 26 July 2010 04:39 |
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 #467619 is a reply to message #467616] |
Mon, 26 July 2010 05:07 |
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 #467648 is a reply to message #467631] |
Mon, 26 July 2010 07:30 |
ThomasG
Messages: 3211
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
