Oracle PHP Examples

If you’re a regular oracle and PHP develober guy, you must have used mostly PHP and Oracle tools to build and develop your application.

But sometime we need examples to build web application we need. So, I copied this examples for you who want to get Oracle and PHP Development Examples.

Source: http://www.oracle-base.com/articles/misc/WebScriptingForOracle.php

PHP is a widely-used general-purpose scripting language that is especially suited for Web development and can be embedded into HTML. The following example shows how PHP5 can be used to interact with an Oracle database.

<?php
  // Accept a parameter called "deptno" from a form or the query string.
  $deptno  = $_REQUEST['deptno'];

  // Default the value if it is not present.
  if ($deptno == "") {
    $deptno = 10;
  }

  // Connect to the SCOTT schema of the DB10G database.
  $conn=oci_connect("scott", "tiger", "DB10G");
  if ( ! $conn ) {
    echo "Unable to connect: " . var_dump( oci_error() );
    die();
  }
  else {
    echo "Connected sucessfully.<br /><br />\n";
  }

  example_query($conn, $deptno);

  example_insert($conn, $deptno);
  example_query($conn, $deptno);

  example_update($conn);
  example_query($conn, $deptno);

  example_delete($conn);
  example_query($conn, $deptno);

  oci_close($conn);
  echo "<br />Disconnected sucessfully.<br /><br />\n";

  function example_query ($conn, $deptno) {
    echo "Return employees for department $deptno.<br />\n";

    // Parse a query containing a bind variable.
    $stmt = oci_parse($conn, "SELECT * ".
                             "FROM   emp ".
                             "WHERE  deptno = :deptno ".
                             "ORDER BY empno");

    // Bind the value into the parsed statement.
    oci_bind_by_name($stmt, ":deptno", $deptno);

    // Execute the completed statement.
    oci_execute($stmt, OCI_DEFAULT);

    while (oci_fetch($stmt)) {
    	$empno    = oci_result($stmt, "EMPNO");
    	$ename    = oci_result($stmt, "ENAME");
    	$job      = oci_result($stmt, "JOB");
    	$mgr      = oci_result($stmt, "MGR");
    	$hiredate = oci_result($stmt, "HIREDATE");
    	$sal      = oci_result($stmt, "SAL");
    	$comm     = oci_result($stmt, "COMM");
    	$deptno   = oci_result($stmt, "DEPTNO");

    	// Do something with the data
    	echo "empno=$empno ";
    	echo "ename=$ename ";
    	echo "job=$job ";
    	echo "mgr=$mgr ";
    	echo "hiredate=$hiredate ";
    	echo "sal=$sal ";
    	echo "comm=$comm ";
    	echo "deptno=$deptno<br />\n";
    }
    oci_free_statement($stmt);
  }

  function example_insert ($conn, $deptno) {
    echo "<br />Insert a new employee.<br />\n";

    // Parse an insert statement containing bind variables.
    $stmt = oci_parse($conn, "INSERT INTO emp (empno, ename, job, deptno) ".
                             "VALUES (:empno, :ename, :job, :deptno)");

    $empno  = 9999;
    $ename  = "HALL";
    $job    = "DBA";

    // Bind the values into the parsed statement.
    oci_bind_by_name($stmt, ":empno", $empno);
    oci_bind_by_name($stmt, ":ename", $ename);
    oci_bind_by_name($stmt, ":job", $job);
    oci_bind_by_name($stmt, ":deptno", $deptno);

    // Execute the completed statement.
    oci_execute($stmt, OCI_DEFAULT);
    oci_commit($conn);
    oci_free_statement($stmt);
    echo "Employee inserted sucessfully.<br />\n";
  }

  function example_update ($conn) {
    echo "<br />Update an existing employee.<br />\n";

    // Parse an update statement containing bind variables.
    $stmt = oci_parse($conn, "UPDATE emp ".
                             "SET    ename = :ename, ".
                             "       job   = :job ".
                             "WHERE  empno = :empno");

    $empno = 9999;
    $ename = "TIM_HALL";
    $job   = "DBA/DEV";

    // Bind the values into the parsed statement.
    oci_bind_by_name($stmt, ":empno", $empno);
    oci_bind_by_name($stmt, ":ename", $ename);
    oci_bind_by_name($stmt, ":job", $job);

    // Execute the completed statement.
    oci_execute($stmt, OCI_DEFAULT);
    oci_commit($conn);
    oci_free_statement($stmt);
    echo "Employee updated sucessfully.<br />\n";
  }

  function example_delete ($conn) {
    echo "<br />Delete an existing employee.<br />\n";

    // Parse a delete statement containing bind variables.
    $stmt = oci_parse($conn, "DELETE FROM emp ".
                             "WHERE  empno = :empno");

    $empno = 9999;

    // Bind the values into the parsed statement.
    oci_bind_by_name($stmt, ":empno", $empno);

    // Execute the completed statement.
    oci_execute($stmt, OCI_DEFAULT);
    oci_commit($conn);
    oci_free_statement($stmt);
    echo "Employee deleted sucessfully.<br />\n";
  }
?>

