Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

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:

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

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 sites and articles may help you get started.

Perl

Perl is a stable, cross platform programming language. It is used for mission critical projects in the public and private sectors and is widely used to program web applications of all needs. The following example shows how it can be used to interact with an Oracle database.

#!/u01/app/oracle/product/10.1.0/db_1/perl/bin/perl -w

print "Content-type: text/html", "\n\n";

use strict;
use DBI;
use CGI;
my $query = new CGI;

# Accept a parameter called "deptno" from a form or the query string.
my $deptno  = $query->param('deptno');

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

# Connect to the SCOTT schema of the DB10G database.
my $conn = DBI->connect('dbi:Oracle:DB10G',
                        'scott',
                        'tiger',
                        { RaiseError => 1, AutoCommit => 0 }
                       ) || die "Database connection not made: $DBI::errstr";

print "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);

$conn->disconnect;
print "<br />Disconnected sucessfully.<br /><br />\n";


sub example_query {
  my($conn, $deptno) = @_;

  print "Return employees for department $deptno.<br />\n";

  # Prepare a query containing a bind variable.
  my $sql = qq{ SELECT *
                FROM   emp
                WHERE  deptno = ?
                ORDER BY empno };

  my $stmt = $conn->prepare($sql);

  # Bind the value into the prepared statement.
  $stmt->bind_param( 1, $deptno );

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

  # Define variable to hold the data and bind them to the statement.
  my( $empno, $ename, $job, $mgr, $hiredate, $sal, $comm);
  $stmt->bind_columns( undef, $empno, $ename, $job, $mgr, $hiredate, $sal, $comm, $deptno );

  while( $stmt->fetch() ) {
    # Handle potential NULLs. Only needed is using strict.
    if (!defined $empno) { $empno = ""; }
    if (!defined $ename) { $ename = ""; }
    if (!defined $job) { $job = ""; }
    if (!defined $mgr) { $mgr = ""; }
    if (!defined $hiredate) { $hiredate = ""; }
    if (!defined $sal) { $sal = ""; }
    if (!defined $comm) { $comm = ""; }
    if (!defined $deptno) { $deptno = ""; }

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


sub example_insert {
  my($conn, $deptno) = @_;

  print "<br />Insert a new employee.<br />\n";

  # Prepare an insert statement containing bind variables.
  my $sql = qq{ INSERT INTO emp (empno, ename, job, deptno)
                VALUES (?, ?, ?, ?) };

  my $stmt   = $conn->prepare($sql);

  # Bind the value into the prepared statement.
  my $empno  = 9999;
  my $ename  = "HALL";
  my $job    = "DBA";
  $stmt->bind_param( 1, $empno );
  $stmt->bind_param( 2, $ename );
  $stmt->bind_param( 3, $job );
  $stmt->bind_param( 4, $deptno );

  # Execute the completed statement.
  $stmt->execute();
  $conn->commit();
  $stmt->finish();
  print "Employee inserted sucessfully.<br />\n";
}


sub example_update {
  my ($conn) = @_;

  print "<br />Update an existing employee.<br />\n";

  # Prepare an update statement containing bind variables.
  my $sql = qq{ UPDATE emp
                SET    ename = ?,
                       job   = ?
                WHERE  empno = ? };

  my $stmt   = $conn->prepare($sql);

  # Bind the value into the prepared statement.
  my $empno  = 9999;
  my $ename  = "TIM_HALL";
  my $job    = "DBA/DEV";
  $stmt->bind_param( 1, $ename );
  $stmt->bind_param( 2, $job );
  $stmt->bind_param( 3, $empno );

  # Execute the completed statement.
  $stmt->execute();
  $conn->commit();
  $stmt->finish();
  print "Employee updated sucessfully.<br />\n";
}


sub example_delete {
  my ($conn) = @_;

  print "<br />Delete an existing employee.<br />\n";

  # Prepare a delete statement containing bind variables.
  my $sql = qq{ DELETE FROM emp
                WHERE  empno = ? };

  my $stmt   = $conn->prepare($sql);

  # Bind the value into the prepared statement.
  my $empno  = 9999;
  $stmt->bind_param( 1, $empno );

  # Execute the completed statement.
  $stmt->execute();
  $conn->commit();
  $stmt->finish();
  print "Employee deleted sucessfully.<br />\n";
}

The following sites and articles may help you get started.

Java Server Pages (JSP)

Java Server Pages (JSP) technology provides a simplified, fast way to create dynamic web content. JSP technology enables rapid development of web-based applications that are server- and platform-independent. The following example shows how it can be used to interact with an Oracle database.

<%@ page language="Java" import="java.sql.*" %>
<%
  // Define a class holding the functions we will use in the main body of the code.
  class PageFunctions extends Object {
    public void example_query (Connection conn, String deptno, javax.servlet.jsp.JspWriter out) throws Exception {
      out.println("Return employees for department " + deptno + ".<br />\n");

      // Prepare a query containing a bind variable.
      String sql = "SELECT * FROM emp WHERE deptno = ? ORDER BY empno";
      PreparedStatement stmt = conn.prepareStatement(sql);

      // Bind the value into the prepared statement.
      stmt.setInt(1, new Integer(deptno).intValue());

      // Execute the completed statement.
      ResultSet rs  = stmt.executeQuery();

      while (rs.next()) {
    	// Do something with the data
      	out.println("empno=" + rs.getString("empno"));
      	out.println(" ename=" + rs.getString("ename"));
      	out.println(" job=" + rs.getString("job"));
      	out.println(" mgr=" + rs.getString("mgr"));
      	out.println(" hiredate=" + rs.getString("hiredate"));
      	out.println(" sal=" + rs.getString("sal"));
      	out.println(" comm=" + rs.getString("comm"));
      	out.println(" deptno=" + rs.getString("deptno") + "<br />\n");
      }

      stmt.close();
      stmt = null;
    }

    public void example_insert (Connection conn, String deptno, javax.servlet.jsp.JspWriter out) throws Exception {
      out.println("<br />Insert a new employee.<br />\n");

      // Prepare an insert statement containing bind variables.
      String sql = "INSERT INTO emp (empno, ename, job, deptno) VALUES (?, ?, ?, ?)";
      PreparedStatement stmt = conn.prepareStatement(sql);

      // Bind the value into the prepared statement.
      int    empno = 9999;
      String ename = "HALL";
      String job   = "DBA";
      stmt.setInt(1, empno);
      stmt.setString(2, ename);
      stmt.setString(3, job);
      stmt.setInt(4, new Integer(deptno).intValue());

      // Execute the completed statement.
      int res = stmt.executeUpdate();

      stmt.close();
      stmt = null;

      out.println("Employee inserted sucessfully.<br />\n");
    }

    public void example_update (Connection conn, javax.servlet.jsp.JspWriter out) throws Exception {
      out.println("<br />Update an existing employee.<br />\n");

      // Prepare an update statement containing bind variables.
      String sql = "UPDATE emp SET ename = ?, job = ? WHERE empno = ?";
      PreparedStatement stmt = conn.prepareStatement(sql);

      // Bind the value into the prepared statement.
      int    empno = 9999;
      String ename = "TIM_HALL";
      String job   = "DBA/DEV";
      stmt.setString(1, ename);
      stmt.setString(2, job);
      stmt.setInt(3, empno);

      // Execute the completed statement.
      int res = stmt.executeUpdate();

      stmt.close();
      stmt = null;

      out.println("Employee updated sucessfully.<br />\n");
    }

    public void example_delete (Connection conn, javax.servlet.jsp.JspWriter out) throws Exception {
      out.println("<br />Delete an existing employee.<br />\n");

      // Prepare a delete statement containing bind variables.
      String sql = "DELETE FROM emp WHERE  empno = ?";
      PreparedStatement stmt = conn.prepareStatement(sql);

      // Bind the value into the prepared statement.
      int empno = 9999;
      stmt.setInt(1, empno);

      // Execute the completed statement.
      int res = stmt.executeUpdate();

      stmt.close();
      stmt = null;

      out.println("Employee deleted sucessfully.<br />\n");
    }
  }

  // Start the main body of the code.
  try {
    // Instantiate the functions class.
    PageFunctions funcs = new PageFunctions();

    // Accept a parameter called "deptno" from a form or the query string.
    String deptno = request.getParameter("deptno");

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

    // Connect to the SCOTT schema of the DB10G database.
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@DB10G", "SCOTT", "TIGER");

    out.println("Connected sucessfully.<br /><br />\n");

    funcs.example_query(conn, deptno, out);

    funcs.example_insert(conn, deptno, out);
    funcs.example_query(conn, deptno, out);

    funcs.example_update(conn, out);
    funcs.example_query(conn, deptno, out);

    funcs.example_delete(conn, out);
    funcs.example_query(conn, deptno, out);

    conn.close();
    conn = null;

    out.println("<br />Disconnected sucessfully.<br /><br />\n");
  } catch (Exception ex) {
    out.println("    Error: " + ex.getLocalizedMessage() + "<br><br>\n");
  }
%>

The following sites and articles may help you get started.

Active Server Pages (ASP)

ASP is a Microsoft scripting langauge that has been superseded by ASP.NET. Despite its age, it is popular due to the number of sites that were coded using it prior to the release of ASP.NET. The following example shows how it can be used to interact with an Oracle database.

<%
  option explicit

  const adParamInput = 1
  const adOutput = 2
  const adVarChar = 200
  const adInteger = 3
  const adStateOpen = 1
  const adUseClient = 3
  const adOpenStatic = 3
  const adCmdStoredProc = 4
  const adCmdText = 1

  Dim conn, deptno

  ' Accept a parameter called "deptno" from a form or the query string.
  deptno = Request.QueryString("deptno")

  ' Default the value if it is not present.
  If deptno = "" Then
    deptno = "10"
  End If

  ' Connect to the SCOTT schema of the DB10G database.
  Set conn = Server.CreateObject("adodb.connection")
  conn.Open "DSN=DB10G;UID=scott;PWD=tiger"

  Response.Write "Connected sucessfully.<br /><br />" & vbCrLf

  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.Close
  Set conn   = nothing

  Response.Write "<br />Disconnected sucessfully.<br /><br />" & vbCrLf


  Sub example_query (conn, deptno)
    Dim cmd, rs

    Response.Write "Return employees for department " & deptno & ".<br />" & vbCrLf

    Set cmd = Server.CreateObject ("ADODB.Command")
    Set cmd.ActiveConnection = conn
    cmd.CommandText = "SELECT * FROM emp WHERE deptno = ? ORDER BY empno"
    cmd.CommandType = adCmdText

    ' name, type, direction, size, value
    cmd.Parameters.Append cmd.CreateParameter ("deptno", adInteger, adParamInput, , CInt(deptno))

    Set rs = cmd.Execute

    Do Until rs.BOF Or rs.EOF
      ' Do something
      Response.Write "empno=" & rs("empno")
      Response.Write " ename=" & rs("ename")
      Response.Write " job=" & rs("job")
      Response.Write " mgr=" & rs("mgr")
      Response.Write " hiredate=" & rs("hiredate")
      Response.Write " sal=" & rs("sal")
      Response.Write " comm=" & rs("comm")
      Response.Write " deptno=" & rs("deptno") & "<br />" & vbCrLf
      rs.MoveNext
    Loop

    rs.Close
    Set rs        = nothing
    Set cmd       = nothing
  End Sub


  Sub example_insert (conn, deptno)
    Dim cmd, sql, empno, ename, job

    Response.Write "<br />Insert a new employee.<br />" & vbCrLf

    ' Prepare an insert statement containing bind variables.
    sql = "INSERT INTO emp (empno, ename, job, deptno) VALUES (?, ?, ?, ?)"
    Set cmd = Server.CreateObject ("ADODB.Command")
    Set cmd.ActiveConnection = conn
    cmd.CommandText = sql
    cmd.CommandType = adCmdText

    ' Bind the value into the prepared statement.
    empno = 9999
    ename = "HALL"
    job   = "DBA"

    ' name, type, direction, size, value
    cmd.Parameters.Append cmd.CreateParameter ("empno", adInteger, adParamInput, , empno)
    cmd.Parameters.Append cmd.CreateParameter ("ename", adVarChar, adParamInput, Len(ename), ename)
    cmd.Parameters.Append cmd.CreateParameter ("job", adVarChar, adParamInput, Len(job), job)
    cmd.Parameters.Append cmd.CreateParameter ("deptno", adInteger, adParamInput, , deptno)

    ' Execute the completed statement.
    cmd.Execute

    Set cmd       = nothing

    Response.Write "Employee inserted sucessfully.<br />" & vbCrLf
  End Sub


  Sub example_update (conn)
    Dim cmd, sql, empno, ename, job

    Response.Write "<br />Update an existing employee.<br />" & vbCrLf

    ' Prepare an update statement containing bind variables.
    sql = "UPDATE emp SET ename = ?, job = ? WHERE empno = ?"
    Set cmd = Server.CreateObject ("ADODB.Command")
    Set cmd.ActiveConnection = conn
    cmd.CommandText = sql
    cmd.CommandType = adCmdText

    ' Bind the value into the prepared statement.
    empno = 9999
    ename = "TIM_HALL"
    job   = "DBA/DEV"

    ' name, type, direction, size, value
    cmd.Parameters.Append cmd.CreateParameter ("ename", adVarChar, adParamInput, Len(ename), ename)
    cmd.Parameters.Append cmd.CreateParameter ("job", adVarChar, adParamInput, Len(job), job)
    cmd.Parameters.Append cmd.CreateParameter ("empno", adInteger, adParamInput, , empno)

    ' Execute the completed statement.
    cmd.Execute

    Set cmd       = nothing

    Response.Write "Employee updated sucessfully.<br />" & vbCrLf
  End Sub


  Sub example_delete (conn)
    Dim cmd, sql, empno

    Response.Write "<br />Delete an existing employee.<br />" & vbCrLf

    ' Prepare a delete statement containing bind variables.
    sql = "DELETE FROM emp WHERE empno = ?"
    Set cmd = Server.CreateObject ("ADODB.Command")
    Set cmd.ActiveConnection = conn
    cmd.CommandText = sql
    cmd.CommandType = adCmdText

    ' Bind the value into the prepared statement.
    empno = 9999

    ' name, type, direction, size, value
    cmd.Parameters.Append cmd.CreateParameter ("empno", adInteger, adParamInput, , empno)

    ' Execute the completed statement.
    cmd.Execute

    Set cmd = nothing

    Response.Write "Employee deleted sucessfully.<br />" & vbCrLf
  End Sub

%>

The following sites and articles may help you get started.

ASP.NET (VB.NET)

ASP.NET is Microsoft's replacement for Active Server Pages. The following example shows how it can be used to interact with an Oracle database using the VB.NET language.

<%@ Page CompilerOptions='/R:"C:\oracle\product\10.2.0\db_1\BIN\Oracle.DataAccess.dll"' Debug="true" Language="VB" %>
<%@ import Namespace="System" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="Oracle.DataAccess.Client" %>
<script runat="server">

    Sub Page_Load()
         ' Accept a parameter called "deptno" from a form or the query string.
         Dim deptno As String = Request.QueryString("deptno")

         ' Default the value if it is not present.
         If deptno = "" Then
             deptno = "10"
         End If

         ' Connect to the SCOTT schema of the DB10G database.
         Dim objConn As OracleConnection = New OracleConnection("User Id=scott;Password=tiger;Data Source=DB10G;")
         objConn.Open()

         Response.Write("Connected sucessfully.<br /><br />")

         example_query(objConn, deptno)

         example_insert(objConn, deptno)
         example_query(objConn, deptno)

         example_update(objConn)
         example_query(objConn, deptno)

         example_delete(objConn)
         example_query(objConn, deptno)

         objConn.Close()
         objConn.Dispose()

         Response.Write("<br />Disconnected sucessfully.<br /><br />")
    End Sub


    Sub example_query(objConn As OracleConnection, deptno As Integer)
         Response.Write("Return employees for department " & deptno & ".<br />")

         Dim strSQL As String = "SELECT * FROM emp WHERE deptno = :deptno ORDER BY empno"
         Dim objCmd As OracleCommand = New OracleCommand(strSQL, objConn)

         Dim objParam1 As OracleParameter = New OracleParameter("deptno", OracleDbType.Int32)
         objParam1.Direction = ParameterDirection.Input
         objParam1.Value = deptno
         objCmd.Parameters.Add(objParam1)

         Dim objDataReader As OracleDataReader = objCmd.ExecuteReader()

         While (objDataReader.Read())
             ' Item collection handles NULL value and datatype conversions for us.
             Response.Write("empno=" & objDataReader.Item("empno"))
             Response.Write(" ename=" & objDataReader.Item("ename"))
             Response.Write(" job=" & objDataReader.Item("job"))
             Response.Write(" mgr=" & objDataReader.Item("mgr"))
             Response.Write(" hiredate=" & objDataReader.Item("hiredate"))
             Response.Write(" sal=" & objDataReader.Item("sal"))
             Response.Write(" comm=" & objDataReader.Item("comm"))
             Response.Write(" deptno=" & objDataReader.Item("deptno") & "<br />")
         End While

         objDataReader.Close()
         objDataReader.Dispose()
         objCmd.Dispose()
    End Sub


    Sub example_insert(objConn As OracleConnection, deptno As Integer)
         Response.Write("<br />Insert a new employee.<br />")

         ' Prepare an insert statement containing bind variables.
         Dim strSQL As String = "INSERT INTO emp (empno, ename, job, deptno) VALUES (:empno, :ename, :job, :deptn)"
         Dim objCmd As OracleCommand = New OracleCommand(strSQL, objConn)

         ' Bind the value into the prepared statement.
         Dim empno As Integer = 9999
         Dim ename As String  = "HALL"
         Dim job As String    = "DBA"

         ' name, type, value, direction
         Dim objPrm(4) As OracleParameter
         objPrm(0) = objCmd.Parameters.Add("empno", OracleDbType.Decimal, empno, ParameterDirection.Input)
         objPrm(1) = objCmd.Parameters.Add("ename", OracleDbType.Varchar2, ename, ParameterDirection.Input)
         objPrm(2) = objCmd.Parameters.Add("job", OracleDbType.Varchar2, job, ParameterDirection.Input)
         objPrm(3) = objCmd.Parameters.Add("deptno", OracleDbType.Decimal, deptno, ParameterDirection.Input)

         ' Execute the completed statement.
         objCmd.ExecuteNonQuery()

         objCmd.Parameters.Clear()

         objPrm(0).Dispose()
         objPrm(1).Dispose()
         objPrm(2).Dispose()
         objPrm(3).Dispose()
         objCmd.Dispose()

         Response.Write("Employee inserted sucessfully.<br />")
    End Sub


    Sub example_update(objConn As OracleConnection)
         Response.Write("<br />Update an existing employee.<br />")

         ' Prepare an update statement containing bind variables.
         Dim strSQL As String = "UPDATE emp SET ename = :ename, job = :job WHERE empno = :empno"
         Dim objCmd As OracleCommand = New OracleCommand(strSQL, objConn)

         ' Bind the value into the prepared statement.
         Dim empno As Integer = 9999
         Dim ename As String  = "TIM_HALL"
         Dim job As String    = "DBA/DEV"

         ' name, type, value, direction
         Dim objPrm(3) As OracleParameter
         objPrm(0) = objCmd.Parameters.Add("ename", OracleDbType.Varchar2, ename, ParameterDirection.Input)
         objPrm(1) = objCmd.Parameters.Add("job", OracleDbType.Varchar2, job, ParameterDirection.Input)
         objPrm(2) = objCmd.Parameters.Add("empno", OracleDbType.Decimal, empno, ParameterDirection.Input)

         ' Execute the completed statement.
         objCmd.ExecuteNonQuery()

         objCmd.Parameters.Clear()

         objPrm(0).Dispose()
         objPrm(1).Dispose()
         objPrm(2).Dispose()
         objCmd.Dispose()

         Response.Write("Employee updated sucessfully.<br />")
    End Sub


    Sub example_delete(objConn As OracleConnection)
         Response.Write("<br />Delete an existing employee.<br />")

         ' Prepare a delete statement containing bind variables.
         Dim strSQL As String = "DELETE FROM emp WHERE empno = :empno"
         Dim objCmd As OracleCommand = New OracleCommand(strSQL, objConn)

         ' Bind the value into the prepared statement.
         Dim empno As Integer = 9999

         ' name, type, value, direction
         Dim objPrm As OracleParameter
         objPrm = objCmd.Parameters.Add("empno", OracleDbType.Decimal, empno, ParameterDirection.Input)

         ' Execute the completed statement.
         objCmd.ExecuteNonQuery()

         objCmd.Parameters.Clear()

         objPrm.Dispose()
         objCmd.Dispose()

         Response.Write("Employee deleted sucessfully.<br />")
    End Sub

</script>

The following sites and articles may help you get started.

ASP.NET (C#.NET)

ASP.NET is Microsoft's replacement for Active Server Pages. The following example shows how it can be used to interact with an Oracle database using the C# language.

<%@ Page CompilerOptions='/R:"C:\oracle\product\10.2.0\db_1\BIN\Oracle.DataAccess.dll"' Debug="true" Language="c#" %>
<%@ import Namespace="System" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="Oracle.DataAccess.Client" %>
<script runat="server">

    void Page_Load() {
         // Accept a parameter called "deptno" from a form or the query string.
         String deptno = Request.QueryString["deptno"];

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

         // Connect to the SCOTT schema of the DB10G database.
         OracleConnection objConn = new OracleConnection("User Id=scott;Password=tiger;Data Source=DB10g;");
         objConn.Open();

         Response.Write("Connected sucessfully.<br /><br />");

         example_query(objConn, deptno);

         example_insert(objConn, deptno);
         example_query(objConn, deptno);

         example_update(objConn);
         example_query(objConn, deptno);

         example_delete(objConn);
         example_query(objConn, deptno);

         objConn.Close();
         objConn.Dispose();

         Response.Write("<br />Disconnected sucessfully.<br /><br />");
    }


    void example_query (OracleConnection objConn, String deptno) {
         Response.Write("Return employees for department " + deptno + ".<br />");

         String strSQL = "SELECT * FROM emp WHERE deptno = :deptno ORDER BY empno";
         OracleCommand objCmd = new OracleCommand(strSQL, objConn);

         OracleParameter objParam1 = new OracleParameter("deptno", OracleDbType.Int32);
         objParam1.Direction = ParameterDirection.Input;
         objParam1.Value = deptno;
         objCmd.Parameters.Add(objParam1);

         OracleDataReader objDataReader = objCmd.ExecuteReader();

         String temp = "";
         while (objDataReader.Read()) {
             // Handle possible NULL values.
             temp = "";
             if (!objDataReader.IsDBNull(0)) temp = objDataReader.GetInt16(0).ToString();
             Response.Write("empno=" + temp);
             temp = "";
             if (!objDataReader.IsDBNull(1)) temp = objDataReader.GetString(1);
             Response.Write(" ename=" + temp);
             temp = "";
             if (!objDataReader.IsDBNull(2)) temp = objDataReader.GetString(2);
             Response.Write(" job=" + temp);
             temp = "";
             if (!objDataReader.IsDBNull(3)) temp = objDataReader.GetInt16(3).ToString();
             Response.Write(" mgr=" + temp);
             temp = "";
             if (!objDataReader.IsDBNull(4)) temp = objDataReader.GetDateTime(4).ToString();
             Response.Write(" hiredate=" + temp);
             temp = "";
             if (!objDataReader.IsDBNull(5)) temp = objDataReader.GetDecimal(5).ToString();
             Response.Write(" sal=" + temp);
             temp = "";
             if (!objDataReader.IsDBNull(6)) temp = objDataReader.GetDecimal(6).ToString();
             Response.Write(" comm=" + temp);
             temp = "";
             if (!objDataReader.IsDBNull(7)) temp = objDataReader.GetInt16(7).ToString();
             Response.Write(" deptno=" + temp + "<br />");
         }

         objDataReader.Close();
         objDataReader.Dispose();
         objCmd.Dispose();
    }


    void example_insert (OracleConnection objConn, String deptno) {
         Response.Write("<br />Insert a new employee.<br />");

         // Prepare an insert statement containing bind variables.
         String strSQL = "INSERT INTO emp (empno, ename, job, deptno) VALUES (:empno, :ename, :job, :deptn)";
         OracleCommand objCmd = new OracleCommand(strSQL, objConn);

         // Bind the value into the prepared statement.
         int    empno = 9999;
         String ename = "HALL";
         String job   = "DBA";

         // name, type, value, direction
         OracleParameter[] objPrm = new OracleParameter[4];
         objPrm[0] = objCmd.Parameters.Add("empno", OracleDbType.Decimal, empno, ParameterDirection.Input);
         objPrm[1] = objCmd.Parameters.Add("ename", OracleDbType.Varchar2, ename, ParameterDirection.Input);
         objPrm[2] = objCmd.Parameters.Add("job", OracleDbType.Varchar2, job, ParameterDirection.Input);
         objPrm[3] = objCmd.Parameters.Add("deptno", OracleDbType.Decimal, deptno, ParameterDirection.Input);

         // Execute the completed statement.
         objCmd.ExecuteNonQuery();

         objCmd.Parameters.Clear();

         objPrm[0].Dispose();
         objPrm[1].Dispose();
         objPrm[2].Dispose();
         objPrm[3].Dispose();
         objCmd.Dispose();

         Response.Write("Employee inserted sucessfully.<br />");
    }


    void example_update (OracleConnection objConn) {
         Response.Write("<br />Update an existing employee.<br />");

         // Prepare an update statement containing bind variables.
         String strSQL = "UPDATE emp SET ename = :ename, job = :job WHERE empno = :empno";
         OracleCommand objCmd = new OracleCommand(strSQL, objConn);

         // Bind the value into the prepared statement.
         int    empno = 9999;
         String ename = "TIM_HALL";
         String job   = "DBA/DEV";

         // name, type, value, direction
         OracleParameter[] objPrm = new OracleParameter[3];
         objPrm[0] = objCmd.Parameters.Add("ename", OracleDbType.Varchar2, ename, ParameterDirection.Input);
         objPrm[1] = objCmd.Parameters.Add("job", OracleDbType.Varchar2, job, ParameterDirection.Input);
         objPrm[2] = objCmd.Parameters.Add("empno", OracleDbType.Decimal, empno, ParameterDirection.Input);

         // Execute the completed statement.
         objCmd.ExecuteNonQuery();

         objCmd.Parameters.Clear();

         objPrm[0].Dispose();
         objPrm[1].Dispose();
         objPrm[2].Dispose();
         objCmd.Dispose();

         Response.Write("Employee updated sucessfully.<br />");
    }


    void example_delete (OracleConnection objConn) {
         Response.Write("<br />Delete an existing employee.<br />");

         // Prepare a delete statement containing bind variables.
         String strSQL = "DELETE FROM emp WHERE empno = :empno";
         OracleCommand objCmd = new OracleCommand(strSQL, objConn);

         // Bind the value into the prepared statement.
         int empno = 9999;

         // name, type, value, direction
         OracleParameter objPrm;
         objPrm = objCmd.Parameters.Add("empno", OracleDbType.Decimal, empno, ParameterDirection.Input);

         // Execute the completed statement.
         objCmd.ExecuteNonQuery();

         objCmd.Parameters.Clear();

         objPrm.Dispose();
         objCmd.Dispose();

         Response.Write("Employee deleted sucessfully.<br />");
    }

</script>

The following sites and articles may help you get started.

Expected Output

The following text shows the type of output expected from each example. There may be some variation in the way dates and NULL values are displayed.

Connected sucessfully.

Return employees for department 10.
empno=7782 ename=CLARK job=MANAGER mgr=7839 hiredate=1981-06-09 00:00:00.0 sal=2450 comm= deptno=10
empno=7839 ename=KING job=PRESIDENT mgr= hiredate=1981-11-17 00:00:00.0 sal=5000 comm= deptno=10
empno=7934 ename=MILLER job=CLERK mgr=7782 hiredate=1982-01-23 00:00:00.0 sal=1300 comm= deptno=10

Insert a new employee.
Employee inserted sucessfully.
Return employees for department 10.
empno=7782 ename=CLARK job=MANAGER mgr=7839 hiredate=1981-06-09 00:00:00.0 sal=2450 comm= deptno=10
empno=7839 ename=KING job=PRESIDENT mgr= hiredate=1981-11-17 00:00:00.0 sal=5000 comm= deptno=10
empno=7934 ename=MILLER job=CLERK mgr=7782 hiredate=1982-01-23 00:00:00.0 sal=1300 comm= deptno=10
empno=9999 ename=HALL job=DBA mgr= hiredate= sal= comm= deptno=10

Update an existing employee.
Employee updated sucessfully.
Return employees for department 10.
empno=7782 ename=CLARK job=MANAGER mgr=7839 hiredate=1981-06-09 00:00:00.0 sal=2450 comm= deptno=10
empno=7839 ename=KING job=PRESIDENT mgr= hiredate=1981-11-17 00:00:00.0 sal=5000 comm= deptno=10
empno=7934 ename=MILLER job=CLERK mgr=7782 hiredate=1982-01-23 00:00:00.0 sal=1300 comm= deptno=10
empno=9999 ename=TIM_HALL job=DBA/DEV mgr= hiredate= sal= comm= deptno=10

Delete an existing employee.
Employee deleted sucessfully.
Return employees for department 10.
empno=7782 ename=CLARK job=MANAGER mgr=7839 hiredate=1981-06-09 00:00:00.0 sal=2450 comm= deptno=10
empno=7839 ename=KING job=PRESIDENT mgr= hiredate=1981-11-17 00:00:00.0 sal=5000 comm= deptno=10
empno=7934 ename=MILLER job=CLERK mgr=7782 hiredate=1982-01-23 00:00:00.0 sal=1300 comm= deptno=10

Disconnected sucessfully.

Hope this helps. Regards Tim...

Back to the Top.