PHP connecting to Oracle database

Petr Faltus development

Example source code in PHP how to connect to the Oracle database, how to update rows, how to read the table and how to call the storage function and the storage procedure.

oracle-client.php

GitHub repository: oracle-database-connection-source-codes

git clone https://github.com/petrfaltus/oracle-database-connection-source-codes.git

Directories to oracle-client.php in the repository: php

<?php

/*
1) Install 'Microsoft Visual C++ Redistributable for Visual Studio 2015, 2017 and 2019' and 'Oracle Instant Client'

2) From Instant client to the PHP directory copied:
oci.dll
oraociei19.dll
oraons.dll

3) In the php.ini added lines:
[PHP]
extension_dir = "ext"
extension=pdo_oci
[Date]
date.timezone = Europe/Prague

*/

$db_driver = "oci";

$db_host = "localhost";
$db_port = 1521;
$db_srvname = "ORCLCDB.localdomain";
$db_username = "testuser";
$db_password = "T3stUs3r!";
$db_table = "cars";

$db_update_column = "remark";
$db_update_column_variable = ":updatevar";

$db_column = "id";
$db_column_variable = ":var";
$db_column_value = 1;

$db_factorial_variable = ":n";
$db_factorial_value = 4;

$db_add_and_subtract_a_variable = ":a";
$db_add_and_subtract_a_value = 12;
$db_add_and_subtract_b_variable = ":b";
$db_add_and_subtract_b_value = 5;

$db_add_and_subtract_x_variable = ":x";
$db_add_and_subtract_x_value = -1;
$db_add_and_subtract_y_variable = ":y";
$db_add_and_subtract_y_value = -1;

$availableDrivers = PDO::getAvailableDrivers();

echo "Available PDO drivers ";
print_r($availableDrivers);
echo PHP_EOL;

if (!in_array($db_driver, $availableDrivers))
  {
   echo "PDO driver ".$db_driver." or it's subdriver is not available or has not been enabled in php.ini".PHP_EOL;
   exit;
  }

try
  {
   // Build the connection string and connect the database
   $dsn = $db_driver.":dbname=//".$db_host.":".$db_port."/".$db_srvname;
   $conn = new PDO($dsn, $db_username, $db_password);

   echo "ATTR_CLIENT_VERSION = ".$conn->getAttribute(PDO::ATTR_CLIENT_VERSION).PHP_EOL;
   echo "ATTR_DRIVER_NAME = ".$conn->getAttribute(PDO::ATTR_DRIVER_NAME).PHP_EOL;
   echo "ATTR_SERVER_INFO = ".$conn->getAttribute(PDO::ATTR_SERVER_INFO).PHP_EOL;
   echo "ATTR_SERVER_VERSION = ".$conn->getAttribute(PDO::ATTR_SERVER_VERSION).PHP_EOL;
   echo PHP_EOL;

   // UPDATE statement
   $new_comment = "PHP ".date("j.n.Y H:i:s");

   $stm0query = "update ".$db_table." set ".$db_update_column."=".$db_update_column_variable." where ".$db_column."!=".$db_column_variable;
   echo $stm0query.PHP_EOL;

   $stm0 = $conn->prepare($stm0query);
   $stm0->bindParam($db_update_column_variable, $new_comment, PDO::PARAM_STR);
   $stm0->bindParam($db_column_variable, $db_column_value, PDO::PARAM_INT);
   $stm0->execute();
   echo "Total updated rows: ".$stm0->rowCount().PHP_EOL;
   echo PHP_EOL;

   $stm0 = null;

   // Full SELECT statement
   $stm1query = "select * from ".$db_table;
   echo $stm1query.PHP_EOL;

   $stm1 = $conn->prepare($stm1query);
   $stm1->execute();
   echo "Total columns: ".$stm1->columnCount().PHP_EOL;

   echo "Fetch all rows ";
   $lines1 = $stm1->fetchAll(PDO::FETCH_ASSOC);
   if ($lines1 == false)
     print_r($stm1->errorInfo());
   else
     print_r($lines1);
   echo PHP_EOL;

   $stm1 = null;

   // SELECT WHERE statement
   $stm2query = "select count(*) from ".$db_table." where ".$db_column."!=".$db_column_variable;
   echo $stm2query.PHP_EOL;

   $stm2 = $conn->prepare($stm2query);
   $stm2->bindParam($db_column_variable, $db_column_value, PDO::PARAM_INT);
   $stm2->execute();
   echo "Total columns: ".$stm2->columnCount().PHP_EOL;

   echo "Fetch all rows ";
   $lines2 = $stm2->fetchAll(PDO::FETCH_ASSOC);
   if ($lines2 == false)
     print_r($stm2->errorInfo());
   else
     print_r($lines2);
   echo PHP_EOL;

   $stm2 = null;

   // SELECT package function statement
   $stm3query = "select calculator.factorial(".$db_factorial_variable.") from dual";
   echo $stm3query.PHP_EOL;

   $stm3 = $conn->prepare($stm3query);
   $stm3->bindParam($db_factorial_variable, $db_factorial_value, PDO::PARAM_INT);
   $stm3->execute();
   echo "Total columns: ".$stm3->columnCount().PHP_EOL;

   echo "Fetch all rows ";
   $lines3 = $stm3->fetchAll(PDO::FETCH_ASSOC);
   if ($lines3 == false)
     print_r($stm3->errorInfo());
   else
     print_r($lines3);
   echo PHP_EOL;

   $stm3 = null;

   // CALL package procedure statement
   $stm4query = "call calculator.add_and_subtract(".$db_add_and_subtract_a_variable.", ".$db_add_and_subtract_b_variable.", ".$db_add_and_subtract_x_variable.", ".$db_add_and_subtract_y_variable.")";
   echo $stm4query.PHP_EOL;

   $stm4 = $conn->prepare($stm4query);
   $stm4->bindParam($db_add_and_subtract_a_variable, $db_add_and_subtract_a_value, PDO::PARAM_INT);
   $stm4->bindParam($db_add_and_subtract_b_variable, $db_add_and_subtract_b_value, PDO::PARAM_INT);
   $stm4->bindParam($db_add_and_subtract_x_variable, $db_add_and_subtract_x_value, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, 20);
   $stm4->bindParam($db_add_and_subtract_y_variable, $db_add_and_subtract_y_value, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, 20);
   $stm4->execute();

   $stm4error = $stm4->errorInfo();
   if ((isset($stm4error[0])) and ($stm4error[0] === "00000"))
     {
      echo "X: ".$db_add_and_subtract_x_value.PHP_EOL;
      echo "Y: ".$db_add_and_subtract_y_value.PHP_EOL;
     }
   else
     print_r($stm4error);

   $stm4 = null;

   // Disconnect the database
   $conn = null;
  }
catch (PDOException $e)
  {
   echo $e->getMessage().PHP_EOL;
  }

?>

Previous example

All examples

Following example

🤝 Your IP address is 3.238.98.39

💻💽💾 Useful examples and tools for home and professional programming.