Re: Write PL/SQL query results to a file

shhmoo_at_excite.com (robert) wrote in news:114e40a3.0306180716.5aa0b4b8 _at_posting.google.com:

> I'm trying to write the results of my PL/SQL query to a file. I have
> came across a number of possible* solutions such as DBMS_PIPE and
> UTL_FILE. Basically, I am running a batch file on an NT box and would
> like to obtain the results from the DB server running Oracle
> 8.1.7.4.0.
>
> So far I have been able to get export a file to my local machine by
> doing:
> exp 'usr/passwd _at_db' full=no file=%ORACLE_SID%.dmp %ORACLE_SID%.log
>
> I would like to have this file be the output of my query instead. Any
> info appreciated. Thanks in advance.

When you say PL/SQL query do you mean you have a stored procedure that returns a ref cursor?

SQL> create or replace package types as
  2 type rc_t is ref cursor;
  3 end;
  4 /

Package created.

SQL> create or replace function f
  2 return types.rc_t as
  3 rc types.rc_t;
  4 begin

  5      open rc for
  6          select ename, sal, hiredate
  7          from emp where rownum < 4;
  8      return rc;

Function created.

If so look up SQL*Plus in the documentation at http://tahiti.oracle.com. PL/SQL runs in the database so its IO is not great and what there is doesn't have any concept of users at terminals somewhere. So you are always better off using a client tool for this kind of thing, and SQL*Plus is ideal for command line batch files.

Here is an example script that uses the above function f and writes the query results to the file out.txt. You'll need to refer to the documentation to understand what the commands do and modify it if needed.

set termout off
set verify off
set timing off
set trimspool on
var c refcursor
exec :c := f
spool out.txt
print c
spool off
exit

Run the script, f2file.sql in this example, from the command line like this.

$ sqlplus -s martin/bub _at_f2file

$ cat out.txt

ENAME SAL HIREDATE

---------- ---------- ---------
SMITH             800 17-DEC-80
ALLEN            1600 20-FEB-81
WARD             1250 22-FEB-81

If you are using a cursor for loop, try and rewrite as a SQL statement and start again at _ look up SQL*Plus ... _ <vbg>

--
Martin Burbridge