How to use oci_new_collection in php?

Question

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?

did you tried using 3th parameter for `oci_new_collection` : `oci_new_collection($conn, "PARAMETER_TABLE", "MY_SCHEMA");` — JC Sama, Feb 15 '15 at 17:46
`$parameter_table = oci_new_collection($conn, "PARAMETER_TABLE"); # add var_dump() and show us the result var_dump ($parameter_table); $parameter_table->append(:name => "owner_id", :value => "3945073");` — Halayem Anis, Feb 16 '15 at 10:49

score 2 · Answer 1 · answered Feb 20 '15 at 22:43

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.

score 2 · Answer 2 · edited May 23 '17 at 11:58

2

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

edited May 23 '17 at 11:58

Community

1
1

answered Feb 21 '15 at 05:58

websky

3,047
1
33
31

How to use oci_new_collection in php?

2 Answers2