Oracle provides the package UTL_FILE to produce OS layer files from within the Oracle RDBMS. This article will describe the common error of ORA-29280 which maybe received when using the UTL_FILE procedure and how to resolve it.
1. Logon to your Oracle database server as the Oracle software owner.
2. Create a file called create_file.sql with the following SQL.
create or replace procedure test_file
is
v_record varchar2(50) := ‘Testing file creation’;
v_file varchar2(30) := ‘test_file.txt’;
v_dir varchar2(512) := ‘/home/lcatt’;
v_write utl_file.file_type;
begin
v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
utl_file.put_line(v_write, v_record);
utl_file.fclose(v_write);
end test_file;
/
3. Enter SQL*PLUS, set ECHO and FEEDBACK to on, and execute the file
create_file.sql as a user with DBA privileges. In this example we are using the account LJCATT.
Mylinux:/home/lcatt:>sqlplus ljcatt
SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 10 10:43:17 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set feedback on
SQL> set echo on
SQL>@./create_file.sql
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7 v_write utl_file.file_type;
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
16 end test_file;
17 /
Procedure created.
SQL>
4. Use the SQL*PLUS command show to display the initialization parameter utl_file_dir and ensure that the directory /home/lcatt is not present.
SQL> show parameter utl_file_dir
NAME TYPE VALUE
———————————— ———– ——————————
utl_file_dir string /home/lcatt/output
SQL>
5. If the directory is present, remove it with the following command:
SQL> alter system set utl_file_dir=” scope=spfile;
System altered.
SQL>
6. Logon to the database as sysdba and restart your oracle instance.
SQL> connect / as sysdba
Connected.
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 2068728 bytes
Variable Size 654315272 bytes
Database Buffers 503316480 bytes
Redo Buffers 48259072 bytes
Database mounted.
Database opened.
SQL>
7. Now logon as the user LJCATT and execute the package test_file with the command execute.
SQL> execute test_file
BEGIN test_file; END;
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at “SYS.UTL_FILE”, line 29
ORA-06512: at “SYS.UTL_FILE”, line 448
ORA-06512: at “LJCATT.TEST_FILE”, line 10
ORA-06512: at line 1
SQL>
8. NOTE: The error produced is a result of no reference to the defined directory structure in the initialization parameter UTL_FILE_DIR. This parameter is used to allow the database access to OS layer directories. To resolve this error, connect to the database as sysdba and place the directory path in the initialization parameter UTL_FILE_DIR with the command alter system and restart the database.
SQL> connect / as sysdba
Connected.
SQL> alter system set utl_file_dir=’/home/lcatt’ scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 2068728 bytes
Variable Size 654315272 bytes
Database Buffers 503316480 bytes
Redo Buffers 48259072 bytes
Database mounted.
Database opened.
SQL> show parameter utl_file_dir
NAME TYPE VALUE
———————————— ———– ——————————
utl_file_dir string /home/lcatt
9. Connect back to the database as the user LJCATT and re-execute the procedure test_file.
SQL> connect ljcatt
Enter password:
Connected.
SQL> execute test_file
PL/SQL procedure successfully completed.
SQL>
10. Exit out of SQL*PLUS, change directories to the location of your output file, and perform an ls command to display the file generated.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Mylinux:/home/lcatt:>cd /home/lcatt
Mylinux:/home/lcatt:>ls testfile
testfile
Mylinux:/home/lcatt:>
The file was produced under the directory defined by the initialization parameter utl_file_dir and the error ORA-29280 has been resolved.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com