In PL/SQL, how do I declare variable MyListOfValues that contains multiple values (MyValue1, MyValue2, etc.)
SELECT *
FROM DatabaseTable
WHERE DatabaseTable.Field in MyListOfValues
I am using Oracle SQL Developer
-
As far as I'm aware you can't do that (at least not in Oracle 11). I suggest that you build a dynamic query and execute that. Best of luck.Bob Jarvis - Слава Україні– Bob Jarvis - Слава Україні2016-02-05 19:22:57 +00:00Commented Feb 5, 2016 at 19:22
-
1Possible duplicate of How to load a large number of strings to match with oracle database?MT0– MT02016-02-05 19:25:46 +00:00Commented Feb 5, 2016 at 19:25
Add a comment
|
4 Answers
Use a collection:
CREATE TYPE Varchar2TableType AS TABLE OF VARCHAR2(200);
Or use a built-in type like SYS.ODCIVARCHAR2LIST or SYS.ODCINUMBERLIST:
VARIABLE cursor REFCURSOR;
DECLARE
your_collection SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
BEGIN
your_collection.EXTEND( 100 );
your_collection( 1) := 'Some value';
your_collection( 2) := 'Some other value';
-- ...
your_collection(100) := DBMS_RANDOM.STRING( 'x', 20 );
OPEN :cursor FOR
SELECT t.*
FROM your_table t
INNER JOIN
TABLE( your_collection ) c
ON t.id = c.COLUMN_VALUE;
END;
/
PRINT cursor;
Create the SQL type like this:
CREATE TYPE MyListOfValuesType AS TABLE OF VARCHAR2(4000);
And then use it in a SQL statement
DECLARE
MyListOfValues MyListOfValuesType;
BEGIN
MyListOfValues := MyListOfValuesType('MyValue1', 'MyValue2');
FOR rec IN (
SELECT *
FROM DatabaseTable
WHERE DatabaseTable.Field in (
SELECT * FROM TABLE(MyListOfValues)
)
)
LOOP
...
END LOOP;
END;
Up until Oracle 11g, this only works with a SQL TABLE type, not with a PL/SQL TABLE type. With Oracle 12c, you could also use PL/SQL types.
Comments
How about using a WITH clause which basically builds a temp table? Not real reusable. You could use an array or I would argue joining to a lookup table would be better.
WITH MyListOfValues(col1) AS (
select 'MyValue1' from dual union
select 'MyValue2' from dual union
select 'MyValue3' from dual
)
SELECT *
FROM DatabaseTable
WHERE Column in (
select col1
from MyListOfValues);
1 Comment
Michael Grazebrook
This requires only the privileges for the query, the other solution creates a type.
SELECT REGEXP_SUBSTR ('a,b,c,d,e,f,g,h', '[^,]+', 1, LEVEL) pivot_char
FROM DUAL
CONNECT BY REGEXP_SUBSTR ('a,b,c,d,e,f,g,h', '[^,]+', 1, LEVEL) IS NOT NULL;
I got this from:
