I have SP with custom type:
Create Or Replace Type tyTestArrayType As Object
(siF1 SmallInt,
siF2 SmallInt,
siF3 SmallInt);
Create Or Replace Type tyTestArray Is Table Of tyTestArrayType;
Create Or Replace Procedure prTestArray(pArr tyTestArray,
pResult In Out SmallInt)
Is
Begin
pResult := 0;
For I In 1..pArr.Count
Loop
pResult := pResult + pArr(I).siF1 + pArr(I).siF2 + pArr(I).siF3;
End Loop;
End;
How can I use this procedure (pass multidimensional array) from PHP (oci8)?
Thanks,
Update:
This is a code performing this in Ruby:
plsql.connection = OCI8.new("user","pass","host/XE")
testArray = [{:sif1 => 1, :sif2 => 12, :sif3=>4},{:sif1 => 5, :sif2 => 2, :sif3=>3}]
puts plsql.prTestArray(p_testArray,0) #=> {:presult=>27}
I have developped an identical stored procedure (using Table of ...) as input and it was difficult to be called from Java code. So I switched to a CLOB input and passed an XML from code. In XML you can have an array as multi-dimensional as you wish and it is very easy to build a XML string in PHP.
XML parsing is made easy with ORACLE:
CLOB:
Query:
Output:
Another option would be to store the array in a temporary table and read it from there (no IN parameters).