0

I am using a variable to store numeric value range like the following:

:p_where := 7||','||8||','||9;

select * from <TABLE> where
col1 in (:p_where);

it will give the following error:

ORA-01722: invalid number

How to handle this issue?

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
Al Zoabi Baker
  • 1
  • 1
  • There might be an answer for this question here [Sending an array of values to Oracle procedure to use in WHERE IN clause](http://stackoverflow.com/questions/13580245/sending-an-array-of-values-to-oracle-procedure-to-use-in-where-in-clause) – A.B.Cade Nov 03 '13 at 09:49

1 Answers1

2

If you do it the way you have shown us, you end up with this:

col1 in ('7,8,9');

instead of:

col11 in (7, 8, 9)

What you have to do is write a PIPELINED function that will tokenize a comma-delimited list of numbers and allow you to query that list and put them in IN list. I'm not sure this will work from Oracle Forms, please see the below example and try using it and give me some feedback:

CREATE TABLE test_pipe (
  id NUMBER,
  name VARCHAR2(20),
  salary NUMBER
);

INSERT INTO test_pipe VALUES (1, 'Smith', 5000);
INSERT INTO test_pipe VALUES (2, 'Brown', 8000);
INSERT INTO test_pipe VALUES (3, 'Bay', 10000);

COMMIT;

CREATE TYPE t_pipe_test_num_tab IS TABLE OF NUMBER;
/

CREATE OR REPLACE FUNCTION test_func_num_pipe(p_list IN VARCHAR2)
  RETURN t_pipe_test_num_tab
  PIPELINED
AS
BEGIN
  FOR v_value IN (
          SELECT regexp_substr(p_list, '[^,]+', 1, level) AS token FROM dual
          CONNECT BY regexp_substr(p_list, '[^,]+', 1, level) IS NOT NULL
        )
  LOOP
    PIPE ROW (v_value.token);
  END LOOP;
END;
/

SELECT *
  FROM test_pipe
WHERE id IN (SELECT COLUMN_VALUE FROM TABLE(test_func_num_pipe('1,3')));

Output:

        ID NAME                     SALARY
---------- -------------------- ----------
         1 Smith                      5000 
         3 Bay                       10000
Przemyslaw Kruglej
  • 8,003
  • 2
  • 26
  • 41