Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003

    Unanswered: "In" operator in a Store Procedure

    hi guys, I am trying pass a string which contains a list of numbers like (21, 22) into a stored procedure and use it in the "In" operator. The code is as below:

    create or replace procedure Test_Pro
    keyValue in varchar2,
    return_dataSet out sys_refcursor
    open return_dataSet for
    select * from vehicle_platform where SEQ_VEHICLE_TYPE in keyValue;

    The problem is that, if I pass in (21, 22), the procedure will throw a ORA-01722 invalid number exception. It seems oracle tries to convert the string to a number. Is there a solution for this? Many thnx guys....

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    Though it is the WRONG THING to do, you need to use EXECUTE IMMEDIATE
    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
    Jun 2003
    Even if I use EXECUTE IMMEDIATE, how can I sign the data set returned from the select into the refcursor out parameter??? Using select into??

  4. #4
    Join Date
    Mar 2007
    The problem is, Oracle cannot convert the string to list of values or subquery as required in IN Condition. Implicitely, without knowing delimiters and optional enclose characters, it would be impossible.
    However no explicit conversion is available (as far as I know), you shall do it yourself as described on AskTom:
    How can I do a variable "in list"
    varying elements in IN list

Posting Permissions

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