Oracle File Write

Hello Readers,

There are a couple of ways to write file directly from Oracle:

1) Spooling

2) UTL_FILE Package

Let’s see an example of each. Consider a CITY_STATE_TEMP table that has 2 cols: CITY and STATE. Some sample records are inserted and the task is to write this data into a flat file from Oracle. The script for this sample table can be downloaded from here.

1) Spooling: Writes data into a flat file by executing a sql script. In the sql script, you would typically start spooling by saying spool <output_file_path>, write your excellent query and stop spool by saying spool off. Some additional configuration are specified at top for proper formatting. Below is the complete script:

SET serveroutput ON
SET heading OFF
SET feedback OFF
SET linesize 32767
SET pagesize 0
SET trimspool ON
SET term OFF
SET verify OFF
SET arraysize 200
spool "C:\temp_badal\spool_city_state.txt"
SELECT city||', '|| state FROM city_state_temp;
spool OFF
EXIT

Let’s assume it is saved at “C:\temp_badal\spool_file_write.sql”. The sql script is ready to be executed:

@"C:\temp_badal\spool_file_write.sql"

After execution, as mentioned in the script, the output file will be written at “C:\temp_badal\spool_city_state.txt”. It can be downloaded from here.

2) UTL_FILE Package: Comes with Oracle and has utility objects to read and write files. You would need an Oracle directory created. To find existing directories, query ALL_DIRECTORIES table. It should return OWNER, DIRECTORY_NAME and DIRECTORY_PATH. If there are none, then you may have to request your DBA to create one. Below procedure uses this package to write data into a txt file.

CREATE OR REPLACE
PROCEDURE utl_write_file(
 directory_name IN VARCHAR2,
 file_name IN VARCHAR2)
IS
 CURSOR c
 IS
 SELECT city, state FROM city_state_temp;
 c1 c%rowtype;
 f utl_file.file_type;
 line VARCHAR2(50);
BEGIN
 f := utl_file.fopen(directory_name,file_name,'W');
 OPEN c;
 LOOP
 FETCH c INTO c1;
 line := c1.city||', '||c1.state;
 utl_file.put_line(f, line);
 EXIT
 WHEN c%notfound;
 END LOOP;
 CLOSE c;
 utl_file.fclose(f);
END;

Run the proc:

exec utl_write_file('<directory_name>','<output_file_name>');

It will write the file at <directory_path/output_file_name>. Click here to download. For more details about UTL_FILE package, visit this website.

So far so good. But What is the difference? Which method should I use? Will Chris ever marry Kate?

The difference is Spool is a client-side program whereas UTL_FILE is a server-side program. Thus if you want to write files on client, spooling would be a better pick. If the files should be available on server, then utl_file would be a better pick. And I have no idea who Chris and Kate are…<my_evil_laugh>

Thanks for reading this post.

Please do leave your comments/feedback…

Your’s Truly

Advertisements

About Badal Chowdhary

I am a Software Engineer by profession. I have done SCJP and SCWCD certifications. Like working on cutting edge technologies and frameworks. Driven by challenges and fascinated by technology. I love playing and watching sports: Cricket, Ping Pong, Tennis, Badminton, Racket Ball and Gym.
This entry was posted in Oracle and tagged , , , , , , , . Bookmark the permalink.

2 Responses to Oracle File Write

  1. Thanks, have been searching all day for something that does exactly this…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s