Oracle provides the package UTL_FILE to produce OS layer files from within the Oracle RDBMS. This article will show a basic procedure for the producing an OS layer file from within the Oracle RDBMS.
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.
Mylinux:/home/lcatt:>sqlplus ljcatt
SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 03 09: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. Ensure that the system initialization parameter for utl_file_dir contains the directory to which utl_file is going to write with the command show utl_file_dir.
SQL> show parameter utl_file_dir
NAME TYPE VALUE
———————————— ———– ——————————
utl_file_dir string
SQL>
5. If it does not contain the directory, as is the case in step 4, logon to your Oracle database as sysdba and add the directory structure and bounce the database, as seen below:
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
SQL>
6. Execute the newly create procedure with the SQL*PLUS command; execute test_file
SQL> execute test_file
PL/SQL procedure successfully completed.
SQL>
7. Exit out of SQL*PLUS and change directory to where you wrote your file.
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:>
8. Execute the ls and more command on the file testfile.
Mylinux:/home/lcatt:>ls testfile
testfile
Mylinux:/home/lcatt:>more testfile
Testing file creation
Mylinux:/home/lcatt:>
This completes the creation of external OS layer file from within the Oracle RDBMS.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com