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 "DWABI.PROC_CREATE_USER", 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.