How to call Oracle stored procedure with object type as output parameter? (PLS-00306: wrong number or types of arguments in call)

Question

How to call Oracle stored procedure with object type as output parameter? (PLS-00306: wrong number or types of arguments in call)

Hello,

I am trying to call a stored procedure from PHP Lumen v6.3 which is defined in Oracle as follows:

PROCEDURE PRC_REGION_LIST(
    p_TYPE          IN    SMALLINT,
    p_REGION_LIST   OUT   CT_LST_REGION,
    p_COD_ERROR     OUT   NUMBER, 
    p_DES_ERROR     OUT   VARCHAR2)
IS
    v_LST_AUX CT_LST_REGION;
BEGIN
    ..........
EXCEPTION 
    WHEN OTHERS THEN 
        p_COD_ERROR := SQLCODE; 
        p_DES_ERROR := SQLERRM; 
END PRC_REGION_LIST;

create or replace TYPE "CT_LST_REGION" AS TABLE OF T_LST_REGION;

create or replace TYPE "T_LST_REGION" AS OBJECT (
    REG_ID      NUMBER(10,0),
    REG_NAME    VARCHAR2(50 BYTE)
);

In my controller, I have a function called getRegionsPDO() where the procedure is executed manually and another function called getRegionsYajra() where the procedure is executed with shortcut method. The code is the following:

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use PDO;

class ExampleController extends Controller
{
    public function getRegionsPDO()
    {
        $procedureName = 'PKG_NAME.PRC_REGION_LIST';

        $p_TYPE = 1;
        $p_REGION_LIST = "-";
        $p_COD_ERROR = "-";
        $p_DES_ERROR = "-";

        $pdo = DB::getPdo();

        $pdo = $pdo->prepare("begin {$procedureName}(:p_TYPE, :p_REGION_LIST, :p_COD_ERROR, :p_DES_ERROR); end;");

        $pdo->bindParam(':p_TYPE', $p_TYPE, PDO::PARAM_INT);
        $pdo->bindParam(':p_REGION_LIST', $p_REGION_LIST, PDO::PARAM_STR);
        $pdo->bindParam(':p_COD_ERROR', $p_COD_ERROR, PDO::PARAM_INT);
        $pdo->bindParam(':p_DES_ERROR', $p_DES_ERROR, PDO::PARAM_STR);

        $pdo->execute();

        dd($pdo);
    }

    public function getRegionsYajra()
    {
        $procedureName = 'PKG_NAME.PRC_REGION_LIST';

        $bindings = [
            'p_TYPE' => 1
        ];

        $result = DB::executeProcedure($procedureName, $bindings);

        dd($result);
    }
}

When I make the call to getRegionsPDO() it shows me the following message:

<h3 class="trace-class">
    <span class="text-muted">(1/1)</span>
    <span class="exception_title">
        <abbr title="Yajra\Pdo\Oci8\Exceptions\Oci8Exception">Oci8Exception</abbr>
    </span>
</h3>
<p class="break-long-words trace-message">
    Error Code    : 6550
    <br>
    Error Message : ORA-06550: line 1, column 7:
    <br>
    PLS-00306: wrong number or types of arguments in call to 'PRC_REGION_LIST'
    <br>
    ORA-06550: line 1, column 7:
    <br>
    PL/SQL: Statement ignored
    <br>
    Position      : 6
    <br>
    Statement     : begin PKG_NAME.PRC_REGION_LIST(:p_TYPE, :p_REGION_LIST, :p_COD_ERROR, :p_DES_ERROR); end;
    <br>
    Bindings      : [1,-,0,-]
    <br>
</p>

And when I make the call to getRegionsYajra() it shows me the following:

<h3 class="trace-class">
    <span class="text-muted">(1/1)</span>
    <span class="exception_title">
        <abbr title="Yajra\Pdo\Oci8\Exceptions\Oci8Exception">Oci8Exception</abbr>
    </span>
</h3>
<p class="break-long-words trace-message">
    Error Code    : 6550
    <br>
    Error Message : ORA-06550: line 1, column 7:
    <br>
    PLS-00306: wrong number or types of arguments in call to 'PRC_REGION_LIST'
    <br>
    ORA-06550: line 1, column 7:
    <br>
    PL/SQL: Statement ignored
    <br>
    Position      : 6
    <br>
    Statement     : begin PKG_NAME.PRC_REGION_LIST(:p_TYPE); end;
    <br>
    Bindings      : [1]
    <br>
</p>

I want to know if someone can help me with the code needed to call the stored procedure in the correct way from my controller. I know the error is in my p_REGION_LIST parameter.

PS: I cannot modify what is in the Oracle Database.

System details

• Operating System: Windows 10
• PHP v7.4.10
• Laravel Version: Lumen Framework v6.3
• Laravel-OCI8 v6.1.1
• laravel-pdo-via-oci8 v2.1.1

Thank you all so much for your help

Answer 1

In your anonymous BEGIN/END block, compose or decompose the procedure's object parameter using simple types that PHP can bind to.

There's a related example on p 209 of Oracle's free PDF http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html