Re: Temporary table dynamic SQL cursor

From: bpolarsk <bpolarskNObpSPAM_at_yahoo.com.invalid>
Date: Wed, 15 Dec 1999 05:05:43 -0800
Message-ID: <176f8e61.75b0598c@usw-ex0107-041.remarq.com>

May be the solution I propose is not relevant, but if you have a query on tables with different level of aggregation, you may still obtain your results from a virtual table which is not a subquery nor a real temporary table.

ie :

       select a.f1,a.f2, b.f1,b.cpt
              from table_1 a,
                   ( select fz f1, count(fz) cpt from table_2
                   group by fz) b
              where a.f1 = b.f1
              order by b.f1

I noticed that people often create temporary table for 'b' while it is not always useful. Of course table_2 may also be a select set.

If you do not need to modify data in table temp, you may consider build your cursor that way.

<cut>
set serveroutput on
declare cursor c1 is

         select a.sid,b.username,a.object
         from v$access a, (
    select sid,username from v$session ) b
        where a.sid = b.sid ;

    ll c1%rowtype ;
begin
  open c1;
  loop
  fetch c1 into ll ;

        exit when c1%notfound ;
        dbms_output.put_line('sid=' || to_char(ll.sid)|| ' object='||

ll.object);
 end loop ;
end;

</cut>

.
.
.

sid=9 object=DBMS_APPLICATION_INFO
sid=9 object=DBMS_OUTPUT
sid=9 object=DBMS_STANDARD
sid=9 object=DUAL
sid=9 object=STANDARD

.
.
.

Hope it helped

B. Polarski

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!

Received on Wed Dec 15 1999 - 07:05:43 CST