Oracle: Paramterized Query with IN clause return null value

Question

The Paramterized Query with IN clause is not working; it not able to replace the value

PROCEDURE p_getdata(A IN LONG,B IN LONG,C IN LONG, cur OUT c_data)
AS
l_query LONG;

   BEGIN
 IF C IS NULL THEN
open cur for  'select firstname, lastname, streetname, city
               from mytable 
               where zip IN(:A) AND streetnumber IN(:B) AND apt_num in(:C)' using A, B,C;

 END IF;
END;

here

       a ='202020';
        b='12','13','10','92','02','02'
        c='A','B'

In Db all datatype is varchar2. How to make it run and store the value is cursor?

Community · Accepted Answer · 2017-05-23 11:59:05Z

2

You may pass the comma separated list as a parameter (bind variable), but you are responsible for parsing it in a subquery. A solution based on this thread uses regexp_substr.

CREATE or REPLACE PROCEDURE p_getdata(A IN VARCHaR2, cur OUT sys_refcursor)
AS
BEGIN
open cur for  'with t1 as (select :A col from dual),
     t2 as (select level lvl,to_number(regexp_substr(col,''[^,]+'', 1, level)) col 
              from t1 connect by regexp_substr(col, ''[^,]+'', 1, level) is not null)
select col as id from t2' using A;
END;
/

The procedure is simplified, but should provide a feeling how to use it.

The big advantage against the use of dynamic SQL (concatenation of strings) is that you need not parse the statement on each execution. Not to mention the security (scare of SQL injection).

Usage:

DECLARE 
  l_cur SYS_REFCURSOR;
  l_id NUMBER;
BEGIN 
  p_getdata('1,1000,282828,4',l_cur);
 LOOP
    FETCH l_cur INTO l_id ;
    EXIT WHEN l_cur%NOTFOUND;
    dbms_output.put_line(l_id);
 END LOOP;
END;
/


1
1000
282828
4

UPDATE

The above procedure is simplified, to get your functionality you should use a query like this in the CURSOR (i.e. first split all three parameters in separate subqueries using subquery factoring, than apply the results in your query)

CREATE or REPLACE PROCEDURE p_getdata(A IN VARCHAR2, B in VARCHAR2, c in VARCHAR2, cur OUT sys_refcursor)
AS
BEGIN
open cur for  'with ta1 as (select :A col from dual),
     ta2 as (select level lvl,to_number(regexp_substr(col,''[^,]+'', 1, level)) col 
              from ta1 connect by regexp_substr(col, ''[^,]+'', 1, level) is not null),
 tb1 as (select :B col from dual),
     tb2 as (select level lvl,to_number(regexp_substr(col,''[^,]+'', 1, level)) col 
              from tb1 connect by regexp_substr(col, ''[^,]+'', 1, level) is not null),
 tc1 as (select :C col from dual),
     tc2 as (select level lvl,to_number(regexp_substr(col,''[^,]+'', 1, level)) col 
              from tc1 connect by regexp_substr(col, ''[^,]+'', 1, level) is not null)              
select firstname, lastname, streetname, city
from mytable 
where zip IN (select col from ta2) AND 
      streetnumber IN (select col from tb2) AND 
      apt_num in (select col from tc2)' using A, B, C;
END;
/

test passed

DECLARE 
  l_cur SYS_REFCURSOR;
  l_firstname VARCHAR2(20);
  l_lastname VARCHAR2(20);
  l_streetname VARCHAR2(20);
  l_city VARCHAR2(20);
BEGIN 
  p_getdata('1100,,1200','1,2','11,12' ,l_cur);
 LOOP
    FETCH l_cur INTO l_firstname, l_lastname, l_streetname, l_city;
    EXIT WHEN l_cur%NOTFOUND;
    dbms_output.put_line(l_firstname|| ' ' || l_lastname || ' ' || l_streetname  || ' ' || l_city);
 END LOOP;
END;
/

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Dec 28, 2015 at 21:29

Marmite Bomber

21.3k4 gold badges31 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Rajesh D Over a year ago

Thanks for the reply. But the query open cur for 'select firstname, lastname, streetname, city from mytable where zip IN(:A) AND streetnumber IN(:B) AND apt_num in(:C)' using A, B,C; its not replaced with the value

Rajesh D Over a year ago

means :A , :B, :C paramter value not replaced

Marmite Bomber Over a year ago

@Rajesh I expanded the answer

Rajesh D Over a year ago

Thanks alot for your reply, I m in edge of solution.

Rajesh D Over a year ago

1) with ta1 as (select :A col from dual) 2)tb1 as (select :B col from dual), 3) tc1 as (select :C col from dual), in this 3 lines I am getting Error as "Error(61,18): PLS-00049: bad bind variable 'A'" same for :B and : C

|

Mr. Llama · Accepted Answer · 2015-12-28 16:42:54Z

1

The whole point of using parameters is to make the structure of the query separate from the data of the query. When you make a placeholder, you've essentially made a promise that it will only ever represent one value, not a list of values.

In your case, if you trust your data, you'll need to use string concatenation and omit the USING:

OPEN cur FOR 'SELECT ... WHERE zip IN(' || A || ') AND ...';

answered Dec 28, 2015 at 16:42

Mr. Llama

21k3 gold badges70 silver badges121 bronze badges

1 Comment

Rajesh D Over a year ago

Data not trustable. So they asked me to use only paramterized query to avoid sql injection.

Rajesh D · Accepted Answer · 2015-12-31 06:12:41Z

1

open cur for  select firstname, lastname, streetname, city
           from mytable 
           where zip (SELECT REGEXP_SUBSTR(A,'[^,]+', 1, LEVEL)
  FROM DUAL
    CONNECT BY REGEXP_SUBSTR(A, '[^,]+', 1, LEVEL) IS NOT NULL
   AND streetnumber IN(SELECT REGEXP_SUBSTR(B,'[^,]+', 1, LEVEL)
  FROM DUAL
    CONNECT BY REGEXP_SUBSTR(B, '[^,]+', 1, LEVEL) IS NOT NULL
   AND apt_num in(SELECT REGEXP_SUBSTR(C,'[^,]+', 1, LEVEL)
  FROM DUAL
    CONNECT BY REGEXP_SUBSTR(C, '[^,]+', 1, LEVEL) IS NOT NULL



It is working

edited Dec 31, 2015 at 6:12

answered Dec 30, 2015 at 4:09

Rajesh D

1772 silver badges13 bronze badges

Collectives™ on Stack Overflow

Oracle: Paramterized Query with IN clause return null value

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related