Pass multidimensional array to Oracle stored procedure

Question

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}

This array has one dimension, so it is not multidimensional. — kubanczyk
– kubanczyk, Commented Jan 15, 2012 at 19:48
Yes, but I need to pass array of tyTestArrayTypes: pArr = array(tyTestArrayType,tyTestArrayType,tyTestArrayType...) — S2201
– S2201, Commented Jan 16, 2012 at 11:34

Samson · Accepted Answer · 2012-05-08 12:05:53Z

1

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:

<?xml version="1.0"?>
<DATA>
     <LINE>
        <COD_NOMCPDT>040001</COD_NOMCPDT>
     </LINE>

     <LINE>
        <COD_NOMCPDT>040002</COD_NOMCPDT>        
     </LINE>    
</DATA>

Query:

SELECT
extractvalue(column_value, '/LINE/COD_NOMCPDT')
  FROM TABLE(XMLSequence(XMLTYPE(
               CLOB_VARIABLE).extract('/DATA/LINE'))) );

Output:

040001
040002

Another option would be to store the array in a temporary table and read it from there (no IN parameters).

edited May 8, 2012 at 12:05

answered May 8, 2012 at 10:51

Samson

2,8317 gold badges40 silver badges55 bronze badges

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

Collectives™ on Stack Overflow

Pass multidimensional array to Oracle stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related