API Laravel - 使用 OCI8 或 PDO 调用带有 IN 和 OUT 参数的存储过程

发布于 2025-01-11 01:45:42 字数 4711 浏览 0 评论 0原文

我是 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

落日海湾 2025-01-18 01:45:42

我放弃了 Eloquent,转而采用普通的 PDO 解决方案,该解决方案可以正常工作,不会出现错误。

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

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);
}
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文