The OCI function names were revised in PHP5 to make them more consistent. If you are using an older version, you will need to make the following alterations.

PHP4	PHP5
OCILogon	oci_connect
OCIParse	oci_parse
OCIBindByName	oci_bind_by_name
OCIFetch	oci_fetch
OCIExecute	oci_execute
OCICommit	oci_commit
OCIFreeStatement	oci_free_statemant
OCICommit	oci_commit
OCILogoff	oci_close
OCIError	oci_error

In addition to the OCI method, PHP5 also includes a new, and as yet unfinished, PHP Data Objects (PDO) interface for accessing the Oracle database. The following example was correct at the time of writing, but the specification and drivers are still under development.

<?php
  // Accept a parameter called "deptno" from a form or the query string.
  $deptno  = $_REQUEST['deptno'];

  // Default the value if it is not present.
  if ($deptno == "") {
    $deptno = 10;
  }

  // Connect to the SCOTT schema of the DB10G database.
  try {
    $conn = new PDO("oci:dbname=DB10G", "scott", "tiger");
    echo "Connected sucessfully.<br /><br />\n";
  } catch (PDOException $e) {
    echo "Unable to connect: " . $e->getMessage();
    die();
  }

  example_query($conn, $deptno);

  example_insert($conn, $deptno);
  example_query($conn, $deptno);

  example_update($conn);
  example_query($conn, $deptno);

  example_delete($conn);
  example_query($conn, $deptno);

  $conn = null;
  echo "<br />Disconnected sucessfully.<br /><br />\n";

  function example_query ($conn, $deptno) {
    echo "Return employees for department $deptno.<br />\n";

    // Parse a query containing a bind variable.
    $stmt = $conn->prepare("SELECT * ".
                           "FROM   emp ".
                           "WHERE  deptno = :deptno ".
                           "ORDER BY empno");

    // Bind the value into the parsed statement.
    $stmt->bindParam(":deptno", $deptno, PDO::PARAM_INT);

    // Execute the completed statement.
    if ($stmt->execute()) {
      while ($row = $stmt->fetch()) {
        $empno    = $row["EMPNO"];
      	$ename    = $row["ENAME"];
      	$job      = $row["JOB"];
      	$mgr      = $row["MGR"];
      	$hiredate = $row["HIREDATE"];
      	$sal      = $row["SAL"];
      	$comm     = $row["COMM"];
      	$deptno   = $row["DEPTNO"];

      	// Do something with the data
      	echo "empno=$empno ";
      	echo "ename=$ename ";
      	echo "job=$job ";
      	echo "mgr=$mgr ";
      	echo "hiredate=$hiredate ";
      	echo "sal=$sal ";
      	echo "comm=$comm ";
      	echo "deptno=$deptno<br />\n";
      }
    }
    $stmt = null;
  }

  function example_insert ($conn, $deptno) {
    echo "<br />Insert a new employee.<br />\n";

    // Parse an insert statement containing bind variables.
    $stmt = $conn->prepare("INSERT INTO emp (empno, ename, job, deptno) ".
                           "VALUES (:empno, :ename, :job, :deptno)");

    $empno  = 9999;
    $ename  = "HALL";
    $job    = "DBA";

    // Bind the values into the parsed statement.
    $stmt->bindParam(":empno", $empno, PDO::PARAM_INT);
    $stmt->bindParam(":ename", $ename, PDO::PARAM_STR);
    $stmt->bindParam(":job", $job, PDO::PARAM_STR);
    $stmt->bindParam(":deptno", $deptno, PDO::PARAM_INT);

    // Execute the completed statement.
    $stmt->execute();

    $stmt = null;
    echo "Employee inserted sucessfully.<br />\n";
  }

  function example_update ($conn) {
    echo "<br />Update an existing employee.<br />\n";

    // Parse an update statement containing bind variables.
    $stmt = $conn->prepare("UPDATE emp ".
                           "SET    ename = :ename, ".
                           "       job   = :job ".
                           "WHERE  empno = :empno");

    $empno = 9999;
    $ename = "TIM_HALL";
    $job   = "DBA/DEV";

    // Bind the values into the parsed statement.
    $stmt->bindParam(":empno", $empno, PDO::PARAM_INT);
    $stmt->bindParam(":ename", $ename, PDO::PARAM_STR);
    $stmt->bindParam(":job", $job, PDO::PARAM_STR);

    // Execute the completed statement.
    $stmt->execute();

    $stmt = null;
    echo "Employee updated sucessfully.<br />\n";
  }

  function example_delete ($conn) {
    echo "<br />Delete an existing employee.<br />\n";

    // Parse a delete statement containing bind variables.
    $stmt = $conn->prepare("DELETE FROM emp ".
                           "WHERE  empno = :empno");

    $empno = 9999;

    // Bind the values into the parsed statement.
    $stmt->bindParam(":empno", $empno, PDO::PARAM_INT);

    // Execute the completed statement.
    $stmt->execute();

    $stmt = null;
    echo "Employee deleted sucessfully.<br />\n";
  }
?>

The following articles may help you get started: 

• How PHP Connect to Oracle: Oracle Connection String
• Oracle PHP Select, Insert, Update and Delete Query