Results 1 to 2 of 2

Thread: Using Variable with IN operator--can it be done?

  1. #1
    Join Date
    Nov 2010
    Posts
    1

    Question 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?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •