PHP - PL/SQL: How to read array (as OUT parameter from Oracle procedure) into PHP

Question

I have a procedure in my Oracle DB with an array as output parameter. In this procedure I put all the teams with their points into an array.

create or replace package pck_tournament
as

  type trranking  is record (
            position  number
       ,    team   VARCHAR2(20)
       ,    points  number 
  );
  type taranking is table of trranking;

  procedure retrieve_ranking (oparray  out taranking);
end pck_tournament;

But when I try to call this procedure with PHP I always get an error like this:

PLS-00306: wrong number or types of arguments in call to 'RETRIEVE_RANKING'

This is a part of my PHP code:

    $out_arr = array();   
    $stmt = oci_parse($conn, "BEGIN pck_tournament.retrieve_ranking(:taranking); END;");
    oci_bind_array_by_name($stmt,":taranking", $out_arr, 10000, 10000, SQLT_CHR );
    oci_execute($stmt);

If I change the OUT parameter to a VARCHAR2 for testing, I'm able to read the result. But I can't manage to make it work if it is an array.

So the problem must be that I use a wrong type of argument to store my OUT parameter?

I've searched a lot of websites but still have no idea how to make this work.

It is possible that you may need to change from using a PL/SQL record data-type and a PL/SQL defined array to using an SQL-defined object and collection data type using CREATE TYPE trranking IS OBJECT( ... ); and CREATE TYPE taranking IS TABLE OF trranking;. — MT0
Good point @MT0 you are quite correct will update my answer. — Shaun Peterson
@MT0 I changed it to an SQL-defined object and collection data type like you said and in PHP I changed the code to: $stmt = oci_parse($conn, "BEGIN pck_tournament.retrieve_ranking(:taranking); END;"); $taranking = oci_new_collection($conn, 'TARANKING', '***'); oci_bind_by_name($stmt, ':taranking', $taranking, 8, OCI_B_NTY); oci_execute($stmt); Now there are no errors anymore ! How can I 'echo' all the items for each object in PHP? @ShaunPeterson — jvlt

Shaun Peterson Shaun Peterson · Accepted Answer · 2020-04-15T22:17:08

What you have in oracle is not a just an array, it is a array of records.... So a standard array in PHP is not going to be able to handle it.

As per the below question here on Stack Overflow you need to tell PHP what the Type is going to look like

PHP: Binding variable to table type output parameter

So use the below (substituting your type and schema)

$table_output = oci_new_collection($conn,'some_table_type','schema');

The other question also has a link to a good resource for finding more information about this.

As pointed out by @MT0 you will have to change the way that you are defining the types as well. You can iether change it to object as suggested or leave it as record, but the major change will be moving the declaration outside of you package.

PHP will not be able to see them if they are only defined in the package.

PHP - PL/SQL: How to read array (as OUT parameter from Oracle procedure) into PHP

1 Answers