Java = Standard Packages Killer ?
Mauricio de Barros Borges, June 06, 2002 - 2:50 pm UTC
Tom,
I have already had problems with this limitation of UTL_FILE (unique output directory). We solved it using shell scripts runned as Unix crontab jobs that distributed the files from this directory to a whole directory tree.
But, my point here is: you solved the problem easily with Java. It's more powerfull and extensible: do you (and Oracle) think that Java Stored Procedures will replace the PL/SQL standard packages and they will be maintained for "backward compatibility purposes only" in the future ?
Maurício
June 06, 2002 - 6:01 pm UTC
You need to read my book.
There are advantages to PLSQL -- do this in java:
begin
for x in ( select * from emp ) loop
..... process record
end loop;
end;
tell me how much code you have to write?
PLSQL lives
PLSQL is being enhanced (9i R2 has new plsql features)
New server functionality is exposed as PLSQL (dbms_ utl_ packages)
There are times when a little java is good, times when plsql is good. Me -- I use a little java to spice up the plsql -- to get it to do things PLSQL as a language is not adept at.
Java is a non-trivial language, it is huge. PLSQL is small, simple, yet surprisingly complete (hey, if they could write advanced replication in Oracle 7.1.6 entirely 100% in plsql !!)
There is room for more then one language in the world. I use SQL until i cannot do it in SQL. Then I use PLSQL. I use that until I cannot do it with that language (or it is too awkward) -- then I use java. When I cannot do it in Java -- I do it in C (hey, if C is a replacement for Java when Java cannot do it or cannot do it efficiently -- maybe we should all write in C -- or assembler?)
PLSQL is here to stay, don't worry about that. No, I do not forsee the plsql statement packages being rewritten in Java (most of them are written mostly in C today actually and are just wrappers)
and I should have mentioned...</code>
http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/u_file.htm#998101 <code>
In Oracle9iR2 -- utl_file would be "flexible" enough with directory objects to do this. You can add directories on the fly.
<quote>
In the past, accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is not recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR. Directory objects offer more flexibility and granular control to the UTL_FILE application administrator, can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools. CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.
</quote>
Nice answer
Mauricio de Barros Borges, June 06, 2002 - 8:24 pm UTC
First of all, calm down, man, it seems that my question just offended your belief on PL/SQL: that was not my intention....
I am a PL/SQL programmer myself and I do like the language very much (already had to defend PL/SQL solutions over Java solutions): your problem resolution path (first SQL, then PL/SQL, Java, C...) is what I use as well, always. It's just that sometimes I saw people getting stuck trying to solve something using PL/SQL when solving the problem using Java (or C) would be much easier, cleaner...
And I asked because I've heard sometime ago that Oracle would deprecate PL/SQL in favor of Java and I wanted to hear the opinion of who is "inside the corp"... Thank you, you answer was very useful !
By the way, I read your entire book already ! Very good one, indeed !
Thank you !
June 06, 2002 - 9:31 pm UTC
;)
no worries...
But why is the Oracle connection is required
A reader, January 15, 2003 - 5:58 am UTC
Hi Tom
ClobUtil is not doing any database activity, so why is an Oracle connection opened?
Thanks
January 15, 2003 - 8:42 am UTC
because I cut and pasted the code from a routine that lived outside the database, where jdbc is brain dead (or optimized for databases that despise things like concurrency controls, data integrity).
there:
17 conn.setAutoCommit(false);
18
is mandatory. when i plopped it in the database -- instead of removing that, I grabbed the connection (it is a freebie -- it doesn't cost anything really, it is already there)
Hard coding UTL_FILE_DIR
Sushama Jaiswal, January 21, 2003 - 5:01 pm UTC
In all the examples that I have seen so far, the UTL_FILE_DIR (example ('d:\tmp' or '\tmp') is always harcoded in the procedure, or passed as a parameter to the procedure.
My question is how could I not hard code the directory name in a variable and/or pass it as parameter. Instead I want the procedure to determine the directory from v$parameter
But if I include this line in PL/SQL procedure
vDirectory varchar2(50);
select value into vDirectory
from V$PARAMETER
where lower(name) = 'utl_file_dir';
or use NDS for the same, the compiler or run time(with NDS) says
'Table or View does not exist'
I need this so that the procedure is independent of the database setup(whether it is QA or prod)
January 21, 2003 - 5:41 pm UTC
Well, you could use Oracle9iR2 which lets you
create or replace directory my_dir as '/tmp';
and then use 'MY_DIR' in UTL_FILE instead of a real path name...
You could put it into your OWN table and fetch it out...
Bear in mind that in general utl_file_dir is a LIST of directories, not a single entity:
utl_file_dir = /tmp /tmp/foo /tmp/bar
so I wouldn't use it (utl_file_dir). Use your own table.
Read:
</code>
http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>
to discover they why behind your pls-201 error
How do I solve this error
Brijesh, January 22, 2003 - 1:04 am UTC
sys@ORA92> ed
Wrote file afiedt.buf
1 begin
2 dbms_java.grant_permission ('SCOTT', 'java.io.FilePermission', 'E:\-', 'write');
3* end;
sys@ORA92> /
begin
*
ERROR at line 1:
ORA-04031: unable to allocate 4032 bytes of shared memory ("shared
pool","JAVA$POLICY$SHARED$0000000dSYS","joxlod: in ehe","ioc_allocate_pal")
ORA-06512: at "SYS.DBMS_JAVA", line 0
ORA-06512: at line 2
sys@ORA92> show parameter shared_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 1677721
shared_pool_size big integer 33554432
sys@ORA92> show parameter java_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 4194304
sys@ORA92> alter system flush shared_pool;
System altered.
sys@ORA92> begin
2 dbms_java.grant_permission ('SCOTT', 'java.io.FilePermission', 'E:\-', 'write');
3 end;
4 /
begin
*
ERROR at line 1:
ORA-04031: unable to allocate 4032 bytes of shared memory ("shared
pool","JAVA$POLICY$SHARED$0000000dSYS","joxlod: in ehe","ioc_allocate_pal")
ORA-06512: at "SYS.DBMS_JAVA", line 0
ORA-06512: at line 2
In the error messages doc it is given as
-------------
ORA-04031 unable to allocate string bytes of shared memory
("string","string","string","string")
Cause: More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either use the DBMS_SHARED_
POOL package to pin large packages, reduce your use of shared memory, or
increase the amount of available shared memory by increasing the value of the
initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_
POOL_SIZE. If the large pool is out of memory, increase the initialization
parameter LARGE_POOL_SIZE.
------------------
This statement is confusing.
>If the shared pool is out of memory, either use the DBMS_SHARED_
POOL package to pin large packages, reduce your use of shared memory
Does this mean that the more package I ping in my shared_pool the more will be available memory ?
Thanks Brijesh.
January 22, 2003 - 8:08 am UTC
Your shared pool is pretty small -- and your java pool size is far too small to run anything java.
java shared pool should be 20meg.
SPFile and utl_file_dir parameter
fan, June 09, 2003 - 12:48 pm UTC
>>Bear in mind that in general utl_file_dir is a LIST of >>directories, not a single
>>entity:
>>utl_file_dir = /tmp /tmp/foo /tmp/bar
Tom, I just installed 9.2 on Linux at home
Now in post-install config stage.
I use dbstart/dbshut. (which docs says read the spfile)
i did:
ALTER SYSTEM SET UTL_FILE_DIR='/tmp'
SCOPE=SPFILE;
I want to specify additonal dirs:
Should I do..
ALTER SYSTEM SET UTL_FILE_DIR='/tmp /home/oradev /home/devman /home/oracle'
SCOPE=SPFILE;
This correct ? Is this effective immediately ?
Thanks
June 09, 2003 - 1:05 pm UTC
In 9iR2, use DIRECTORY objects instead of utl_file_dir. much easier, no reboots needed.
create directory TMP_DIR as '/tmp';
declare
l_file utl_file.file_type;
begin
l_file := utl_file.fopen( 'TMP_DIR', 'foo.dat', 'w' );
utl_file.put_raw( l_file, utl_raw.cast_to_raw( 'blah blah blah' ) );
utl_file.fclose( l_file );
end;
/
(uppercase in the 'TMP_DIR' is purposeful and important)....
you can even grant READ or READ WRITE on a directory object -- much better.
DIRECTORY objects...
fan, June 09, 2003 - 1:26 pm UTC
>>In 9iR2, use DIRECTORY objects instead of utl_file_dir. >>much easier, no reboots needed.
That's cool...but our code just lost backward compatibility (to 8i)?
so the ALTER SYSTEM...set UTL_FILE_DIR='/tmp /home/devman...' is good ?
June 09, 2003 - 1:52 pm UTC
ops$tkyte@ORA920> alter system set utl_file_dir='/tmp','/home/tkyte' scope=spfile;
System altered.
and then bounce the database (note 'foo','bar' -- not 'foo,bar')
ops$tkyte@ORA920> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /tmp, /home/tkyte
ops$tkyte@ORA920> set echo on
ops$tkyte@ORA920> @test
ops$tkyte@ORA920> !rm -f /tmp/foo.dat /home/tkyte/foo.dat
ops$tkyte@ORA920>
ops$tkyte@ORA920> !ls -l /tmp/foo.dat /home/tkyte/foo.dat
ls: /tmp/foo.dat: No such file or directory
ls: /home/tkyte/foo.dat: No such file or directory
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 l_file utl_file.file_type;
3 begin
4 l_file := utl_file.fopen( '/tmp', 'foo.dat', 'w' );
5 utl_file.put_line( l_file, 'hello world' );
6 utl_file.fclose( l_file );
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> declare
2 l_file utl_file.file_type;
3 begin
4 l_file := utl_file.fopen( '/home/tkyte', 'foo.dat', 'w' );
5 utl_file.put_line( l_file, 'hello world' );
6 utl_file.fclose( l_file );
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> !ls -l /tmp/foo.dat /home/tkyte/foo.dat
-rw-rw-r-- 1 ora920 ora920 12 Jun 9 13:54 /home/tkyte/foo.dat
-rw-rw-r-- 1 ora920 ora920 12 Jun 9 13:54 /tmp/foo.dat
And the other way?
Steve, June 16, 2003 - 9:04 am UTC
I have to save files to numerous directories. Is there a similar technique available rather than have to declare 55 directories?
Cheers
Steve
June 16, 2003 - 9:28 am UTC
you'll need 55 directory objects to use UTL_FILE on 55 directories.
or
you can write a java stored procedure and grant it write access on a "root" directory and all subdirectories.
My mistake!
steve, June 16, 2003 - 9:42 am UTC
I meant to say read in files from 55 directories, but presumably your answer still applies!
Cheers
Steve
June 16, 2003 - 9:48 am UTC
yup
GRANT READ ON DIRECTORY...
fan, June 16, 2003 - 9:42 am UTC
>> you can even grant READ or READ WRITE on a directory object -- much better.
Tom, I only did :
grant read on directory 'tmp_dir' to HR ;
But HR can write to that 'tmp_dir'...is WRITE a default ?
Thanks
June 16, 2003 - 9:50 am UTC
No, but if HR created it, HR can write it. consider:
ops$tkyte@ORA920> create directory tmp_dir as '/tmp/'
2 /
Directory created.
ops$tkyte@ORA920> grant read on directory tmp_dir to scott;
Grant succeeded.
ops$tkyte@ORA920> select grantee, privilege from
2 dba_tab_privs where owner = 'SYS' and table_name = 'TMP_DIR';
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
OPS$TKYTE READ
OPS$TKYTE WRITE
SCOTT READ
so, check dba_tab_privs and if you believe something is in error, give us a FULL test case to work with.
9iR2 -- Don't seem like there is READ-ONLY Priviledge
Robert, June 19, 2003 - 11:53 am UTC
Hi Tom,
9iR2 --- Don't seem like there is READ-ONLY Priviledge re UTL_FILE
Below is what I did to show that READ priviledge allows
one to WRITE...seems by default.
It also allows deletion.
OTN's example (OBE) also shows similar case...just GRANT READ...
Then that user can write.
-------------------------
select owner, directory_name, trim(directory_path) from all_directories
SYS MEDIA_DIR /opt/oracle/product/9.2.0/demo/schema/product_media/
SYS LOG_FILE_DIR /opt/oracle/product/9.2.0/demo/schema/log/
SYS DATA_FILE_DIR /opt/oracle/product/9.2.0/demo/schema/sales_history/
SYS TMP_DIR /tmp
SYS TMP_TEST /tmp/test
SYS MYDIR /home/rchin
SYS USER_DIR /tmp/user
dbarchin@ORA1> create user goober identified by goober ;
User created.
dbarchin@ORA1> grant connect, resource to goober;
Grant succeeded.
dbarchin@ORA1> connect sys as sysdba
Enter password:
Connected.
dbarchin@ORA1> grant read on directory tmp_test to goober;
Grant succeeded.
dbarchin@ORA1> @connect goober/goober
****** WELCOME TO ORACLE 9i Release 2 ******
goober@ORA1> !ls -aFl /tmp/test
drwxrwxrwx 2 rchin rchin 4096 Jun 15 15:46 ./
drwxrwxrwt 36 root root 4096 Jun 18 23:40 ../
goober@ORA1> ed
Wrote file afiedt.buf
1 DECLARE
2 v_fh UTL_FILE.fILE_TYPE;
3 v_buffer VARCHAR2(4000) := 'Hello Utl_File';
4 BEGIN
5 v_fh := UTL_FILE.FOPEN('TMP_TEST', 'user_goober.txt', 'w');
6 UTL_FILE.PUT_LINE(v_fh, v_buffer);
7 UTL_FILE.FCLOSE(v_fh);
8 EXCEPTION
9 WHEN UTL_FILE.ACCESS_DENIED THEN
10 DBMS_OUTPUT.PUT_LINE('No Access!!!');
11 WHEN others THEN
12 DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
13* END;
goober@ORA1> /
PL/SQL procedure successfully completed.
goober@ORA1> !ls -aFl /tmp/test
total 20
drwxrwxrwx 2 rchin rchin 4096 Jun 19 00:34 ./
drwxrwxrwt 36 root root 4096 Jun 18 23:40 ../
-rw-rw-r-- 1 oracle oinstall 15 Jun 19 00:34 user_goober.txt
goober@ORA1> @connect dbarchin
Enter password:
****** WELCOME TO ORACLE 9i Release 2 ******
dbarchin@ORA1> select grantee, privilege from
dba_tab_privs where owner = 'SYS' and table_name = 'TMP_TEST';
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
HR READ
RCHIN READ
RCHIN WRITE
GOOBER READ
4 rows selected.
June 19, 2003 - 1:19 pm UTC
bummer, I see an in progress bug on that.
Shared drives
Sikandar Hayat, August 07, 2003 - 6:42 am UTC
I am trying to create directories with of shared drives on my client. I am using Oracle 9i R2 on wind000. I have mapped drives e.g.
G:\ or \\server\data\
H:\ or \\server2\data\
but when I access these directores no it results nothing. While the same is working on local drives, C:\ or D:\
I am interested to use network shared drives.
August 09, 2003 - 4:37 pm UTC
too bad you aren't on unix where this works "as one would expect"
remember - Oracle has to run as a "service" on windows. When you log in -- those shared drives exist -- but when you friend down the hall logs in -- what is g: or h:? windows is not a true multi-user OS in the conventional sense. What you see is what YOU see, Oracle cannot see it.
search for
utl_file network drive nt
on this site for a paper on how to set up windows to permit Oracle to see a nt share.
Great
Sikandar Hayat, August 10, 2003 - 3:42 am UTC
Correction to the xmltofile code
MSG, September 29, 2003 - 3:10 pm UTC
Tom:
The existing code was writing blank line to the OS file once the CLOB is more than 30 K, my application DBA and I have corrected the code and would like to submit it for your review.
Thanks
MSG
Modified Code:
procedure xmltofile(
p_xml in clob,
p_path in varchar2,
p_file in varchar2,
p_open_mode in varchar2)
is
v_amt constant pls_integer := 32767;
v_idx pls_integer := 1;
v_file_handle utl_file.file_type;
begin
v_file_handle := utl_file.fopen(p_path, p_file, p_open_mode, v_amt);
for i in 1..ceil(dbms_lob.getlength(p_xml) / v_amt) loop
utl_file.put_raw(
v_file_handle,
utl_raw.cast_to_raw(dbms_lob.substr(p_xml, v_amt, v_idx)));
v_idx := v_idx + v_amt;
end loop;
utl_file.fclose(v_file_handle);
exception
when others then
utl_file.fclose(v_file_handle);
raise;
end xmltofile;
WHY DOESN'T THIS WORK
sonali, October 31, 2003 - 1:04 pm UTC
In oracle 9i I suppose one can use directory to write to csv file... All I want to do is to write to csv file from a table.. here is my code
CONNECT SYSTEM/xxx@xyz;
CREATE DIRECTORY utl_file_logs AS 'e:\dumps\ngitcs';
grant read, write on directory utl_file_logs to ngitcs;
CONNECT NGITCS/NGITCS@xyz;
CREATE OR REPLACE PROCEDURE LOAD_TO_CSV(TNAME VARCHAR2)
IS
v_File UTL_FILE.FILE_TYPE;
BEGIN
v_File := UTL_FILE.FOPEN('utl_file_logs', 'test.txt', 'A', 2000);
UTL_FILE.PUT_LINE(v_File, TNAME);
UTL_FILE.FCLOSE(v_File);
END LOAD_TO_CSV;
/
EXECUTE NGITCS.LOAD_TO_CSV('MWEBORG');
I tried conventional 8i way.. using the procedure example you gave, bothe gave me the same error, what am I doing wrong ?
I have init.ora set up to that path and also the dba_user in the OS group.
create or replace procedure dump_table_to_csv( p_tname in varchar2,
p_dir in varchar2,
p_filename in varchar2 )
authid current_user /* <<<== if you want... runs as "invoker" and runs with ROLES */
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_query varchar2(1000)
default 'select * from ' || p_tname;
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' , 32000);
execute immediate
'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
utl_file.put( l_output,
l_separator || '"' || l_descTbl(i).col_name || '"' );
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_separator := ',';
end loop;
utl_file.new_line( l_output );
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := ',';
end loop;
utl_file.new_line( l_output );
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
exception
when others then
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
raise;
end;
/
execute dump_table_to_csv( 'NGITCS.mweborg', 'c:\oracle\ora92', 'Sonalitest.dat' );
November 01, 2003 - 12:14 pm UTC
why didn't you give us the error code and error message?????
i'll guess. this code:
v_File := UTL_FILE.FOPEN('utl_file_logs', 'test.txt', 'A', 2000);
is failing. it is because the directory name is case sensitive and you created a directory named UTL_FILE_LOGS, not utl_file_logs (identifiers are folded to uppercase unless you "quote" them)
but, if I guessed wrong, hows about you give us the basic info such as "whats the error"
i forgot put the error
sonali, October 31, 2003 - 1:38 pm UTC
With 9i code
SQLWKS> execUTE NGITCS.LOAD_TO_CSV('MWEBORG');
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 424
ORA-06512: at "NGITCS.LOAD_TO_CSV", line 7
ORA-06512: at line 2
with 8i code
SQLWKS> execute dump_table_to_csv( 'NGITCS.MWEBORG', 'C:\ORACLE\admin', 'Sonalitest.txt' );
ORA-29280: invalid directory path
ORA-06512: at "NGITCS.DUMP_TABLE_TO_CSV", line 49
ORA-06512: at line 2
November 01, 2003 - 12:15 pm UTC
in 9i - case issue above
in 8i - since I cannot see your utl_file_dir init.ora parameter, I cannot comment but I would guess you set it "wrong" or not at all.
YOU ARE THE BEST
sonali, November 03, 2003 - 1:40 pm UTC
It worked 9i code, it was the case issue !! I would have never figured that one out.
In 8i I have the correct init.ora file and have restarted the server.. but as this worked in 9i code. I will not bother you anymore.
Thanks again
dbms_java_io permission error
reader, June 10, 2004 - 11:10 am UTC
SQL> select grantee, privilege from
2 dba_tab_privs where owner = 'SYS' and table_name = 'UTL_DIR';
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
DOB READ
DOB WRITE
SQL>
16:37:02> ERROR: ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException:
the Permission (java.io.FilePermission UTL_DIR read) has not been granted to DOB. The PL/SQL to grant
**********************************************************
we are getting the above error message when running an
application. User DOB has read/write permissions to the
UTL_DIR directory that was created.
any hints to solving the problem is most appreciated.
ps: this is Oracle v 9iR2
June 10, 2004 - 5:18 pm UTC
entire example please, not sure what the java is trying to do here.
thanks for your time
reader, June 11, 2004 - 8:49 am UTC
It has been resolved.
June 11, 2004 - 4:14 pm UTC
for the rest of us -- what was the problem and how was it resolved?
java.io.FilePermission error
Richard, December 01, 2004 - 4:16 pm UTC
I run the script on the top of this page about CLOB to file using java. I got this error message:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
C:\BH0000605.XML write) has not been granted to HBS. The PL/SQL to grant this
is dbms_java.grant_permission( 'HBS', 'SYS:java.io.FilePermission',
'C:\BH0000605.XML', 'write' )
ORA-06512: at "HBS.SAVE_CLOB", line 0
ORA-06512: at "HBS.HBS_XML_EXPORT", line 678
ORA-06512: at line 1
Any comment on it?
December 01, 2004 - 7:49 pm UTC
this is like the best error message we've ever produced.
Just read it -- it actually has the exact command you need to run in order for you to have the priv you need.
utl_file_dir='*'
A reader, December 01, 2004 - 5:27 pm UTC
Tom,
What does utl_file_dir='*' mean? Does it mean all database users can read/write to all files in all OS directories?
Thanks as always.
December 01, 2004 - 7:57 pm UTC
yes, so don't use it!
Re: utl_file_dir='*'
A reader, December 03, 2004 - 9:18 am UTC
Tom,
Thanks a lot for your information and advice. I have more questions related to this configuration:
1. Will utl_file_dir='*' configuration cause Oracle to bypass all OS file system privileges? In other words, if the permission of a file in OS (UNIX) is '0700' and owned by root user, will all database users are allowed to read/write it by using PL/SQL packages/procedures if the utl_file_dir is set to '*'?
2. In Oracle9i, Oracle suggests to use 'create directory' instead of use utl_file_dir initial parameter. Could you please list the reasons and some benefits of it?
Thanks as always.
December 03, 2004 - 9:25 am UTC
1) the server process will be running as "oracle" and will be subject to the OS privs of "oracle" whatever they may be. If you could write the file when logged in as "oracle" to the OS, utl_file_dir=* would let you do it (eg: hey, lets open /usr/oracle/oradata/system.dbf -- that would erase your system datafile)
2) you don't need to shutdown the database and restart to add a directory to the list of available directories for reading/writing. utl_file_dir is not dynamic, directories are.
Re: utl_file_dir='*'
A reader, December 03, 2004 - 9:45 am UTC
Very helpful, Tom! A lot of thanks.
do I still limited to a server only?
Eugene, January 15, 2005 - 9:18 pm UTC
Tom,
Even thought I can create directories for utl_file now, am I still limited to a server where Oracle is installed.
Can I use UNC paths? Do I need to map a drive to the other server to be able to utilize .fcopy, .frename etc?
Thanks,
Eugene
January 16, 2005 - 10:22 am UTC
see support note: Note 45172.1
on metalink.oracle.com. It says in part:
b. Universal Naming Convention : UNC is preferable to Mapped Drives because
it does not require anyone to be logged on and UTL_FILE_DIR should be set to
a name in the form :
\\\<machine name>\<share name>\<path>
or
"\\<machine name>\<share name>\<path>"
OK
Kumar, February 09, 2005 - 12:47 pm UTC
Hi Tom,
When I use Utl_file package to read and write to OS Files,
I get error as Invalid file path.
I work in a networked system.How to create a directory from
SQL when we are working under networked systems.I think there lies the problem.How to alleviate this??
Will the directory be created in my local machine or in the
Server?
Please help.
February 09, 2005 - 3:00 pm UTC
guessing you are on "windows"
where "mounts" don't work like they do in unix (much easier in unix - much easier)
utl_file reads and writes to and from the SERVER only (happens in the server - makes sense. if you want to read/write a file on the client, you use client code to do that)
are you on windows?
do you need to access a "share"
see
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:241814624807 <code>
directory read error
Eugene, March 10, 2005 - 1:28 pm UTC
Hi Tom,
I am using Oracle 9.2 on Windows 2003 server. Email server is NetWare.
My prod server is PROD_SRV and email server is EMAIL_SRV (different physical server). I need to read a text file from EMAIL_SRV into PORD_SRV. Here is what I do:
[as sys]
create directory EP_TEST as '\\ktemail02\sys\PUBLIC\BA\Labels\';
grant read, write on directory EP_TEST to ep_user;
[as ep_user]
CREATE OR REPLACE PROCEDURE Ep_Utl_Test AS
2
3 v_exists BOOLEAN;
4 v_file_length NUMBER;
5 v_block_size NUMBER;
6
7 utl_file_handle UTL_FILE.FILE_TYPE;
8 vLine VARCHAR2(32767);
9 vDirectory VARCHAR2(30) := 'EP_TEST';
10 vFile VARCHAR2(30) := 'test.txt';
11
12 BEGIN
13 UTL_FILE.FGETATTR(LOCATION => vDirectory,
14 FILENAME => vFile,
15 FEXISTS => v_exists,
16 FILE_LENGTH => v_file_length,
17 BLOCK_SIZE => v_block_size);
18
19 utl_file_handle := UTL_FILE.FOPEN (vDirectory, vFile, 'R');
20 UTL_FILE.GET_LINE (utl_file_handle,vLine);
21
22
23 IF v_exists THEN
24 DBMS_OUTPUT.PUT_LINE('Exists: TRUE');
25 ELSE
26 DBMS_OUTPUT.PUT_LINE('Exists: FALSE');
27 END IF;
28
29 UTL_FILE.GET_LINE(utl_file_handle, vLine);
30
31 DBMS_OUTPUT.PUT_LINE('File Length:' || v_file_length);
32 DBMS_OUTPUT.PUT_LINE('Block Size :' || v_block_size);
33
34 UTL_FILE.FCLOSE(utl_file_handle);
35
36 END Ep_Utl_Test;
37 /
Procedure created.
UB_OBJECTS@TITAN> exec ep_utl_test;
BEGIN ep_utl_test; END;
*
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 "UB_OBJECTS.EP_UTL_TEST", line 19
ORA-06512: at line 1
Questions:
Can I create a directory on the different machine?
Why am I getting this error, since I have full control on the parent folder?
Thanks,
utl_file exclusively why?
friend, April 22, 2005 - 6:24 pm UTC
Hi ,
Why we need to grant execute on sys.utl_file exclusively and not through some role?
as I tried and faced problem...
Please suggest
utl_file
friend, April 24, 2005 - 2:22 am UTC
I am very sorry as not very good in coding...
create user test identified by test;
grant connect , resource to test;
select value from v$parameter where name = 'utl_file_dir';
VALUE
-----
/oracle/db01/utl
CREATE role cust;
GRANT execute ON sys.utl_file to cust;
grant cust to test;
connect test/test
create or replace procedure utl_file_test_write (
path in varchar2,
filename in varchar2,
firstline in varchar2,
secondline in varchar2)
is
output_file utl_file.file_type;
begin
output_file := utl_file.fopen (path,filename, 'W');
utl_file.put_line (output_file, firstline);
utl_file.put_line (output_file, secondline);
utl_file.fclose(output_file);
--exception
-- when others then null;
end;
/
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE UTL_FILE_TEST_WRITE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/18 PLS-00201: identifier 'SYS.UTL_FILE' must be declared
7/18 PL/SQL: Item ignored
9/5 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
9/5 PL/SQL: Statement ignored
11/5 PL/SQL: Statement ignored
11/24 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
12/5 PL/SQL: Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
12/24 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
13/5 PL/SQL: Statement ignored
13/21 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
SQL> set role none
2 ;
Role set.
SQL> create or replace procedure utl_file_test_write (
2 path in varchar2,
3 filename in varchar2,
4 firstline in varchar2,
5 secondline in varchar2)
6 is
7 output_file utl_file.file_type;
8 begin
9 output_file := utl_file.fopen (path,filename, 'W');
10
11 utl_file.put_line (output_file, firstline);
12 utl_file.put_line (output_file, secondline);
13 utl_file.fclose(output_file);
14
15 --exception
16 -- when others then null;
17 end;
18 /
Warning: Procedure created with compilation errors.
same error ....
Please suggest
April 24, 2005 - 5:26 am UTC
plesae check that link out again.
it is telling you that you NEED to have DIRECT access (not via a role) to utl_file.
Normally, UTL_FILE is granted to public. Your DBA must have revoked it. You need to ask them to grant it to you.
'UTL_FILE' must be declared
Dawar, July 05, 2005 - 3:53 pm UTC
Tom,
DB Version: 10.1.0.3.0
I am getting following compiling errors.
I have feeling other we have deleted utl_file object from the system.
DMSYS PACKAGE BODY DBMS_DATA_MINING_INTERNAL
Line # = 2924 Column # = 17 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 2924 Column # = 17 Error Text = PL/SQL: Item ignored
Line # = 2931 Column # = 5 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 2931 Column # = 5 Error Text = PL/SQL: Statement ignored
Line # = 2951 Column # = 7 Error Text = PL/SQL: Statement ignored
Line # = 2952 Column # = 9 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 2955 Column # = 23 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 2955 Column # = 7 Error Text = PL/SQL: Statement ignored
Line # = 2962 Column # = 21 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 2962 Column # = 5 Error Text = PL/SQL: Statement ignored
DMSYS PACKAGE BODY DM_SEC_SYS
Line # = 1310 Column # = 6 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 1310 Column # = 6 Error Text = PL/SQL: Item ignored
Line # = 3265 Column # = 16 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 3265 Column # = 16 Error Text = PL/SQL: Item ignored
Line # = 3267 Column # = 3 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 3267 Column # = 3 Error Text = PL/SQL: Statement ignored
Line # = 3268 Column # = 22 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 3268 Column # = 3 Error Text = PL/SQL: Statement ignored
Line # = 3269 Column # = 19 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 3269 Column # = 3 Error Text = PL/SQL: Statement ignored
DMSYS PACKAGE BODY DBMS_DM_UTIL
Line # = 5 Column # = 19 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 5 Column # = 19 Error Text = PL/SQL: Item ignored
OLAPSYS PACKAGE BODY DBMS_AW_UTILITIES
Line # = 63 Column # = 23 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 63 Column # = 23 Error Text = PL/SQL: Item ignored
Line # = 64 Column # = 22 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 64 Column # = 22 Error Text = PL/SQL: Item ignored
Line # = 65 Column # = 22 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 65 Column # = 22 Error Text = PL/SQL: Item ignored
Line # = 160 Column # = 41 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 160 Column # = 13 Error Text = PL/SQL: Statement ignored
Line # = 183 Column # = 47 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 183 Column # = 5 Error Text = PL/SQL: Item ignored
Line # = 211 Column # = 45 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 211 Column # = 5 Error Text = PL/SQL: Item ignored
Line # = 325 Column # = 16 Error Text = PLS-00905: object OLAPSYS.CWM2_OLAP_AW_AWUTIL is invalid
Line # = 325 Column # = 9 Error Text = PL/SQL: Statement ignored
Line # = 337 Column # = 17 Error Text = PLS-00905: object OLAPSYS.CWM2_OLAP_AW_AWUTIL is invalid
Line # = 337 Column # = 17 Error Text = PL/SQL: Statement ignored
Line # = 338 Column # = 17 Error Text = PLS-00905: object OLAPSYS.CWM2_OLAP_AW_AWUTIL is invalid
Line # = 338 Column # = 17 Error Text = PL/SQL: Statement ignored
Line # = 413 Column # = 12 Error Text = PLS-00905: object OLAPSYS.CWM2_OLAP_AW_AWUTIL is invalid
Line # = 413 Column # = 9 Error Text = PL/SQL: Statement ignored
OLAPSYS PACKAGE BODY CWM2_OLAP_OLAPAPI_ENABLE
Line # = 15 Column # = 23 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 15 Column # = 23 Error Text = PL/SQL: Item ignored
Line # = 28 Column # = 43 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 28 Column # = 3 Error Text = PL/SQL: Item ignored
Line # = 56 Column # = 44 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 56 Column # = 3 Error Text = PL/SQL: Item ignored
Line # = 64 Column # = 52 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 64 Column # = 3 Error Text = PL/SQL: Item ignored
Line # = 77 Column # = 24 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 77 Column # = 5 Error Text = PL/SQL: Statement ignored
Line # = 78 Column # = 24 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 78 Column # = 5 Error Text = PL/SQL: Statement ignored
Line # = 79 Column # = 24 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 79 Column # = 5 Error Text = PL/SQL: Statement ignored
Line # = 80 Column # = 24 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 80 Column # = 5 Error Text = PL/SQL: Statement ignored
Line # = 81 Column # = 24 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 81 Column # = 5 Error Text = PL/SQL: Statement ignored
Line # = 82 Column # = 24 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 82 Column # = 5 Error Text = PL/SQL: Statement ignored
XDB PACKAGE BODY DBMS_XMLPARSER
Line # = 677 Column # = 12 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 677 Column # = 12 Error Text = PL/SQL: Item ignored
Line # = 685 Column # = 4 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 685 Column # = 4 Error Text = PL/SQL: Statement ignored
Line # = 686 Column # = 18 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 686 Column # = 4 Error Text = PL/SQL: Statement ignored
Line # = 687 Column # = 20 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 687 Column # = 4 Error Text = PL/SQL: Statement ignored
FLOWS_010500 PROCEDURE WWV_FLOW_GEN_TRANS
Line # = 24 Column # = 29 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 24 Column # = 29 Error Text = PL/SQL: Item ignored
FLOWS_010500 PACKAGE BODY WWV_FLOW_BACKUP
Line # = 112 Column # = 14 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 112 Column # = 14 Error Text = PL/SQL: Item ignored
Line # = 162 Column # = 5 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 162 Column # = 5 Error Text = PL/SQL: Statement ignored
Line # = 174 Column # = 24 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 174 Column # = 11 Error Text = PL/SQL: Statement ignored
Line # = 177 Column # = 29 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 177 Column # = 11 Error Text = PL/SQL: Statement ignored
Line # = 183 Column # = 21 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 183 Column # = 5 Error Text = PL/SQL: Statement ignored
FLOWS_010500 PACKAGE BODY WWV_FLOW_DEBUG
Line # = 280 Column # = 12 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 280 Column # = 12 Error Text = PL/SQL: Item ignored
Line # = 283 Column # = 5 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 283 Column # = 5 Error Text = PL/SQL: Statement ignored
FLOWS_010500 PACKAGE BODY WWV_FLOW_GEN_API2
Line # = 5 Column # = 31 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 5 Column # = 31 Error Text = PL/SQL: Item ignored
FLOWS_010500 PACKAGE BODY WWV_FLOW_GEN_GLOBAL_API
Line # = 4 Column # = 29 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 4 Column # = 29 Error Text = PL/SQL: Item ignored
FLOWS_010500 PACKAGE BODY WWV_FLOW_GEN_HINT
Line # = 5 Column # = 27 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 5 Column # = 27 Error Text = PL/SQL: Item ignored
FLOWS_010500 PACKAGE BODY WWV_FLOW_LOAD_DATA
Line # = 681 Column # = 25 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 681 Column # = 25 Error Text = PL/SQL: Item ignored
FLOWS_010500 PACKAGE BODY WWV_FLOW_SCRIPT_EXPORT
Line # = 5 Column # = 29 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 5 Column # = 29 Error Text = PL/SQL: Item ignored
Do I need to run any built in script to built utl_file object again?
If yes, which one.
Regards,
Dawar
July 05, 2005 - 4:54 pm UTC
it would/could be that your DBA has restricted access to UTL_FILE in which case it would just a be a grant.
Are you working with your DBA? They should know how to
a) determine if it is a privilege issue
b) fix a privilege issue
c) reinstall utl_file if not a/b and they did drop it.
Dawar, July 05, 2005 - 5:05 pm UTC
Tom,
DBA is not available.
Generally I take care of his reposibilities in his absence.
a) determine if it is a privilege issue
How to detremine?
b) fix a privilege issue
Probably I need to grant .... on all_objects.
c) reinstall utl_file if not a/b and they did drop it.
How to reinstall utlfile objects?
Do I need to run any script.
I do have some utl objcets.
How should I know which one we are missing?
Regards,
Dawar
July 05, 2005 - 5:22 pm UTC
I'm not going there, sorry -- you want your DBA to work with you.
You shouldn't have DBA privs (and you must if you are installing stuff), that is "pretty dangerous", sort of like giving me root, I know just enough to be thoroughly dangerous.
Please, wait for the dba to come back, let them do the install too.
GRANT EXECUTE ON SYS.UTL_FILE TO PUBLIC, worked
Dawar, July 05, 2005 - 8:51 pm UTC
Tom,
It has worked.
You gave me very good hint. thanks
It was a privilege issue
1. CREATE PUBLIC SYNONYM UTL_FILE FOR SYS.UTL_FILE -- It was lready exitsed so I done step 2 & step 3 below.
2. GRANT EXECUTE ON SYS.UTL_FILE TO PUBLIC
3. ALTER PACKAGE .... COMPILE BODY
Regards,
Dawar
July 06, 2005 - 7:36 am UTC
arg... I feel so defeated.
someone purposely took away the grant on utl_file, perhaps for some perfectly sane reason.
and you've put it back, at the system level, for all to use, instead of waiting for the person that knows.
scary.
trouble with big table
Antonio Godoi, July 18, 2005 - 6:51 pm UTC
Hi,
i´ve tested the procedure xmltofile, and it works fine with small tables, BUT in big tables it does a eternal loop (except when you stop it :), and create a giant file, full of blank spaces (in +/- 3k lines).
I've found in this link, a procedure that do the same thing (with little adjustments) and runs FASTER, but not little, MUTCH FASTER, and without bugs...
</code>
http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_41.shtml#Write_CLOB_To_XML_File <code>
[]´s
Antonio Godoi
A reader, August 26, 2005 - 2:04 pm UTC
Peter Shawk, October 16, 2012 - 10:51 am UTC
Hi tom,
Hope you do answer to this real quick :) as always ...
We have a four node Oracle RAC infrastructure 11g with Linux. We are trying to get the UTL_FILE_DIR configured on this one.
1) Incase we pass the permissions as 777 to the dir's listed in UTL_FILE_DIR it sails through and we don't come across any issues,but if set them to 770 or 755 it just error's out with the usual no such file or directory.
2) We don't want to use the permissions as above for security reasons.
Can you please let us know the way forward??
Thanks in Advance,
Peter
October 16, 2012 - 11:08 am UTC
DO NOT USE UTL_FILE_DIR UNDER ANY CIRCUMSTANCES.
Please use create or replace directory.
utl_file_dir is deprecated and unsafe (no control over privileges!)
how do you connect to this database - over the network or locally without sqlnet?