CODEWITHSUNDEEP
phporacle-databaseplsql
jvlt
jvlt

Reputation: 13

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

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.

Upvotes: 1

Views: 799

Answers (1)

Shaun Peterson
Shaun Peterson

Reputation: 1790

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.

Upvotes: 1

Related Questions