CODEWITHSUNDEEP
phporacle-databaseoracle-call-interface
HelloWorld
HelloWorld

Reputation: 4349

How to use oci_new_collection in php?

I am trying to call a stored procedure using php 5, my question is how would I use the 'oci_new_collection' function properly? I couldn't find any examples on the php site. For one of the variables that needs to be passed to the stored procedure it uses this custom user defined table type...

SQL> desc parameter_table
 parameter_table TABLE OF PARAMETER_TYPE
 Name                   Null?    Type
 ---------------------- -------- ----------------------------
 NAME                            VARCHAR2(200)
 VALUE                           VARCHAR2(4000)

So my guess is that i need to use the oci_new_collection to use this table type. My code is as follows...

    $conn = DBConnect::getConnection();
    $parameter_table = oci_new_collection($conn, "PARAMETER_TABLE");
    //need to do something here....
    $parameter_table->append(:name => "owner_id", :value => "3945073");
    //

    $curs = oci_new_cursor($conn);
    $stid = oci_parse($conn, "begin reporting.execute_report(:name, :plist, :out); end;");
    oci_bind_by_name($stid, ':name', "TRAFFIC_ANALYSIS_CALL_SUMMARY");
    oci_bind_by_name($stid, ':plist', $parameter_table);
    oci_bind_by_name($stid, ":out", $curs, -1, OCI_B_CURSOR);
    oci_execute($stid); 

    oci_execute($curs);  // Execute the REF CURSOR like a normal statement id
    while (($row = oci_fetch_array($curs, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
        echo $row[0] . " " . $row[1] . "<br>\n";
    }

How would I populate the name/value fields for the $parameter_table variable?

Upvotes: 4

Views: 1774

Answers (2)

websky
websky

Reputation: 3172

nested table

CREATE OR REPLACE TYPE name_ntable
AS TABLE OF VARCHAR(60 CHAR)

variable definition - OCI-Collection:

$collection = oci_new_collection($connect, 'NAME_NTABLE');

because the variables are the type oracle you need to use oci_bind_by_name

other

Access a varray type defined inside a package using oci_new_collection

https://community.oracle.com/message/2436812

Upvotes: 2

quickshiftin
quickshiftin

Reputation: 69731

Goggling around for oci_new_collection doesn't turn up much, nor do the docs on the PHP website.

I did however find there are some usage examples in the phpt tests that ship with the PHP source. You'll find them in the ext/oci8/tests directory of a PHP source distribution. There's roughly a dozen files that call oci_new_collection.

You may be able to reverse engineer your way to an answer with those.

Upvotes: 2

Related Questions