0

I'm working on case that allows the user to pass list of IDs and in background convert it into list of string, for instance '112456,450087' to '112456','450087' and it works but passing the final list to query returns no rows

Declare

IDLST varchar2(100);
resLST varchar2(100);
cnt number;

begin

IDLST:= '112345587, 45003421';

select ''''||replace(IDLST,',',''',''')||'''' into resLST from dual ;

DBMS_OUTPUT.PUT_LINE('OUTPUT ' || resLST );

select count(*) into cnt from TABLEX where IDnum in (resLST);

DBMS_OUTPUT.PUT_LINE('Result ' || cnt );

end;
Meemz
  • 1
  • 1
  • resLST is being treated as a single string rather than a list of strings. use Oracle's execute immediate to get your desired result `EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM TABLEX WHERE IDnum IN (' || resLST || ')' INTO cnt;` – Bryan Dellinger Apr 04 '23 at 23:59
  • `resLST` is a bind variable and when you use it in an SQL statement is a single scalar value; it does **NOT** get evaluated as part of the SQL statement (which is a good thing because it helps to prevent your code being vunerable to SQL injection attacks). What you need to do is either: match on sub-strings (as per the accepted answer of the linked duplicate); split the string into rows and match on those split strings (as per the highest voted answer of the linked duplicate); or use collections (as [this answer](https://stackoverflow.com/q/64730770/1509264)). – MT0 Apr 05 '23 at 00:16

0 Answers0