Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    2

    Unanswered: Where type IN (variable_list)

    Hi All,

    I have a table that looks like:

    Code:
    create table my_table
    (
        our_key  Varchar2(20),
        type       Varchar2(20)
    );
    I have code that looks something like:

    code:

    Code:
    Function get_cursor (ptype IN Varchar2) as Return my_pkg.ref_cursor;
        my_cursor  my_pkg.ref_cursor;
    Begin
    
        open my_cursor for
            select out_key, type
            from my_table
            where type = ptype;
    
        Return my_cursor;
    End get_cursor;
    The table, the code and the cursor are all quite a bit bigger but this is the gist of it.
    It works fine. But I want to change the
    Code:
    type = ptype
    to be
    Code:
    type in (ptype)
    and I want ptype to be a list of one or more strings. I would like to avoid dynamic sql if I can.

    I know that
    Code:
    type in ('ABCD','EFGH')
    will work so I tried
    Code:
    ptype := '''ABCD'''||','||'''EFGH''';
    and plugged in ptype and it failed.

    I tried
    Code:
    type varray_type is varray(3) of varchar2(30);
    ta  varray_type;
    ta(1) := 'ABCD';
    ta(2) := 'EFGH';
    ta(3) := 'IJKL';
    type in (ta)
    and got a compile error.

    Code:
    type in (ta(1),ta(2),ta(3))
    works fine but I don't know at compile time how many items the user will want to find so I can't use a fixed number of variables

    is there some way for me to get a list of items into this where clause without using dynamic sql?

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >is there some way for me to get a list of items into this where clause without using dynamic sql?
    It depends.
    At run time, what is the decision criteria to determine what should be included within the IN list?


    WHERE TYPE IN (SELECT ITEM FROM CODE_TABLE WHERE DATE_CREATED > TRUNC(SYSDATE-1))

    above is valid SQL construct
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2007
    Posts
    2
    There will be a user interface, two actually, one in java and one in C#. The user will input a list of values that will be passed to my plsql.

    I was hoping to find a way to plug that list directly into the WHERE clause, without using dynamic sql. I will use D sql if I have to, this is probably one of the reasons they created it.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The user will input a list of values that will be passed to my plsql.

    be VERY, VERY wary of SQL injection attacks
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    You can find appropriate solution in the following AskTom threads:
    varying elements in IN list
    How can I do a variable "in list"
    I agree that creating dynamic SQL is not proper in this case.
    [Edit: added last sentence]

Posting Permissions

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