📋Steps in this guide1/7

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.

oracle miscconfigurationintermediate

by OracleDba

11 views

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

Code/Command (click line numbers to comment):

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

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

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. The following sites and articles may help you get started. - Perl.org - DBI.Perl.org - cpan.org - DBD::Oracle Module

Code/Command (click line numbers to comment):

#!/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";
}

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. The following sites and articles may help you get started. - Java.com - Fast Track JSP - Developing Web Applications With JavaServer Pages 2.0 - Apache Tomcat

Code/Command (click line numbers to comment):

<%@ 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");
  }
%>

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

Code/Command (click line numbers to comment):

<%
  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

%>

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

Code/Command (click line numbers to comment):

<%@ 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>

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

Code/Command (click line numbers to comment):

<%@ 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>

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. Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!