Skip to Main Content
  • Questions
  • utl_file_dir instance parameter and UTL_FILE - usage information

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Luigi.

Asked: June 05, 2002 - 2:10 pm UTC

Last updated: October 16, 2012 - 11:08 am UTC

Version: 9.0.1

Viewed 10K+ times! This question is

You Asked

Hi, Tom, nice to see you online again!
I'm managing a project which has several tasks. One of them is to produce XML files from SQL queries (in PL/SQL stored procedures) and store them on the server (by the way, it's a Linux Suse 7.3 - installing Oracle Server 9i wasn't so simple...).
I tried using DBMS_XMLQUERY package, and wrote the following (in SCOTT schema, for testing purpose only), which creates a XML clob from a query:

Function GET_XML_FROM_QUERY(sql_statement varchar2)
RETURN clob IS
l_ctx dbms_xmlquery.ctxType;
l_xml clob;
BEGIN
dbms_lob.createtemporary(l_xml,true,dbms_lob.session);
l_ctx := dbms_xmlquery.newContext(sql_statement);
dbms_xmlquery.setRaiseNoRowsException(l_ctx, false);
l_xml := dbms_xmlquery.getXML(l_ctx);
dbms_xmlquery.closeContext(l_ctx);
RETURN l_xml;
EXCEPTION
WHEN others THEN
dbms_xmlquery.closeContext(l_ctx);
raise;
END;

This works fine. Then I tried to store the clob returned into a physical file in the server. I wrote the following:

procedure xmltofile(p_xml in clob, my_path varchar2, my_file varchar2, my_open_mode varchar2) is
l_xml varchar2(30000);
l_line varchar2(2000);
l_amt pls_integer := 30000;
l_idx pls_integer := 1;


my_file_handle utl_file.file_type;
BEGIN
my_file_handle := utl_file.fopen_nchar(my_path, my_file, my_open_mode);
for i in 1 .. ceil(dbms_lob.getlength(p_xml)/l_amt) loop
l_xml := dbms_lob.substr(p_xml, l_amt, l_idx);
loop
exit when l_xml is null;
l_line := substr(l_xml, 1, instr(l_xml, chr(10))-1);
utl_file.put_line_nchar(my_file_handle, l_line);
l_xml := substr(l_xml, instr(l_xml, chr(10))+1);
end loop;
end loop;
utl_file.fclose_all;
exception
when others then utl_file.fclose_all;
raise;
end xmltofile;

Some of the code above was taken from an answer you gave in the past to someone else.
Then, I set up the utl_file_dir instance parameter with the value '/u01/utldir', which is a folder I created in my Linux server (CHMOD 777 to avoid any problem).
I tried to execute the second procedure as follows:

BEGIN
xmltofile(get_xml_from_query('select * from dept'), '/u01/utldir', 'test.xml', 'w');

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
RAISE;
END;

It worked. The file test.xml was generated correctly.
So, what's the problem? Simple to tell, maybe harder to solve.
What about if I would generate a file not just in the /u01/utldir folder, but also in the /u01/utldir/userdir folder?
In other words, I'd like to generate the output files placing them in different dirs for different users. Is there any way to do that?
I just tried statements like:

xmltofile(get_xml_from_query('select * from dept'), '/u01/utldir/userdir', 'test.xml', 'w');

or

xmltofile(get_xml_from_query('select * from dept'), '/u01/utldir', './userdir/test.xml', 'w');

but none worked.

Thank you very much.

Luigi


and we said...

utl_file_dir works directory by directory and never never includes subdirectories.

You can use a java stored procedure to do this easily -- I asked Mark.Piermarini@oracle.com to help me out quickly. You can do this:

ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
2 dbms_java.grant_permission
3 ( USER,
4 'java.io.FilePermission',
5 '/tmp/-',
6 'write' );
7 end;
8 /
PL/SQL procedure successfully completed.


(the "-" allows for recusive permissions)


ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace
2 and compile
3 java source named "ClobUtil"
4 as
5
6 import java.io.*;
7 import java.lang.*;
8 import java.sql.*;
9 import java.util.*;
10 import oracle.jdbc.driver.*;
11 import oracle.sql.*;
12
13 public class ClobUtil {
14 public static void save(CLOB clob, String filename) throws Exception
15 {
16 Connection conn = DriverManager.getConnection("jdbc:oracle:kprb:");
17 conn.setAutoCommit(false);
18
19 InputStream is = clob.getAsciiStream();
20 FileOutputStream os = new FileOutputStream(filename);
21
22 int size = clob.getChunkSize();
23 byte buffer[] = new byte[size];
24 int length;
25
26 while ((length = is.read(buffer, 0, size)) != -1) {
27 os.write(buffer, 0, length);
28 }
29
30 is.close();
31 os.close();
32 conn.close();
33 }
34 }
35 /

Java created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> show errors
No errors.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure save_clob(
2 p_clob in CLOB,
3 p_filename in VARCHAR2)
4 as language java name 'ClobUtil.save(
5 oracle.sql.CLOB,
6 java.lang.String) ';
7 /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_clob clob;
3 begin
4 dbms_lob.createtemporary( l_clob, true );
5 dbms_lob.open( l_clob, dbms_lob.lob_readwrite );
6 for i in 1 .. 8333
7 loop
8 dbms_lob.writeAppend( l_clob, length('hello world '), 'hello world ' );
9 end loop;
10
11 save_clob( l_clob, '/tmp/foo.dat' );
12 save_clob( l_clob, '/tmp/tkyte/foo.dat' );
13 end;
14 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> !ls -l /tmp/foo.dat /tmp/tkyte/foo.dat
-rw-r--r-- 1 ora817 99996 Jun 6 13:09 /tmp/foo.dat
-rw-r--r-- 1 ora817 99996 Jun 6 13:09 /tmp/tkyte/foo.dat

ops$tkyte@ORA817DEV.US.ORACLE.COM>



Rating

  (34 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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


Tom Kyte
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 !


Tom Kyte
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

Tom Kyte
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)



Tom Kyte
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.

Tom Kyte
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






Tom Kyte
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 ?


Tom Kyte
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

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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.

Tom Kyte
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.

Tom Kyte
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

Thanks TOM I was not giving login/pass in the service. After reading
</code> http://asktom.oracle.com/pls/ask/f?p=4950:8:2197672568838825509 <code>

Now I am able to get my results.

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' );


Tom Kyte
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



Tom Kyte
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

 

Tom Kyte
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.

Tom Kyte
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?

Tom Kyte
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.

Tom Kyte
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.

Tom Kyte
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

Tom Kyte
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.


Tom Kyte
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

Tom Kyte
April 22, 2005 - 7:10 pm UTC

</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

you dont NEED it, unless you want to create a compiled stored object that references it.

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 

Tom Kyte
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


Tom Kyte
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




Tom Kyte
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




Tom Kyte
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
Tom Kyte
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?

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here