how to use a variable of type dbms_sql.varchr2_table in where clause

Question

I have added rows from a table into a variable of type dbms_sql.varchar2_table.

select id 
  bulk collect into collValyes 
  from tabl1;

collValyes is a variable of type dbms_sql.varchar2_table. Now i have to use collValyes in where clause.Like

update Table2 
   set Status ='R' 
 where id in collValyes .

one xecuting above query , I am getting

PLS-00382: expression is of wrong type

I searched a lot on web but did not find the solutions. Please help

Ollie · Accepted Answer · 2011-10-13 09:38:39Z

3

You can use the FORALL bulk processing for your update:

FORALL x IN INDICES OF collValyes
   UPDATE Table2
      SET status = 'R'
    WHERE id = collValyes(x);

You can find out more about Oracle FORALL here:

Hope it helps...

answered Oct 13, 2011 at 9:38

Ollie

17.6k8 gold badges50 silver badges61 bronze badges

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

1 Answer 1