laravelstored-procedurespdolaravel-8oci8

API Laravel - Call Store Procedure with IN and OUT parameter using OCI8 or PDO


I'm new in Laravel 8.
Connection from Laravel to Oracle SQL Developer: yajra/oci8
I want to call my store procedure in oracle database using API Laravel.
my store procedure have 3 IN parameters and 4 OUT parameters.

I have few solution but it didn't work. (Tested in postman)

Here's my code in API : (my solution 1 and 2, based on : https://yajrabox.com/docs/laravel-oci8/master/stored-procedure)

my solution 1 :

public function checkLogin01(Request $request) {
    $procedureName = 'myschema.VERIFY_LOGIN';

    $pIsSuccess = "";
    $pMsg = "";
    $pLastScope = "";
    $pParam = "";

    $bindings = [
        'pUserLogin' => $request['userLogin'],
        'pUserPassword' => $request['passLogin'],
        'pIPLogin' => $request['userLogin'],
        'pIsSuccess' => $pIsSuccess,
        'pMsg' => $pMsg,
        'pLastScope' => $pLastScope,
        'pParam' => $pParam,
    ];

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

    return response()->json([
        'bindings' => $bindings, 
        'IsSuccess' => $pIsSuccess, 
        'Msg' => $pMsg, 
        'LastScope' => $pLastScope, 
        'Param' => $pParam, 
    ], 422);
}

error message from solution 1 :

"message": "Error Code : 6502\nError Message : ORA-06502: PL/SQL: numeric or value error: character string buffer too small\nORA-06512: at "myschema.VERIFY_LOGIN", line 88\nORA-06502: PL/SQL: numeric or value error: character string buffer too small\nORA-06512: at line 1\nPosition : 0\nStatement : begin myschema.VERIFY_LOGIN(:pUserLogin,:pUserPassword,:pIPLogin,:pIsSuccess,:pMsg,:pLastScope,:pParam); end;\nBindings : [hadi,pass1234,hadi,,,,]\n", "exception": "Yajra\Pdo\Oci8\Exceptions\Oci8Exception",

my Solution 2 :

public function checkLogin02(Request $request) {
    $pdo = DB::getPdo();

    $pIsSuccess = "";
    $pMsg = "";
    $pLastScope = "";
    $pParam = "";

    $procedureName = 'myschema.VERIFY_LOGIN';
    
    $stmt = $pdo->prepare("begin " . $procedureName . " (:pUserLogin, :pUserPassword, :pIPLogin, :pIsSuccess, :pMsg, :pLastScope, :pParam); end;");
    $stmt->bindParam(':pUserLogin', $request['userLogin'], PDO::PARAM_STR); // this is line 208 from error message
    $stmt->bindParam(':pUserPassword', $request['passLogin'], PDO::PARAM_STR);
    $stmt->bindParam(':pIPLogin', $request['userLogin'], PDO::PARAM_STR);
    $stmt->bindParam(':pIsSuccess', $pIsSuccess, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
    $stmt->bindParam(':pMsg', $pMsg, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
    $stmt->bindParam(':pLastScope', $pLastScope, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
    $stmt->bindParam(':pParam', $pParam, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
    
    $stmt->execute();

    return response()->json([
        'bindings' => $bindings, 
        'IsSuccess' => $pIsSuccess, 
        'Msg' => $pMsg, 
        'LastScope' => $pLastScope, 
        'Param' => $pParam, 
    ], 422);
}

error message from solution 2 :

"message": "Indirect modification of overloaded element of Illuminate\Http\Request has no effect", "exception": "ErrorException", "file": "/opt/lampp/htdocs/Hadi/projectname/app/Http/Controllers/AuthController.php", "line": 208,

my solution 3:

public function checkLogin03(Request $request) {
    $pdo = DB::getPdo();

    $pIsSuccess = "";
    $pMsg = "";
    $pLastScope = "";
    $pParam = "";

    $procedureName = 'myschema.VERIFY_LOGIN';
    
    DB::statement("call " . $procedureName . "(:pUserLogin, :pUserPassword, :pIPLogin, :pIsSuccess, :pMsg, :pLastScope, :pParam)" , [
        ':pUserLogin' => $request['userLogin'],
        ':pUserPassword' => $request['passLogin'],
        ':pIPLogin' => $request['userLogin'],
        ':pIsSuccess' => $pIsSuccess,
        ':pMsg' => $pMsg,
        ':pLastScope' => $pLastScope,
        ':pParam' => $pParam
    ]);

    return response()->json([
        'bindings' => $bindings, 
        'IsSuccess' => $pIsSuccess, 
        'Msg' => $pMsg, 
        'LastScope' => $pLastScope, 
        'Param' => $pParam, 
    ], 422);
}

error message from solution 3:

"message": "Error Code : 6502\nError Message : ORA-06502: PL/SQL: numeric or value error: character string buffer too small\nORA-06512: at "myschema.VERIFY_LOGIN", line 89\nORA-06502: PL/SQL: numeric or value error: character string buffer too small\nPosition : 5\nStatement : call myschema.VERIFY_LOGIN(:pUserLogin, :pUserPassword, :pIPLogin, :pIsSuccess, :pMsg, :pLastScope, :pParam)\nBindings : [hadi,pass1234*,hadi,,,,]\n (SQL: call myschema.VERIFY_LOGIN(:pUserLogin, :pUserPassword, :pIPLogin, :pIsSuccess, :pMsg, :pLastScope, :pParam))", "exception": "Illuminate\Database\QueryException", "file": "/opt/lampp/htdocs/Hadi/projectname/vendor/laravel/framework/src/Illuminate/Database/Connection.php", "line": 712,

please help me how to fix it. thanks a lot!


Solution

  • I had ditch Eloquent and resort to vanilla PDO solution which works without errors.

    public function cekLogin(Request $request) {
        $pdo = DB::getPdo();
    
        $pIsSuccess = "";
        $pMsg = "";
        $pLastScope = "";
        $pParam = "";
    
        $procedureName = 'myschema.VERIFY_LOGIN';
        
        $userLogin = $request['userLogin'];
        $passLogin = $request['passLogin'];
        $userLogin = $request['userLogin'];
        
        $stmt = $pdo->prepare("begin " . $procedureName . " (:pUserLogin, :pUserPassword, :pIPLogin, :pIsSuccess, :pMsg, :pLastScope, :pParam); end;");
        $stmt->bindParam(':pUserLogin', $userLogin, PDO::PARAM_STR);
        $stmt->bindParam(':pUserPassword', $passLogin, PDO::PARAM_STR);
        $stmt->bindParam(':pIPLogin', $userLogin, PDO::PARAM_STR);
        $stmt->bindParam(':pIsSuccess', $pIsSuccess, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
        $stmt->bindParam(':pMsg', $pMsg, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
        $stmt->bindParam(':pLastScope', $pLastScope, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
        $stmt->bindParam(':pParam', $pParam, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
        
        $stmt->execute();
    
        return response()->json([
            'IsSuccess' => $pIsSuccess, 
            'Msg' => $pMsg, 
            'LastScope' => $pLastScope, 
            'Param' => $pParam, 
        ], 422);
    }