dsgdf45654 2019-06-26 10:57
浏览 72

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

I am trying to call Oracle Stored Procedure from PHP. I am having two textboxes in HTML. Users providing username & password on those textboxes. After the button click, those values getting passed into PHP. PHP should pass those parameters to Oracle Stored Procedure. Both the parameters getting passed to PHP successfully. But while running Procedure, It is throwing an error. While trying to run the same procedure on SQL developer, it is working fine.

I have tried to follow the following documents - Call Oracle stored procedure from PHP

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

Oracle procedure -

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 code -

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

?>

Error Details -

'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 />

It will be very helpful if someone can let me know what I am doing wrong.

UPDATE: The issue has been resolved by adding DOUBLE QUOTES instead of Single Quotes.

  • 写回答

0条回答

    报告相同问题?

    悬赏问题

    • ¥15 求daily translation(DT)偏差订正方法的代码
    • ¥15 js调用html页面需要隐藏某个按钮
    • ¥15 ads仿真结果在圆图上是怎么读数的
    • ¥20 Cotex M3的调试和程序执行方式是什么样的?
    • ¥20 java项目连接sqlserver时报ssl相关错误
    • ¥15 一道python难题3
    • ¥15 牛顿斯科特系数表表示
    • ¥15 arduino 步进电机
    • ¥20 程序进入HardFault_Handler
    • ¥15 关于#python#的问题:自动化测试