• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3352
  • Last Modified:

Oracle PL-SQL: How can I write to a file?

Hi Everyone,

I'm learning PL-SQL.  I want to know how to extract data from a table(s) and write it to a file.  

Do I use DBMS_OUTPUT or UTL_FILE? What is the difference here?

Can anyone give me a simple example to work with (eg, emp table with emp_id and emp_name columns)?

Thanks for your time,
ITOrBust

0
itorbust
Asked:
itorbust
  • 2
3 Solutions
 
seazodiacCommented:
Use UTL_FILE to write a file.
use dbms_output to write to the CONSOLE.

here is the example for utl_file.
but you have to set UTL_FILE_DEST parameter in the init.ora file.

declare
emp_rec emp%ROWTYPE;
file_id UTL_FILE.FILE_TYPE;
BEGIN
  file_id := utl_file.FOPEN( '/temp', 'test.txt', 'w' );
  FOR emp IN (SELECT employee_name FROM employee)
  LOOP
     utl_file.PUT_LINE( file_id, emp.employee_name );
  END LOOP;
  utl_file.fCLOSE(file_id);
END;
/
0
 
pratikroyCommented:
As seazodiac mentioned that dbms_output is used to write to the console, and UTL_FILE is used to write to a file. And you can see in seazodiac's example about how you can use the UTL_FILE.

But you need to keep in mind that a parameter utl_file_dir has to be set in the init.ora. This will contain the name of the directory when you can read or write the file. In seazodiac's example, this directory would be "/temp"

If you wanted to do the same thing using DBMS_OUTPUT, you could have done this :

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
emp_rec emp%ROWTYPE;
BEGIN
 FOR emp IN (SELECT employee_name FROM employee)
  LOOP
     dbms_output.put_line( emp.emp_id || ' ' || emp.employee_name );
  END LOOP;
END;
/

This would display the emp_id and emp name at the console. You can very well use the SPOOL command, to spool this output into the file.
0
 
pratikroyCommented:
oops, did'nt see that seazodiac is not selecting emp_id from employee ... so i missed it ;)

the SQL statement should read "SELECT emp_id, employee_name FROM employee"
0
 
n4nazimCommented:

Here is a procedure which writes CSV flat files using UTL_FILE. The procedure also contains EXCEPTIONs to handle ( that wld help u dubug issues )..


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     => '/csv_folder',
                           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 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;
/

HTH
Rgds,
Nazim M


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now