
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 44.211.239.1
💻💽💾 Useful examples and tools for home and professional programming.
© Petr Faltus - 2021