Oracle PLSQL: Write to output file

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.
Who is Participating?
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
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);
  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
      utl_file.get_line(in_file, text_str);
      lines := lines +1;
      when others then
        err_text := substr(sqlerrm,1,200);
        lines := lines +1;
  end loop;
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.

All Courses

From novice to tech pro — start learning today.