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? — Alex Poole
– Alex Poole, Commented Sep 6, 2019 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
– Alex Poole, Commented Sep 6, 2019 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
– NoBullMan, Commented Sep 6, 2019 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
– NoBullMan, Commented Sep 6, 2019 at 18:59

coceban.vlad · Accepted Answer · 2019-09-06 16:17:41Z

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 6, 2019 at 16:17

coceban.vlad

5091 gold badge7 silver badges23 bronze badges

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

3 Comments

NoBullMan Over a year ago

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!

coceban.vlad Over a year ago

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.

NoBullMan Over a year ago

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.

Collectives™ on Stack Overflow

Problem with DELETE in stored procedure using variable in WHERE clause

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related