Re: Building temporary result set in PL/SQL

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 13 May 2002 05:12:44 -0700
Message-ID: <aboajs0dsb@drn.newsguy.com>

In article <3CDF86DF.3060903_at_kikobu.com>, Morten says...
>
>
>Hi. Can someone point me in the right direction on how I
>programatically build a result set in PL/SQL? I could
>insert into a temporary table and select * from that,
>but can I not avoid the temporary table?
>
>I'm not quite sure what to look for in the documentation.
>In effect, what I'm looking for, is an alternative to the
>below routine, that does not use inserts, but returns the
>output relation.
>
>declare
>cursor c is
>select /*+ FIRST_ROWS */ id, score(1) from table_a
>where contains(content, '((test%)*(1/10))', 1) > 0
>order by score(1) desc;
>begin
>for i in c
>loop
>
>INSERT INTO temp_table(id, score) ...
>
>exit when c%rowcount > 21;
>end loop;
>end;
>/
>
>Thanks,
>
>Morten
>

see
http://asktom.oracle.com/~tkyte/ResultSets/index.html

and you'll see how the above procedure will become something like:

SQL> create or replace package demo_pkg
  2 as
  3 type rc is ref cursor;
  4
  5 procedure get_data( p_cursor in out rc, p_search in varchar2 );   6 end;
  7 /

Package created.

SQL> 
SQL> 
SQL> create or replace package body demo_pkg

  2 as
  3
  4
  5 procedure get_data( p_cursor in out rc, p_search in varchar2 )   6 is
  7 begin

  8      open p_cursor for
  9      'select *
 10         from ( select /*+ FIRST_ROWS */ displayid, score(1)
 11                  from WWC_ASK_INDEXED_QUESTIONS$
 12                 where contains( text, :x, 1 ) > 0
 13                 order by 2 desc
 14              )
 15        where rownum < 21' USING p_search;

 16 end;
 17
 18 end;
 19 /

Package body created.

SQL> 
SQL> variable x refcursor
SQL> 
SQL> exec demo_pkg.get_data( :x, 'Oracle' )

PL/SQL procedure successfully completed.

SQL> print x

 DISPLAYID SCORE(1)
---------- ----------

1.1442E+12        100
1.6694E+12        100
1.9346E+12        100
2.2141E+11        100
3.5757E+12        100
2.8992E+12        100
2.4094E+12        100
2.5920E+12        100
3.3612E+12        100
1.1123E+12        100
5.9882E+11        100
2.2490E+12        100
1.5588E+12        100
1.3572E+12        100
2.4179E+12        100
5.0302E+11        100
9.6858E+11        100
7.0160E+11        100
3.4831E+12        100
2.8380E+12        100

20 rows selected.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon May 13 2002 - 07:12:44 CDT