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.