Oracle PL/SQL: Dump query result into file

Question

Hi.

I'm working on a pl sql stored procedure. What I need is to do a select, use 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?

Thank alot.

C.C.

Answer 1

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.

Answer 2

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

If you call the script using SQL*Plus, just put the following into your test.sql (the SETs are from SQL*Plus 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  

Answer 3

Tom Kyte has answered this, see

Flat

from this question on Ask Tom

Answer 4

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);