Using Variable with IN operator--can it be done?
I am writing a query that has an explicit cursor, into which two parameters are passed. So I am declaring the cursor like so,
CURSOR returns_cur(v_toloc VARCHAR2, v_fromloc VARCHAR2) IS
Select ... etc.etc.
In my BEGIN block, the variables v_toloc and v_fromloc are filled with strings from an input file, then concatenated and formatted to look like so:
'loc1', 'loc2', 'loc3'...etc
What I am trying to do is pass that formatted string variable into my cursor's SELECT statement so that
table.fromname IN (v_fromloc)
and table.toname IN (v_toloc);
but for some reason even though the string values in v_fromloc and v_toloc are formatted like a list of text strings in an IN statement, the cursor absolutely will not run or recognize the values contained in v_fromloc and v_toloc. If I actually type in
table.fromname IN ('loc1', 'loc2', 'loc3...etc.)
and table.toname IN ('loc6, 'loc7', 'loc8'...etc.) then the cursor runs fine. But when I am trying to pass those variables into the cursor to use with the IN statement instead of typing out each string value one at a time, it totally won't work. Can you use the IN operator with a variable name instead of specific text strings? I am trying to use the variable method because there are many, many values to include in the select statement. Writing the "from location" values to a variable and writing the "to location" values to a different variable seemed more efficient then hardcoding the actual numerous values into the Select IN statement. Any ideas anyone?
