php - 无法使用 PHP 调用 oracle 存储过程

Question

我正在尝试从 PHP 调用 Oracle 存储过程。我有两个 HTML 文本框。用户在这些文本框中提供用户名和密码。单击按钮后，这些值将传递到 PHP。PHP 应该将这些参数传递给 Oracle 存储过程。两个参数都成功传递给 PHP。但是在运行过程时，它会抛出一个错误。尝试在 SQL 开发人员上运行相同的过程时，它工作正常。

我尝试遵循以下文档 - 从 PHP 调用 Oracle 存储过程

https://www.sitepoint.com/community/t/php-and-oracle-oci8-stored-procedures/7541

甲骨文程序 -

create or replace 
PROCEDURE       proc_create_user (
   p_user_name   IN VARCHAR2,
   p_user_pass   IN VARCHAR2)
AUTHID  DEFINER   
IS
   lv_user_exists   INTEGER := 0;
   lv_user_name     VARCHAR2 (15) := UPPER (trim(p_user_name));
BEGIN
   SELECT COUNT (*)
     INTO lv_user_exists
     FROM dba_users
    WHERE username = lv_user_name;

   IF lv_user_exists = 0
   THEN
      EXECUTE IMMEDIATE
            'CREATE USER '
         || lv_user_name
         || ' IDENTIFIED BY '
         || p_user_pass
         || ' DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK';

      EXECUTE IMMEDIATE 'GRANT CONNECT TO ' || lv_user_name;

      EXECUTE IMMEDIATE 'ALTER USER ' || p_user_name || ' DEFAULT ROLE ALL';
   ELSE
      RAISE_APPLICATION_ERROR (-20000, 'User already exists');
   END IF;
END proc_create_user;

PHP 代码 -

<?php
// required headers
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

require '../vendor/autoload.php';

foreach($_GET as $key=>$value){

}

$uid = $_GET['uid'];
$pass = $_GET['pass'];
$uidupper = strtoupper($_GET['uid']);
$uidupper = "'".$uidupper."'";
$pass = "'".$pass."'";

echo $uidupper;
echo $pass;

$db = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ppe.testdom.com)(PORT=1521)(SEND_BUF_SIZE=)(RECV_BUF_SIZE=))(LOAD_BALANCE=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ppe.testdom.com)))" ;
$conn = OCILogon("test", "test", $db);
$sql = 'BEGIN PROC_CREATE_USER(:p_user_name, :p_user_pass); END;';
$stmt = oci_parse($conn,$sql);                     
oci_bind_by_name($stmt,":p_user_name",$uidupper);           
oci_bind_by_name($stmt,":p_user_pass",$pass);  
echo $sql;
echo $stmt;
oci_execute($stmt);

?>

错误详情 -

'ADB''abcd@1234'BEGIN DWABI.PROC_CREATE_USER(:p_user_name, :p_user_pass); END;Resource id #79<br />
<b>Warning</b>:  oci_execute(): ORA-01935: missing user or role name
ORA-06512: at &quot;DWABI.PROC_CREATE_USER&quot;, line 16
ORA-06512: at line 1 in <b>C:\xampp\htdocs\Log_Check\email_db.php</b> on line <b>48</b><br />

如果有人可以让我知道我做错了什么，那将非常有帮助。

更新： 通过添加双引号而不是单引号解决了该问题。

标签： phporaclestored-procedures