bonez bonez - 1 month ago 8
PHP Question

how to run oracle stored procedure in php

I have tried the following

class DatabaseConnection {


private $con;
private $credentials;
protected $statement;
private $dbName;
// parsedSQL is not intended to used by anything except logging in the case of failed execution.
public $parsedSql;
protected $mode = OCI_COMMIT_ON_SUCCESS;
const OCI_RETURN_DB_POINTER = 1001;
const OCI_RETURN_DB_PERSISTENT = 1002;
const OCI_RETURN_DB_UNIQUE = 1003;

public function __construct($dbName, $connectType = DatabaseConnection::OCI_RETURN_DB_POINTER) {
$this->credentials = array (
"slrukdb" => array (
"username" => "OBLICORE",
"pw" => "xxx",
"connstr" => "OIUKDB.prod.uk"
),
);
$dbName = strtolower ( $dbName );
$this->dbName = $dbName;
switch ($connectType) {
case DatabaseConnection::OCI_RETURN_DB_POINTER :
$this->con = oci_connect ( $this->credentials [$dbName] ['username'], $this->credentials [$dbName] ['pw'], $this->credentials [$dbName] ['connstr'] );
break;
case DatabaseConnection::OCI_RETURN_DB_PERSISTENT :
$this->con = oci_pconnect ( $this->credentials [$dbName] ['username'], $this->credentials [$dbName] ['pw'], $this->credentials [$dbName] ['connstr'] );
break;
case DatabaseConnection::OCI_RETURN_DB_UNIQUE :
$this->con = oci_new_connect ( $this->credentials [$dbName] ['username'], $this->credentials [$dbName] ['pw'], $this->credentials [$dbName] ['connstr'] );
break;
default:
echo "No Connection";
die();
break;
}

if ($this->dbName == 'slrukdb') {
$dbt = debug_backtrace ();
$location = substr ( strrchr ( $dbt [0] ["file"], "/" ), 1 ) . " (" . $dbt [0] ["line"] . ")";
oci_set_client_identifier ( $this->con, $location );
}
}

public function execute($sql = null) {
if ($this->dbName == 'slrukdb') {
$dbt = debug_backtrace ();
$location = substr ( strrchr ( $dbt [0] ["file"], "/" ), 1 ) . " (" . $dbt [0] ["line"] . ")";
//print_r($this->con);
//print_r($location);
oci_set_client_identifier ( $this->con, $location );
}
if (! is_null ( $sql )) {
$ocistatement = oci_parse ( $this->con, $sql );
} else {
$ocistatement = $this->statement;
}

$success = oci_execute ( $ocistatement, $this->mode );
if (! $success) {
if ($this->mode != OCI_COMMIT_ON_SUCCESS) {
$this->transactionRollback ();

$exceptionSql = (is_null ( $sql )) ? $this->parsedSql : $sql;

throw new Exception ( "Statement execution failed. Transaction rolled back. SQL: $exceptionSql" );
} else {
$message = "";
$oci_error = oci_error ();
if (! empty ( $oci_error )) {
$message = "OCI_ERROR: " . $oci_error ["code"] . ": " . $oci_error ["message"];
if (! empty ( $oci_error ["sqltext"] )) {
$message .= " in SQL{" . $oci_error ["sqltext"] . "}";
}
} else {
$message = "unknown error in DatabaseConnection->execute";

if (! empty ( $this->parsedSql )) {
$message .= " with parsed SQL{" . $this->parsedSql . "}";
} else if (! empty ( $sql )) {
$message .= " with SQL{" . $sql . "}";
}
}

trigger_error ( $message, E_USER_ERROR );
}
}

return $ocistatement;
}
}

$db = new DatabaseConnection ( 'slrukdb' );
$db->execute ( "P_B_NMS_Interface_Latency" );


I get error

PHP Warning: oci_execute(): ORA-00900: invalid SQL statement in /var/SP/oiadm/docroot/common/php/DatabaseConnection.php on line 170
PHP Fatal error: unknown error in DatabaseConnection->execute with SQL{P_B_NMS_Interface_Latency} in /var/SP/oiadm/docroot/common/php/DatabaseConnection.php on line 196


procedure

create or replace PROCEDURE P_B_NMS_Interface_Latency as

BEGIN

