Solved

Oracle PLSQL: Write to output file

Posted on 2004-03-22
1
2,351 Views
Last Modified: 2010-10-05
Hello Everyone,

I'm new to PLSQL and I been testing some of its features.  I need to select columns from a table and write them to an output file.  How can I do this?  For example, let say that I have an "EMP" table that has "NAME", and "Address" columns.  How can I select both of these columns and write it to a file using PL-SQL?

(I've been trying to use a package called "logfilepkg.writelogline" which always seems to give me 21/13    PLS-00201: identifier 'LOGFILEPKG.WRITELOGLINE' must be declared
21/13    PL/SQL: Statement ignored.)

Can anyone provide me with a simple example just to get me started?  Thank you very much 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
1 Comment
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 120 total points
ID: 10651051
PL\SQL can be used to write output to a text file.  That is not the simplest way to get Oracle data to a text file though.   The simplest way is via SQL*Plus in three steps:
1. spool [a valid path/file_name]
2. select ...
3. spool off

If you want to use PL\SQL, the package that has the procedures you need is utl_file.  Here is an example of that approach:
create or replace procedure sample (path_nm in varchar2, file_nm in varchar2,
 lines_to_read in number default 3, chars_per_record in number default 999,
 output_path in varchar2 default 'C:\temp') as
-- This procedure opens a text file in any directory, reads three lines from it
--  and writes them to C:\temp\temp.txt.
-- This is intended for large text files, to quickly get a small section of the file that can
--  be opened and browsed more easily in a text editor.
  in_file   utl_file.file_type;
  out_file  utl_file.file_type;
  text_str  varchar2(4000);
  lines     pls_integer;
  err_text  varchar2(200);
begin
  lines := 0;
  out_file := utl_file.fopen(output_path,'temp.txt','W');
  in_file := utl_file.fopen(path_nm,file_nm,'R',chars_per_record);
  while lines < lines_to_read loop
    begin
      utl_file.get_line(in_file, text_str);
      utl_file.put_line(out_file,nvl(text_str,to_char(lines)));
      lines := lines +1;
    exception
      when others then
        err_text := substr(sqlerrm,1,200);
        lines := lines +1;
        utl_file.put_line(out_file,err_text);
    end;
  end loop;
  utl_file.fclose(in_file);
  utl_file.fclose(out_file);
end;
/
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

635 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