Web Scripting for Oracle (PHP, Perl, JSP, ASP and ASP.NET)

This article presents the basic syntax for several popular web scripting languages that connect to Oracle databases. All database requests are separated into subroutines and are performed against the SCOTT.EMP table using bind variables. The examples for each language have the same format:

Accept a parameter called DEPTNO from the query string, or default the value if it is omitted.
Connect to the SCOTT schema.
Query records from the EMP table that match the specified DEPTNO.
Insert a new employee and requery the data.
Update an existing employee and requery the data.
Delete an existing employee and requery the data.
Disconnect from the database.

The output from the scripts is not well-formed HTML, as this would increase the size and complexity of each script.

PHP
Perl
Java Server Pages (JSP)
Active Server Pages (ASP)
ASP.NET (VB.NET)
ASP.NET (C#.NET)
Expected Output

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";
  }
?>