There are a couple of ways to write file directly from Oracle:
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:
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:
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…