passing arrays as parameters to plsql procedure

Question

I need was playing around with sql developer and I have ran into a wall here... I need to run a simple update query on a table and I want to pass in an array of Ids and update all the rows pointed by those Ids.

I have written the following stored procedure

PROCEDURE SAMPLE_PROCEDURE(SAMPLE_ARRAY IN NUM_ARRAY)
AS
BEGIN
UPDATE RETURNLIST_PICKLIST_MAPPING 
SET PICKLIST_ID = 1111111 
WHERE RETURNLIST_ID IN (SELECT * FROM TABLE(SAMPLE_ARRAY));
END SAMPLE_PROCEDURE;

NUM_ARRAY is a custom type defined as follows

create or replace 
TYPE NUM_ARRAY 
AS VARRAY(40) OF NUMBER(38, 0);

When I run the stored procedure in sql developer I want to input the value for SAMPLE_ARRAY. I have tried (2222,1111,1234) and [2222,1111,1234] and {2222,1111,1234} and each time I get "expression is of wrong type" error.

I desperate need help with this guys....

Hawk · Accepted Answer · 2016-01-20 08:48:02Z

3

You did not show how did you assign values to your varray variable. But, I believe you can do it like this:

DECLARE 
V_T NUM_ARRAY;
BEGIN
V_T := NUM_ARRAY(1,2,3);
SAMPLE_PROCEDURE(V_T);
END;
/

In general, you can define a standalone VARRAY as follows:

CREATE Or REPLACE TYPE varray_type_name AS VARRAY(n) OF <element_type>;

Or, within PL/SQL block:

TYPE varray_type_name IS VARRAY(n) of <element_type>

Refer to this for more details

edited Jan 20, 2016 at 8:48

answered Jan 20, 2016 at 8:42

Hawk

5,17014 gold badges55 silver badges78 bronze badges

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

2 Comments

Giridhar Karnik Over a year ago

how to sense NUM_ARRAY through ADO.net?

Hawk Over a year ago

@HorribleGuy Sorry, that's beyond my knowledge. I suggest you post another question and tag ADO

Collectives™ on Stack Overflow

passing arrays as parameters to plsql procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related