Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unhappy Unanswered: Variable parameters with the SQL IN function

    Hi all, I am writing a stored procedure which receives a collection of program codes(char(5)). I want to open a cursor that selects the records having their program code in the list of codes I received in parameter.

    The query looks like this:

    SELECT ... FROM ... WHERE program IN (var, var, var);

    I can't find how to use the IN function with a list of variable. Seems like it can only be used with fixed values. Am I wrong?
    Is there a way to check if the program code of a record is in a list of program codes?

    PLEASE I REALLY NEED HELP WITH THIS ONE!!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Variable parameters with the SQL IN function

    Follow this link: http://asktom.oracle.com/pls/ask/f?p...D:210612357425

    It's isn't exactly trivial!

    A simpler but less efficient way would be:
    PHP Code:
      ...
      
    v_bigstring LONG := '#';
    BEGIN
      
    FOR i IN 1..collection.count LOOP
        v_bigstring 
    := v_bigstring || collection(i) || '#';
      
    END LOOP;
      
    SELECT ... FROM ... WHERE INSTR(v_bigstring,'#'||program||'#') > 0;
      ... 
    This works by building a big string like #abc#919#hello#xxx# and then using INSTR to see if the required value is there delimited by #.

  3. #3
    Join Date
    Mar 2004
    Posts
    3

    Unhappy Re: Variable parameters with the SQL IN function

    Well it seems to work well that way (i mean when the select clause is inside the begin section of the procedure). But when the select clause is for use with a cursor it won't work. Seems it doesn't like the 'instr' function or something...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Variable parameters with the SQL IN function

    What does it say then?

  5. #5
    Join Date
    Mar 2004
    Posts
    3

    Talking Re: Variable parameters with the SQL IN function

    Made it work finally. Looks good.
    Thanks a lot for your help.

Posting Permissions

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