Oracle PL/SQL: Dump query result into file

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 OFFSET NEWPAGE 0SET SPACE 0SET PAGESIZE 0SET FEEDBACK OFFSET HEADING OFFSET TRIMSPOOL ONSET TAB OFFSelect owner || ';' || object_nameFrom 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 Varchar2AsBegin  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 dualUnion Select 2 id, 'A' val FROM dualUnion Select 3 id, 'B' val FROM dualUnion Select 4 id, 'B' val FROM dual)--# End of test-dataSelect  id,  val,  Case When ( val <> prev_val Or prev_val Is Null ) Then '*' End As changedFrom (  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 asbegin  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 friendhttp://www.adp-gmbh.ch/ora/plsql/utl_file.htmlBut is writes the data to the filesystem on the server so you probably need your DBA's help for this.