The UTL_FILE
package can be used to perform read/write operations on text files. In Oracle 9i it's functionality has been extended to include binary reads/write operations. In this article I will demonstrate how to use the UTL_FILE
package to perform a simple data extract to a CSV file.
In Oracle8i the UTL_FILE.FOPEN
procedure accepted an actual path for the location parameter. Access to the directory was restricted by the UTL_FILE_DIR parameter in the init.ora file. This gave a reduced level of control and required the server to be bounced before changes could take affect. This situation has been improved in Oracle 9i where the path has been replaced by a directory object which
points to the path. This makes alteration of the path possible without alterations to the code. In addition access control is granted via privileges which gives a greater degree of control.
In later releases SQL*Plus and SQLcl allow you to generate CSV output directly, so you may not need the solutions presented here. You can read more about these solutions here.
If you are using SQL*Plus, a simple solution is to avoid PL/SQL altogether and concatenate all the column values together with dividing commas. The output from this type of query can be spooled out to a file.
SET LINESIZE 1000 TRIMSPOOL ON PAGESIZE 0 FEEDBACK OFF SPOOL c:\oracle\extract\emp.csv SELECT empno || ',' || ename || ',' || job || ',' || mgr || ',' || TO_CHAR(hiredate,'DD-MON-YYYY') AS hiredate || ',' || sal || ',' || comm || ',' || deptno FROM emp ORDER BY ename; SPOOL OFF SET PAGESIZE 14
Define a directory object which points to an existing filesystem directory on the server. We must grant the necessary access privilege on the directory object to the user who will perform the extract.
CONNECT / AS SYSDBA CREATE OR REPLACE DIRECTORY EXTRACT_DIR AS 'c:\oracle\extract'; GRANT READ, WRITE ON DIRECTORY EXTRACT_DIR TO SCOTT; GRANT EXECUTE ON UTL_FILE TO SCOTT;
Next we create the extract procedure.
CONNECT scott/tiger CREATE OR REPLACE PROCEDURE EMP_CSV AS CURSOR c_data IS SELECT empno, ename, job, mgr, TO_CHAR(hiredate,'DD-MON-YYYY') AS hiredate, sal, comm, deptno FROM emp ORDER BY ename; v_file UTL_FILE.FILE_TYPE; BEGIN v_file := UTL_FILE.FOPEN(location => 'EXTRACT_DIR', filename => 'emp_csv.txt', open_mode => 'w', max_linesize => 32767); FOR cur_rec IN c_data LOOP UTL_FILE.PUT_LINE(v_file, cur_rec.empno || ',' || cur_rec.ename || ',' || cur_rec.job || ',' || cur_rec.mgr || ',' || cur_rec.hiredate || ',' || cur_rec.empno || ',' || cur_rec.sal || ',' || cur_rec.comm || ',' || cur_rec.deptno); END LOOP; UTL_FILE.FCLOSE(v_file); EXCEPTION WHEN OTHERS THEN UTL_FILE.FCLOSE(v_file); RAISE; END; /
We are now able to perform the extract as follows.
EXEC EMP_CSV;
In the event of a problem the current exception handler gives no useful information. In order to report something more useful we could alter the exception handler as follows.
EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20000, 'File location is invalid.'); WHEN UTL_FILE.INVALID_MODE THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20001, 'The open_mode parameter in FOPEN is invalid.'); WHEN UTL_FILE.INVALID_FILEHANDLE THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20002, 'File handle is invalid.'); WHEN UTL_FILE.INVALID_OPERATION THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20003, 'File could not be opened or operated on as requested.'); WHEN UTL_FILE.READ_ERROR THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20004, 'Operating system error occurred during the read operation.'); WHEN UTL_FILE.WRITE_ERROR THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20005, 'Operating system error occurred during the write operation.'); WHEN UTL_FILE.INTERNAL_ERROR THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20006, 'Unspecified PL/SQL error.'); WHEN UTL_FILE.CHARSETMISMATCH THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20007, 'A file is opened using FOPEN_NCHAR, but later I/O ' || 'operations use nonchar functions such as PUTF or GET_LINE.'); WHEN UTL_FILE.FILE_OPEN THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20008, 'The requested operation failed because the file is open.'); WHEN UTL_FILE.INVALID_MAXLINESIZE THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20009, 'The MAX_LINESIZE value for FOPEN() is invalid; it should ' || 'be within the range 1 to 32767.'); WHEN UTL_FILE.INVALID_FILENAME THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20010, 'The filename parameter is invalid.'); WHEN UTL_FILE.ACCESS_DENIED THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20011, 'Permission to access to the file location is denied.'); WHEN UTL_FILE.INVALID_OFFSET THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20012, 'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; ' || 'it should be greater than 0 and less than the total ' || 'number of bytes in the file.'); WHEN UTL_FILE.DELETE_FAILED THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20013, 'The requested file delete operation failed.'); WHEN UTL_FILE.RENAME_FAILED THEN UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20014, 'The requested file rename operation failed.'); WHEN OTHERS THEN UTL_FILE.FCLOSE(v_file); RAISE; END;
For a simple generic solution, load the csv.sql package into the SCOTT schema.
CONNECT scott/tiger @csv.sql
Using the directory object created previously, execute the following commands.
-- Query. EXEC csv.generate('EXTRACT_DIR', 'emp.csv', p_query => 'SELECT * FROM emp'); -- REF CURSOR. DECLARE l_refcursor SYS_REFCURSOR; BEGIN OPEN l_refcursor FOR SELECT * FROM emp; csv.generate_rc('DBA_DIR','generate.csv', l_refcursor); END; /
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/9i/generating-csv-files