2

I would like to, in a stored proc, update all the records that match an id. Now, this list of id, is being passed in as a table of varchar (Associative Array)..

CREATE TYPE varcharArray AS TABLE OF VARCHAR2(1000) index by ...

and the proc declaration is something like

PROCEDURE testProc (p_IDs in varcharArray, p_Success out Number)

and the update statement in the proc

update testtable 
set col = 'val' 
where id in (select column_value from table(p_IDs);

This doesn't seem to work. So i had to do a loop in the array and update for each Id.

But i'd really like it to update using the where in clause.. Any help would be great.

PS: the id field is a number.

Ocelot
  • 1,733
  • 4
  • 29
  • 53
  • What does the "..." after your CREATE TYPE mean? – Tony Andrews Jun 02 '15 at 12:56
  • thats nothing, will update. – Ocelot Jun 02 '15 at 12:56
  • OK, then it isn't in fact an associative array, it is "nested table". An associative array is a PL/SQL type where you specify the index type e.g. "index by pls_integer" or "index by varchar2(40)" – Tony Andrews Jun 02 '15 at 13:04
  • actually there is an index by. sorry about that. Updated in question. – Ocelot Jun 02 '15 at 13:05
  • But then the `create type` is wrong, because you cannot create an associative array in the database, you can only declare it in a PL/SQL block. – Tony Andrews Jun 02 '15 at 13:09
  • I've declared it in a package. The stored proc is in the same package. – Ocelot Jun 02 '15 at 13:09
  • Then remove the "index by" clause to make it a nested table, and ensure that the type is declared in the package spec rather than in the body. Then it should work. – Tony Andrews Jun 02 '15 at 13:11
  • It is declared in the package spec and not in the body. Plus, i need it to be an associative array because i'm invoking it from .Net managed data access which supports Associative arrays but not udt. – Ocelot Jun 02 '15 at 13:12
  • In that case I think you will need to transfer the data from the associative array into a nested table and then query from that. – Tony Andrews Jun 02 '15 at 13:19
  • oh.. wow, could you please give me a snippet of that? You can probably add it to your answer, so that if it works, i can upvote. – Ocelot Jun 02 '15 at 13:20
  • for i in 1..assocarry.count loop nestedtable(i) := assocarray(i); end loop; – Tony Andrews Jun 02 '15 at 13:22
  • ok, thanks for that.. will try this too.. and let you know how it goes. – Ocelot Jun 02 '15 at 13:25

1 Answers1

0

It works for me:

create table testtable (col varchar2(10), id varchar2(1000));
insert into testtable values (null, 'AAA');
insert into testtable values (null, 'BBB');
insert into testtable values (null, 'CCC');
insert into testtable values (null, 'DDD');
commit;


create or replace PROCEDURE testProc (p_IDs in varcharArray) is
begin
update testtable
set col = 'val'
where id in (select column_value from table(p_IDs));
end;
/

exec testproc(p_ids=>varcharArray('AAA','DDD'));

SQL> select * from testtable;

COL        ID
---------- ----------
val        AAA
           BBB
           CCC
val        DDD
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • how about create table testtable (col varchar2(10), id number); is there a way to cast the varcharArray .. something like : where in (select to_number(column_value) from table(p_IDs)) – Ocelot Jun 02 '15 at 12:58
  • oh, i was just guessing.. I didn't know that theres a to_number() cast. i'll try it out. – Ocelot Jun 02 '15 at 13:03