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!
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);
}