Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-04-15
4
Medium Priority
?
3,300 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 160 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 80 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 160 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

783 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