Home » SQL & PL/SQL » SQL & PL/SQL » passing in nested (multi-dimensional) varray to procedure?
passing in nested (multi-dimensional) varray to procedure? [message #219397] Wed, 14 February 2007 04:28
Messages: 1
Registered: February 2007
Junior Member
Hi all,

In my web app I have a large grid of unknown dimensions, the values of which I want to pass into a stored procedure to loop through and merge into a table.

What I want to do is (very much simplified) this:
create or replace TYPE triples IS VARRAY(3) OF NUMBER;
create or replace TYPE triples_array IS VARRAY(106) OF triples;

PROCEDURE set_triples_test(vals in triples_array) AS
  FOR counter in 0..vals.count LOOP
    INSERT INTO triples_test (val1, val2, val3) 
    VALUES (vals(counter)(1), vals(counter)(2), vals(counter)(3));
END set_triples_test;

However, when I try to compile that I get the following error:
PLS-00224: object 'VALS' must be of type function or array to be used this way.
(Specifically it's the second index causing the error.)

All documentation I can find is talking about tables with columns of datatype varray, which is useless to me, as my problem is with passing in a multi-dimensional array from PHP. (Likewise, I don't believe nested tables to be of any use to me, as I'd have no idea how to generate one such in PHP. (I suspect hoping for an automatic conversion from array to table would be too much.))

The oracle 8 documentation stated "You cannot create nested VARRAY or nested table types. That is, VARRAY and nested table types cannot contain any elements that are VARRAYs or nested tables." - however, I can't find any similar statement for oracle 10, therefor I believe this should be possible now. If only I could figure out how to address the second dimension in my SQL.

For the moment I've solved this problem by passing in three varrays each with a single dimension, and explicitly assuming that these three varrays will have the same length, but that's a pretty ugly solution, and an assumption I really don't want to be forced to make.

Thanks in advance for any pointers!

edit: minor wtf?? here as what I think is still exactly identical to the above (but obviously isn't) suddenly compiles but instead I get:
Warning: oci_execute() [function.oci-execute]: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SET_TRIPLES_TEST' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

edit the second: okay, I have no idea what happened the first time, but it's starting to look like this isn't an oracle problem at all, but rather a PHP problem, where it's oci_bind_array_by_name() which doesn't know how to deal with the nested array. Meh. I'll leave my original text as it might someday be of help to someone else.

[Updated on: Wed, 14 February 2007 06:08]

Report message to a moderator

Previous Topic: duplicate values
Next Topic: Update statement
Goto Forum:

Current Time: Sat Jul 22 07:49:13 CDT 2017

Total time taken to generate the page: 0.05532 seconds