OraFAQ Forum: SQL & PL/SQL » PL-SQL set object or selection from table variable

Home » SQL & PL/SQL » SQL & PL/SQL » PL-SQL set object or selection from table variable

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PL-SQL set object or selection from table variable [message #37418]

Wed, 06 February 2002 21:37

Denis
Messages: 8
Registered: December 2000

Junior Member

Hi group,
I need some help with the in-clause for a select statement. Basically I have a prcedure with one parameter:
procedure foo(a integer)
if a's value is say zero I want to perform a selection from a table into a sequence (or table or varray I don't know).

if a = 0 then
<set variable> := select id from table tA;
else
<set variable> := select id from table tA where id = a;

I think this is possible:
select b from tB where id in (select id from table tA);
or this
select b from tB where id in (1, 2, 3);

the problem is that I need to define this set prior to selection.

Is there a way to do this or I need to use dynamic sql?

Thans,
Denis

Report message to a moderator

Re: PL-SQL set object or selection from table variable [message #37424 is a reply to message #37418]

Thu, 07 February 2002 01:03

pratap kumar tripathy
Messages: 660
Registered: January 2002

Senior Member

hi,

try this statement instead of two

select id from table tA
where id = decode(a,0,id,a);

and

select b from tB where id in
(select id from table tA
where id = decode(a,0,id,a)
)

no need to use dynamic pl/sql
cheers
pratap

Report message to a moderator

Previous Topic:	create package
Next Topic:	INSERT using BIG subquery

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 19 03:33:43 CDT 2025