Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

UTL_FILE alter system

732311Feb 24 2011 — edited Feb 24 2011
Hi expert,

I'm trying to use UTL_FILE.

In first time I run only these command:

SQL> CREATE DIRECTORY MIGR AS '/tmp/MIGR/';
SQL> GRANT READ, WRITE ON DIRECTORY MIGR TO DBA;

but for to use utl_file I'm obliged to run "alter system set utl_file_dir=...."

is it true? Can I not set the utl_file_dir, for to use UTL_FILE?

Cheers,
Lain

oracle@mauri-laptop:/tmp/MIGR$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 24 16:31:25 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE DIRECTORY MIGR AS '/tmp/MIGR/'; 
SQL> GRANT READ, WRITE ON DIRECTORY MIGR TO DBA; 
SQL> host cat /tmp/MIGR/Report.txt
 test
SQL> DECLARE 
  V1 VARCHAR2(32767); 
  F1 UTL_FILE.FILE_TYPE; 
BEGIN 
  F1 := UTL_FILE.FOPEN('MIGR','Report.txt','R',256); 
  UTL_FILE.GET_LINE(F1,V1,32767); 
  UTL_FILE.FCLOSE(F1); 
END;
  2    3    4    5    6    7    8    9  
 10  /
DECLARE
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 5


SQL> select * from all_directory;
select * from all_directory
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from all_directories;

OWNER			       DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS			       MIGR
/tmp/MIGR

SYS			       SUBDIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep

SYS			       SS_OE_XMLDIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/


OWNER			       DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS			       LOG_FILE_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/

SYS			       DATA_FILE_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/

SYS			       XMLDIR
/ade/b/2125410156/oracle/rdbms/xml


OWNER			       DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS			       MEDIA_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/

SYS			       DATA_PUMP_DIR
/u01/app/oracle/admin/localdb/dpdump/

SYS			       ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/ccr/state


9 rows selected.

SQL> 
SQL> 
SQL> show parameter utl_file;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir			     string	 

SQL>
SQL> alter system set utl_file_dir='/tmp/MIGR','/u01/app/oracle/product/11.2.0/dbhome_1/MIGR/' scope=spfile
  2  ;

System altered.

SQL> 
SQL> 
SQL>       
SQL> shutdown

Database closed.
Database dismounted.

ORACLE instance shut down.
SQL> SQL> SQL> 
SQL> 
SQL> 
SQL> 
SQL> startup
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size		    2217832 bytes
Variable Size		 1795164312 bytes
Database Buffers	 1476395008 bytes
Redo Buffers		   16568320 bytes
Database mounted.
Database opened.
SQL> 
SQL> show parameter utl_file

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir			     string	 /tmp/MIGR, /u01/app/oracle/pro
						 duct/11.2.0/dbhome_1/MIGR/

SQL>
SQL> SET SERVEROUT ON

declare
v_buff VARCHAR2(2000);
fhandle UTL_FILE.FILE_TYPE;
begin
dbms_output.put_line('WRITE');
fhandle:= UTL_FILE.FOPEN('MIGR','Report.txt','W');
UTL_FILE.put_line(fhandle,' Attempt to write');
UTL_FILE.FCLOSE(fhandle);
end;
/SQL> SQL>   2    3    4    5    6    7    8    9   10  
WRITE

PL/SQL procedure successfully completed.
This post has been answered by sb92075 on Feb 24 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 24 2011
Added on Feb 24 2011
2 comments
17,110 views