3

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?

Improve this question
5
  • Out of curiosity, did you try my solution? Commented Mar 31, 2010 at 12:30
  • I allready answered. I cannot afford doing more that one time the query. Your solution (the Analytic Function) need the query to be executed more than one. The other solution does not work neither because I need to have the value of a field from the previous iteration. Commented Mar 31, 2010 at 15:40
  • Why do you need to execute the query more than once using Analytic Functions? Did you read my last comment? Commented Mar 31, 2010 at 15:53
  • 1
    Maybe I was wrong. I will try to execute your solution. Commented Mar 31, 2010 at 16:18
  • I just tryied your solution and it's even better than the one I choosed first time. At first I misunderstood but now I got it. Thanks alot. Commented Apr 1, 2010 at 7:10

4 Answers 4

Reset to default
8

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This does not work also, because the problem is that I need to do some stuff for every row, so I did a cursor and inside I do my stuff for every line.
Please see my updated answer. What are you trying that can't be done in pure SQL?
Not bad this solution, but...:) The treatement I do on each line is: If the before value of a field is different that the current value, I will concatenate something, else do nothing. So using a cursor it works, but I cannot/ I don't know how to dump the result.
Interesting solution but I have 14 millions lines, and I'm trying to do something in a reasonable time. I cannot afford to do 4 times the select.
Not sure what you mean? My 4 selects are only to provide some test-data. Remove the part before End of test-data and select from your table. Performance should actually be a lot better than with your current solution.
1

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);
Improve this answer

1 Comment

I'm trying this solution, but I since I have huge amount of data, can I use it? v_line will have more that 2000 caracters? Does it still work? Or in v_line is only the infromation of one line ?
0

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.

Improve this answer

1 Comment

I saw the utl_file but I cannot use it because I don't have the rights.
0

Tom Kyte has answered this, see

Flat

from this question on Ask Tom

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.