Solved

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

Posted on 2004-04-15
4
3,253 Views
Last Modified: 2007-12-19
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
Comment
Question by:itorbust
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 40 total points
Comment Utility
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
 
LVL 9

Assisted Solution

by:pratikroy
pratikroy earned 20 total points
Comment Utility
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
 
LVL 9

Expert Comment

by:pratikroy
Comment Utility
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
 
LVL 2

Assisted Solution

by:n4nazim
n4nazim earned 40 total points
Comment Utility

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now