insert into B_NMS_Interface_Latency values (sysdate,'A','IMSI',(Select max(timestamp) from B_NMS_A_Interface where IMSI is not null and IMSI >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'A','Cell',(Select max(timestamp) from B_NMS_A_Interface where Cell is not null and Cell >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'A','IMEI',(Select max(timestamp) from B_NMS_A_Interface where IMEI is not null and IMEI >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'A','PNUM',(Select max(timestamp) from B_NMS_A_Interface where PNUM is not null and PNUM >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'IuCS','IMSI',(Select max(timestamp) from B_NMS_IuCS_Interface where IMSI is not null and IMSI >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'IuCS','Cell',(Select max(timestamp) from B_NMS_IuCS_Interface where Cell is not null and Cell >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'IuCS','IMEI',(Select max(timestamp) from B_NMS_IuCS_Interface where IMEI is not null and IMEI >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'IuCS','PNUM',(Select max(timestamp) from B_NMS_IuCS_Interface where PNUM is not null and PNUM >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'Gb','IMSI',(Select max(timestamp) from B_NMS_Gb_Interface where IMSI is not null and IMSI >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'Gb','Cell',(Select max(timestamp) from B_NMS_Gb_Interface where Cell is not null and Cell >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'Gb','IMEI',(Select max(timestamp) from B_NMS_Gb_Interface where IMEI is not null and IMEI >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'Gb','APN',(Select max(timestamp) from B_NMS_Gb_Interface where APN is not null and APN >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'IuPS','IMSI',(Select max(timestamp) from B_NMS_IuPS_Interface where IMSI is not null and IMSI >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'IuPS','Cell',(Select max(timestamp) from B_NMS_IuPS_Interface where Cell is not null and Cell >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'IuPS','IMEI',(Select max(timestamp) from B_NMS_IuPS_Interface where IMEI is not null and IMEI >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'IuPS','APN',(Select max(timestamp) from B_NMS_IuPS_Interface where APN is not null and APN >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'GN2G','IMSI',(Select max(timestamp) from B_NMS_GN2G_Interface where IMSI is not null and IMSI >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'GN2G','IMEI',(Select max(timestamp) from B_NMS_GN2G_Interface where IMEI is not null and IMEI >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'GN2G','APN',(Select max(timestamp) from B_NMS_GN2G_Interface where APN is not null and APN >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'GN2G','PURL',(Select max(timestamp) from B_NMS_GN2G_Interface where PURL is not null and PURL >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'GN2G','DOM',(Select max(timestamp) from B_NMS_GN2G_Interface where DOM is not null and DOM >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'GN3G','IMSI',(Select max(timestamp) from B_NMS_GN3G_Interface where IMSI is not null and IMSI >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'GN3G','IMEI',(Select max(timestamp) from B_NMS_GN3G_Interface where IMEI is not null and IMEI >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'GN3G','APN',(Select max(timestamp) from B_NMS_GN3G_Interface where APN is not null and APN >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'GN3G','PURL',(Select max(timestamp) from B_NMS_GN3G_Interface where PURL is not null and PURL >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'GN3G','DOM',(Select max(timestamp) from B_NMS_GN3G_Interface where DOM is not null and DOM >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'AllVoice','IMSI',(Select max(timestamp) from B_NMS_AllVoice_Interface where IMSI is not null and IMSI >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'AllVoice','IMEI',(Select max(timestamp) from B_NMS_AllVoice_Interface where IMEI is not null and IMEI >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'AllVoice','PNUM',(Select max(timestamp) from B_NMS_AllVoice_Interface where PNUM is not null and PNUM >= 1000));

insert into B_NMS_Interface_Latency values (sysdate,'BO','webi',(Select max(timestamp) from B_NMS_BOTimestamps where Type = 'webi'));

insert into B_NMS_Interface_Latency values (sysdate,'BO','deski',(Select max(timestamp) from B_NMS_BOTimestamps where Type = 'deski'));

commit;

END P_B_NMS_Interface_Latency;


UPDATE

when i tried

$db->execute ( "call P_B_NMS_Interface_Latency()" );


error:

PHP Warning: oci_execute(): ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "OBLICORE.P_B_NMS_INTERFACE_LATENCY", line 5 in /var/SP/oiadm/docroot/common/php/DatabaseConnection.php on line 170
PHP Fatal error: unknown error in DatabaseConnection->execute with SQL{call P_B_NMS_Interface_Latency()} in /var/SP/oiadm/docroot/common/php/DatabaseConnection.php on line 196

Answer

You call a procedure inside a BEGIN..END anonymous block like this

$sql = 'BEGIN P_B_NMS_Interface_Latency(); END;';

$stmt = oci_parse($conn,$sql);

oci_execute($stmt);
Comments