Problem with DELETE in stored procedure using variable in WHERE clause

Question

I am trying to delete some records in a SP before doing subsequent inserts. When I use a variable in WHERE clause of the DELETE, it doesn't work. When I replace the variable with its value, it deletes the records. I am guessing it is something I am doing with placing the variable in WHERE clause but can't figure out what it is. I tried different ways, none of which worked.

PROCEDURE USPGETOUTCOME
(
IPSITEMIDS                VARCHAR2,
....
CUR_OUT                   OUT GETDATACURSOR
)
IS
....
....    
V_TEMP VARCHAR(200);
V_NEWITEMSLIST VARCHAR2(4000) := REPLACE(IPSITEMIDS, '''', '');


CURSOR cur IS
SELECT  REGEXP_SUBSTR(V_NEWITEMSLIST, '[^,]+', 1, LEVEL) V_NEWITEM2 FROM DUAL CONNECT BY instr(V_NEWITEMSLIST, ',',1, LEVEL -1) > 0;

BEGIN

-- IPSITEMIDS is ''EM060500103','LP060500105''   
V_TEMP := TRIM(BOTH '''' FROM IPSITEMIDS);

-- After above statement V_TEMP shows as 'EM060500103','LP060500105'

-- These don't work
--    DELETE FROM TEMPOUTCOME WHERE ITEMID IN (IPSITEMIDS);
--    DELETE FROM TEMPOUTCOME WHERE ITEMID IN (V_TEMP);
--    DELETE FROM TEMPOUTCOME WHERE ITEMID IN ('''|| V_TEMP ||''');
-- This works   
      DELETE FROM TEMPOUTCOME WHERE ITEMID IN ('EM060500103','LP060500105');
COMMIT;

-- This is using cursor to loop through each item and delete one by one; works but seems a waste!

-- Delete existing records from temp table for given ITEM IDs
FOR rec IN cur LOOP   
    V_NEWITEM := rec.V_NEWITEM2;
    DELETE FROM IDCTEMPOUTCOME WHERE ITEMID IN (V_NEWITEM);
END LOOP;
COMMIT;

Possible duplicate of [How to use parameters in a 'where value in...' clause?](https://stackoverflow.com/questions/11041647/how-to-use-parameters-in-a-where-value-in-clause) — Alex Poole, Sep 06 '19 at 15:46
Why not? Your variable version ends up being single string with a comma in it, not a list of comma-separated strings as in your working version. That's the same problem as that duplicate. — Alex Poole, Sep 06 '19 at 18:09
I can use a cursor to loop through each Item ID and delete the records one by one but it seems to me it would be a waste to delete one by one when the whole thing can be done in query. I update the question to show the cursor version. — NoBullMan, Sep 06 '19 at 18:50
When I TRIM ''A','B'', I don't get a single string like 'A,B'; I get 'A','B' which looks like the hard-coded version but somehow it doesn't work whereas the hard-coded version works. — NoBullMan, Sep 06 '19 at 18:59
No you don't, you get a single string; I think you're confusing quotes embedded within a string with the string delimiters. [In this db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=5fa8b862e8b0f5bb3ef51316c507635b) I've attempted to make the difference clearer using angle brackets. Another comment mentions c#, maybe you can avoid the whole mess by passing a collection instead of a string, [as shown in this other duplicate](https://stackoverflow.com/q/1625649/266304)? — Alex Poole, Sep 09 '19 at 08:16

score 0 · Accepted Answer · answered Sep 06 '19 at 16:17

0

You must split the values.

Check in my example

--      BANNER
-- 1    Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-- 2    PL/SQL Release 11.2.0.2.0 - Production
-- 3    CORE    11.2.0.2.0  Production
-- 4    TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
-- 5    NLSRTL Version 11.2.0.2.0 - Production

DECLARE
  l_n NUMBER;
  l_filter VARCHAR2(100) := '''PL/SQL Release 11.2.0.2.0 - Production'',''NLSRTL Version 11.2.0.2.0 - Production''';
BEGIN
  SELECT COUNT(banner) AS n INTO l_n FROM v$version where banner IN (select REGEXP_SUBSTR(l_filter, '\''(.*?)\''(?:\,)?', 1, LEVEL, NULL, 1) FROM dual CONNECT BY LEVEL <= REGEXP_COUNT(l_filter, '''(?: +)?(\,)(?: +)?''', 1) + 1);
  DBMS_OUTPUT.put_line(l_n);
END;

answered Sep 06 '19 at 16:17

coceban.vlad

509
1
7
23

And then delete one by one? Can you give the example using the variables I declared in original post? Not an Oracle guy, a C# developer tasked with updating SP! – NoBullMan Sep 06 '19 at 19:02
Hello @NoBullMan. You ca change my SELECT statement into a DELETE and delete them all at once. It was only a example to show the number of matches based on the value of the variable (right as in your example). Let me know please if it doesn't work. – coceban.vlad Sep 06 '19 at 19:49
Changed `l_filter` to `IPSITEMIDS` which was list of item IDs, as string enclosed in single quotes, and changed SLEECT to DELETE and it worked without the need to loop through each item ID. Thanks. – NoBullMan Sep 09 '19 at 13:57

Problem with DELETE in stored procedure using variable in WHERE clause

1 Answers1