API Laravel - 使用 OCI8 或 PDO 调用带有 IN 和 OUT 参数的存储过程
我是 Laravel 8 的新手。
从 Laravel 到 Oracle SQL Developer 的连接:yajra/oci8
我想使用 Laravel API 调用 Oracle 数据库中的存储过程。
我的存储过程有 3 个 IN 参数和 4 个 OUT 参数。
我几乎没有解决方案,但它不起作用。 (在邮递员中测试)
这是我在 API 中的代码: (我的解决方案1和2,基于: https://yajrabox.com/ docs/laravel-oci8/master/stored-procedure)
我的解决方案 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);
}
来自解决方案 1 的错误消息:
“message”:“错误代码:6502\n错误消息:ORA-06502:PL/SQL:数字或值错误:字符串缓冲区太小\nORA-06512:位于“myschema.VERIFY_LOGIN”,第88行\nORA -06502: PL/SQL: 数字或值错误: 字符串缓冲区太小\nORA-06512: 位于行1\n位置 : 0\n语句 : 开始 myschema.VERIFY_LOGIN(:pUserLogin,:pUserPassword,:pIPLogin,:pIsSuccess,:pMsg,:pLastScope,:pParam); end;\n绑定 : [hadi,pass1234,hadi,,,, ]\n", “异常”:“Yajra\Pdo\Oci8\Exceptions\Oci8Exception”,
我的解决方案 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);
}
来自解决方案 2 的错误消息:
"message": "间接修改 Illuminate\Http\Request 的重载元素没有效果", "异常": "错误异常", “文件”:“/opt/lampp/htdocs/Hadi/projectname/app/Http/Controllers/AuthController.php”, “行”:208,
我的解决方案 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);
}
来自解决方案 3 的错误消息:
“message”:“错误代码:6502\n错误消息:ORA-06502:PL/SQL:数字或值错误:字符串缓冲区太小\nORA-06512:位于“myschema.VERIFY_LOGIN”,第89行\nORA -06502: PL/SQL: 数字或值错误: 字符串缓冲区太小\n位置: 5\n语句: 调用 myschema.VERIFY_LOGIN(:pUserLogin, :pUserPassword, :pIPLogin, :pIsSuccess, :pMsg, :pLastScope, :pParam)\n绑定 : [hadi,pass1234*,hadi,,,,]\n (SQL: call myschema. VERIFY_LOGIN(:pUserLogin, :pUserPassword, :pIPLogin、:pIsSuccess、:pMsg、:pLastScope、:pParam))", "exception": "照亮\数据库\QueryException", “文件”:“/opt/lampp/htdocs/Hadi/projectname/vendor/laravel/framework/src/Illuminate/Database/Connection.php”, “行”:712,
请帮我解决这个问题。多谢!
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!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我放弃了 Eloquent,转而采用普通的 PDO 解决方案,该解决方案可以正常工作,不会出现错误。
I had ditch Eloquent and resort to vanilla PDO solution which works without errors.