Oracle PL/SQL: Dump query result into file

I'm working on a pl sql stored procedure. What I need is to do a select, us开发者_如何转开发e a cursor and for every record build a string using values. At the end I need to write this into a file. I try to use dbms_output.put_line("toto") but the buffer size is to small because I have about 14 millions lines. I call my procedure from a unix ksh. I'm thinking at something like using "spool on" (on the ksh side) to dump the result of my procedure, but I don' know how to do it (if this is possible)

Anyone has any idea?

Unless it is really necessary, I would not use a procedure.

If you call the script using SQLPlus, just put the following into your test.sql (the SETs are from SQLPlus FAQ to remove noise):

SET ECHO OFF
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET TAB OFF

Select owner || ';' || object_name
From all_objects;

QUIT

and redirect output to a file (test.txt):

sqlplus user/passwd@instance @ test.sql > test.txt

If you really need to do stuff in PL/SQL, consider putting that into a function and call it per record:

Create Or Replace Function calculate_my_row( in_some_data In Varchar2 )
  Return Varchar2
As
Begin
  Return in_some_data || 'something-complicated';
End calculate_my_row;

Call:

Select owner || ';' || calculate_my_row( object_name )
From all_objects;

Performance could suffer, but it should work. Make sure, that what you try can't be done in pure SQL, though.

Reading your comment I think that Analytic Function Lag is what you need.

This example appends * in case the value of val has changed:

With x As (
      Select 1 id, 'A' val FROM dual
Union Select 2 id, 'A' val FROM dual
Union Select 3 id, 'B' val FROM dual
Union Select 4 id, 'B' val FROM dual
)
--# End of test-data
Select
  id,
  val,
  Case When ( val <> prev_val Or prev_val Is Null ) Then '*' End As changed
From (
  Select id, val, Lag( val ) Over ( Order By id ) As prev_val
  From x
)
Order By id

Returns

        ID V C
---------- - -
         1 A *
         2 A  
         3 B *
         4 B  

If every line of your output is the result of an operation on one row in the table, then a stored function, combined with Peter Lang's answer, can do what you need.

create function create_string(p_foobar foobar%rowtype) return varchar2 as
begin
  do_some_stuff(p_foobar);
  return p_foobar.foo || ';' ||p_foobar.bar;
end;
/

If it is more complicated than that, maybe you can use a pipelined table function

create type varchar_array
    as table of varchar2(2000)
/

create function output_pipelined return varchar_array PIPELINED as
  v_line varchar2(2000);
begin
  for r_foobar in (select * from foobar)
  loop
    v_line := create_string(r_foobar);
    pipe row(v_line);
  end loop;
  return;
end;
/ 

select * from TABLE(output_pipelined);  

utl_file is your friend http://www.adp-gmbh.ch/ora/plsql/utl_file.html But is writes the data to the filesystem on the server so you probably need your DBA's help for this.

Tom Kyte has answered this, see

Flat

from this question on Ask Tom