Solved

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

Posted on 2004-04-15
4
3,266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 40 total points
ID: 10836569
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
ID: 10837789
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
ID: 10837797
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
ID: 10839525

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
best datatype for oracle table email creation 8 108
Email query results in HTML 6 49
Updating a temp table inside a PL/SQL block 3 64
Shared Service Environment 2 54
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

739 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