Topic
  • No replies
RakeshKumarV.V.
RakeshKumarV.V.
1 Post

Pinned topic Derived table from multiple collections

‏2012-03-21T17:45:53Z |
Hi,
What is the best approach to pass a key-value collection to a stored procedure?

I assume we cannot declare a collection variable with more that one column. Something as shown below.
CREATE PROCEDURE my_sp(p_key_values LIST(CHAR(40) not null, INT not null))

So I declared it as two different collection as shown below.
CREATE PROCEDURE my_sp(p_keys LIST(CHAR(40) not null), p_values LIST(INT not null))

I would like to iterate through each key-value pair so as to perform some operation and later insert the key-value pair to a table. I tried out below code but it didn't work. I used the return statement just to output it and check if I am getting the values as expected. Below code fails while execution with the error message - "The number of derived columns do not match the actual number of columns.". Looks as if it is ignoring p_values collection.
FOREACH
SELECT * INTO v_key,v_value FROM TABLE(p_keys, p_values)
RETURN v_key,v_value WITH RESUME;
END FOREACH;

With the collections - p_keys and p_values, is it possible to create a temp table? If that possible then I could you iterated through that temp table. Something as shown below.
FOREACH
SELECT * INTO v_key,v_value FROM temp_table
RETURN v_key,v_value WITH RESUME;
END FOREACH;

Any help in this regard is greatly appreciated. Thanks in advance.
Thanks
Rakesh