PL/SQL: Using a constant for values in set

Giganews Newsgroups
Subject: PL/SQL: Using a constant for values in set
Posted by:  Randi W (randiwoln…@hotmail.com)
Date: Mon, 26 Apr 2004

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

Replies