Re: How to create cursor inside begin/end block in pl/sql

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Wed, 6 Dec 2006 00:06:00 +0100
Message-ID: <1ba001c718c1$ef6532d0$3c02a8c0@JARAWIN>

Hi Singh,

> if (dummy=1)
> then
> cursor c1 is select id_acc from tab1;
> else
> cursor c1 is select id_acc from tab2;

you may additional to dynamic sql (something like OPEN cv FOR v_sql_text) try statical cursor with union all.

CURSOR cv(i_id VARCHAR2) IS
SELECT id FROM tab1 where i_id = 1
union all
SELECT id FROM tab2 where i_id != 1

The filters in the execution plan force based on the actual value of the parameter that at most one part of the union will be performed. See execution plan below. This is - I guess - what you mean. (tested in 10g)

|   0 | SELECT STATEMENT    |      |       |       |    77 (100)|
|   1 |  UNION-ALL          |      |       |       |            |
|*  2 |   FILTER            |      |       |       |            |
|   3 |    TABLE ACCESS FULL| TAB1 |  9999 | 29997 |    38   (0)|
|*  4 |   FILTER            |      |       |       |            |
|   5 |    TABLE ACCESS FULL| TAB2 |  9999 | 29997 |    38   (0)|
-----------------------------------------------------------------

Predicate Information (identified by operation id):

2 - filter(TO_NUMBER(:B1)=1)
4 - filter(TO_NUMBER(:B1)<>1)

Of course for a complex select you should check the execution plan carefully.

Regards,

Jaromir D.B. Nemec

Original Message ----- From: Guang Mei To: Harvinder.Singh_at_MetraTech.com ; oracle-l Sent: Tuesday, December 05, 2006 8:48 PM Subject: RE: How to create cursor inside begin/end block in pl/sql

yes, use dynamic sql

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 05 2006 - 17:06:00 CST