Re: PL/SQL: Using a constant for values in set

On Mon, 26 Apr 2004 14:05:35 +0200, "Randi W" <randiwolner_at_hotmail.com> wrote:

>
>I am writing a PL/SQL program that several times will perform test checking
>whether a value can be found in a set of strings. I would like to have the
>set defined as a constant value in the top of the programt. Is that
>possible?
>
>I would like to do something like this:
>
>ConstFirst CONSTANT VARCHAR2(500) := '''TEST1'', ''TEST2''';
>.
>.
>SELECT 'X'
>INTO V_Found
>FROM TestTable
>WHERE Testcode IN (ConstFirst) AND
>RowNum = 1;
>.
>.
>... and later in the program (TestRec is a row from the open cursor)
>IF TestRec.Testcode IN (ConstFirst) THEN . . .
>
>I have tested this with and without apostrofs. I do not get any compile
>error, but I do not get the result I was hoping for when executing it. Will
>I have to use dynamic SQL to be able to use a constant value this way?
>
>Thanks for any help,
>Randi W
>
>

Hi Randi,

Regarding the SQL :

You can either use Dynamic SQL, which will you give all the flexibility you want. Or you can use some workarounds like :

1. SELECT 'X' INTO V_Found FROM TestTable WHERE Testcode IN (select 'TEST1' from dual UNION select 'TEST2' from dual)

 AND RowNum = 1;

Which does not hold the values in a constant and is very unflexible.

b) Put the values in a physcial table

create table foo (c1 varchar2(100));

insert into foo values ('TEST1');
insert into foo values ('TEST2');

And then

SELECT 'X'
INTO V_Found
FROM TestTable
WHERE Testcode IN (select c1 from foo)
 AND RowNum = 1;

Which is more flexible.

Regarding

    IF TestRec.Testcode IN (ConstFirst) THEN :

The problem is that PL/SQL has no SET-like data structure, i.e. a data structure equivavalent to a "real" table. Thus you can either use a collection :

Type FooT is table of varchar2(100);

Fee FooT;

Fee(1) := 'TEST1';
Fee(2) := 'TEST2';

And then traverse Fee and compare each value to the actual value :

i := Foo.first;
found := false;
while i is not null loop and not(found) loop   found := Fee(i) = TestRec.Testcode;
 i := Fee.next(i)
end loop;

Or (simpler) use the Foo table above :

select count(*)
  into NoHits
   from foo
where c1 = TestRec.Testcode;  

Found := NoHits > 0;

• Kenneth Koenraadt