This discussion is archived
3 Replies Latest reply: Nov 21, 2012 7:24 AM by user7988 RSS

Dynamic sql reurns no data when multiple values are passed.

user7988 Newbie
Currently Being Moderated
(Dynamic sql returns no data when multiple values are passed.)

Hi,

While executing the below dynamic sql in the procedure no data is returned when it has multiple input values.

When the input is EMPID := '1'; the procedure works fine and returns data.Any suggestion why the procedure doen't works when input as EMPID := '1'',''2'; is passed as parameter?

=======================================================

create or replace PROCEDURE TEST(EMPID IN VARCHAR2, rc OUT sys_refcursor)
IS
stmt VARCHAR2(9272);
V_EMPID VARCHAR2(100);
BEGIN
V_EMPID :=EMPID;

stmt := 'select * from TEST123 where Empid is NOT NULL';

IF V_EMPID <> '-1' THEN
stmt := stmt || ' and Empid in (:1)';
ELSE
stmt := stmt || ' and -1 = :1';
END IF;

OPEN rc FOR stmt USING V_EMPID;

END Z_TEST;

============================================================
Script for create table
==================================================================

CREATE TABLE TEST123 (
EMPID VARCHAR2(10 BYTE),
DEPT NUMBER(3,0)
)
===========================================
Insert into PDEVUSER.TEST123 (EMPID,DEPT) values ('1',20);
Insert into PDEVUSER.TEST123 (EMPID,DEPT) values ('2',10);
Insert into PDEVUSER.TEST123 (EMPID,DEPT) values ('3',30);
Insert into PDEVUSER.TEST123 (EMPID,DEPT) values ('3',30);
Insert into PDEVUSER.TEST123 (EMPID,DEPT) values ('2',10);
=============================================
Select * from TEST123 where Empid in (1,2,3)

EMPID DEPT
1     20
2     10
3     30
3     30
2     10

===================================================================
Any suggestion why the procedure doen't works when input EMPID := '1'',''2';?

Thank you,
  • 1. Re: Dynamic sql reurns no data when multiple values are passed.
    MLBrown Journeyer
    Currently Being Moderated
    The whole scenario is a little strange. When I tried to compile your procedure it couldn't compile, but I added the missing info and was able to get it compiled.
    create or replace PROCEDURE TEST (EMPID IN VARCHAR2, rc OUT sys_refcursor)
    IS
      stmt        VARCHAR2 (9272);
      V_EMPID     VARCHAR2 (100);
    BEGIN
      V_EMPID := EMPID;
      stmt := 'select * from TEST123 where Empid is NOT NULL';
    
      IF V_EMPID = '-1' THEN
        stmt := stmt || ' and Empid in (:1)';
      ELSE
        stmt := stmt || ' and -1 = :1';
      END IF;
    
      OPEN rc FOR stmt USING V_EMPID;
    END;
    If you pass in 1 as a parameter, it is going to execute because the statement that it is building is:
    select * from TEST123 where Empid is NOT NULL and -1 = 1
    Although the syntax is valid -1 will never equal 1 so you will never get any data.

    If you pass in 1,2 as a parameter then it is basically building the following:
    select * from TEST123 where Empid is NOT NULL and -1 = 1,2
    This will cause an invalid number because it is trying to check where -1 = 1,2

    You could always change your code to:
    PROCEDURE TEST (EMPID IN VARCHAR2, rc OUT sys_refcursor)
    IS
      stmt        VARCHAR2 (9272);
      V_EMPID     VARCHAR2 (100);
    BEGIN
      V_EMPID := EMPID;
      stmt := 'select * from TEST123 where Empid is NOT NULL';
    
      stmt := stmt || ' and Empid in (:1)';
    
      OPEN rc FOR stmt USING V_EMPID;
    END;
    and forget the if v_empid = '-1' check. If you pass in a 1 it will work, if you pass in 1,2 is will work, but don't pass them in with any tick marks.
  • 2. Re: Dynamic sql reurns no data when multiple values are passed.
    Andreas Weiden Guru
    Currently Being Moderated
    This does not work when you pass a value to a bind variable. A bind variable works differently, it does not do a string replacement as in your example.

    What you might do is do a string concatenation when you build up your query, but be aware that this is dangerous in terms of sql-injection (and some other reasons).

    Another is to build a query like
    SELECT 
      FROM TABLE
     WHERE INSTR(:YOUREMPIDS, EMP_ID)>0
    but be ware that this will not use any index on EMP_ID and may be slow on larger tables. Also, you may have to surround your id's by some special character, so that the INSTR does not return wrong result (INSTR(100, 1) also returns true, although you do not want to search for EMPID 1)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points