How to use Collection as a table for UPDATE with an IN clause

Question

I need this piece of code in a stored procedure so I can pass an array of ids and update the related records. I am wondering whether I have to use the loop rather than use an IN clause in the sp.

SET SERVEROUTPUT ON

DECLARE 
  P_IDS PKGINFO.t_ids; --type: table of NUMBER index by pls_integer;
  P_RESULT NUMBER;  

BEGIN 
  p_IDS(1) := 12345;

--this works fine:  
  for i in ( select * from table(p_ids))
    loop    
    UPDATE TABLE1
    SET FD1 = 'test'    
    WHERE     P_ID = i.column_value;
   end loop;

--this works fine too:   
   SELECT COUNT(*) INTO p_RESULT FROM TABLE1
   WHERE  P_ID IN (SELECT * FROM TABLE (p_ids));

--but this does not work, why????? how to make it work?
  UPDATE TABLE1
    SET FD1 = 'test'    
    WHERE  P_ID IN (SELECT * FROM TABLE (p_ids));

END;

--==================PKGINFO.t_ids==================
CREATE OR REPLACE package dbname.PKGINFO as
  -- package created to perform Associative array calls
  type t_ids is table of NUMBER index by pls_integer;
end PKGINFO;
/

I expected the UPDATE can use IN clause, but it gives me an INVALID TYPE error.

Please **edit your question** using the `edit` button just below the tags and include the statement you use to create `PKGINFO.T_IDS` in the question. Thanks. — Bob Jarvis - Слава Україні, Jun 10 '19 at 21:25

Alex Poole · Accepted Answer · 2019-06-11T07:10:09.223

1

Until fairly recently Oracle didn't allow PL/SQL types in SQL statements, including in a table collection expression. You seem to be using a version where support has been added for select but not (yet) for update. If you had one available then you could use a schema-level type. Also have a look at member of.

With the type you have now you could use FORALL, which would be more efficient than a loop with individual updates::

FORALL i IN p_ids.first..p_ids.last
UPDATE TABLE1
  SET FD1 = 'test'    
  WHERE P_ID = p_ids(i);

edited Jun 11 '19 at 07:10

answered Jun 10 '19 at 22:18

Alex Poole

183,384
11
179
318

good to know this... I found the original post where I found the code... it may not work either? https://stackoverflow.com/a/243011/723979 – shrimp rice Jun 11 '19 at 01:58
1

@shrimprice - no, that would work, because it uses a schema-level type rather than a PL/SQL-declared type. I slightly misunderstood the issue you were having, sorry. I've updated the explanation part of my answer. Which version of Oracle are you using, 12cR1? The `FORALL` should work anyway... – Alex Poole Jun 11 '19 at 07:12
Thank you for the FORALL... however, inside the loop I need to do one UPDATE and one INSERT to different tables. So I will need to use FORALL twice. Will that way still be more efficient? – shrimp rice Jun 11 '19 at 14:14
1

Yes, it will still be more efficient than a loop with row-by-row updates and inserts. The `FORALL` is a single bulk operation. – Alex Poole Jun 11 '19 at 14:20
...Something interesting happened today. The code was working when I asked this question. However, today the sp is moved to another environment which I thought it should be the same. Now it gives an error: ERROR- ORA-21700: object does not exist or is marked for delete. I figured out it is the "SELECT" part not working this time! Thanks for your post so I know select was not supported in some version..... So do you have any suggestion about how to use SELECT then? – shrimp rice Jun 18 '19 at 01:50
I found another interesting post here: https://stackoverflow.com/questions/50821267/error-ora-21700-with-table-operator-after-updating-to-oracle-12-2-from-12-1 do you know the answer anyways? – shrimp rice Jun 18 '19 at 01:58

How to use Collection as a table for UPDATE with an IN clause

1 Answers1