Using OO4O Automation with Active Server Pages (ASP)

This example uses Active Server Pages (ASP) in a Microsoft Internet Information Server (IIS) to demonstrate the Connection Pooling feature of Oracle Objects for OLE. The sample code executes a SQL SELECT query and returns the result as an HTML table. The database connection used in this script is obtained from a pool that is created when global.asa is executed.

To use Oracle Objects for OLE with OLE Automation and IIS, you need to install IIS 3.0 or later, including all ASP extensions. On the computer where IIS is running, an Oracle database must be accessible also.

Note: The sample code for this example is available in the ORACLE_BASE\ORACLE_HOME\oo4o\iis\samples\asp\connpool directory.

Start SQL*Plus and log in to the Oracle database as “scott/tiger”. Create the following PL/SQL procedures.

-- creates PL/SQL package to be used in ASP demos

create or replace package ASP_demo as

--cursor c1 is select * from emp;

type empCur is ref cursor;

PROCEDURE GetCursor(p_cursor1 in out empCur, indeptno IN NUMBER,

p_errorcode OUT NUMBER);

END ASP_demo;

/

--------------------------------------------------------------

create or replace package body ASP_demo as

PROCEDURE GetCursor(p_cursor1 in out empCur, indeptno IN NUMBER,

p_errorcode OUT NUMBER) is

BEGIN

p_errorcode:= 0;

open p_cursor1 for select * from emp where deptno = indeptno;

EXCEPTION

When others then

p_errorcode:= SQLCODE;

END GetCursor;

----------------------------------------------------------------

END ASP_demo;

/

2. Create the Active Server Pages (ASP) sample code. The OO4O related code is in bold.

'GLOBAL.ASA

<OBJECT RUNAT=Server SCOPE=Application ID=OraSession

PROGID="OracleInProcServer.XOraSession"></OBJECT>

<SCRIPT LANGUAGE=VBScript RUNAT=Server>

Sub Application_OnStart

'Get an instance of the Connection Pooling object and

'create a pool of OraDatabase

OraSession.CreateDatabasePool 1,40,200,"exampledb", "scott/tiger", 0

End Sub

'OO4ODEMO.ASP

<html>

<head>

<title>Oracle Objects For OLE (OO4O) </title>

</head>

<body BGCOLOR="#FFFFFF">

<font FACE="ARIAL,HELVETICA">

<h2 align="center">Oracle Objects For OLE (OO4O) </h2>

<form ACTION="OO4ODEMO.asp" METHOD="POST">

<%

SqlQuery = Request.Form("sqlquery")

%>

<p>This sample executes a SQL SELECT query and returns the result as an HTML table. The database connection used in this script is obtained from a pool that is created when the <strong>global.asa</strong> is executed. </p>

<p>SQL Select Query: <input SIZE="48" NAME="sqlquery"> </p>

<p><input TYPE="SUBMIT"> <input TYPE="RESET"> <input LANGUAGE="VBScript" TYPE="button" VALUE="Show ASP Source" ONCLICK="Window.location.href = "oo4oasp.htm""

NAME="ShowSrc"></p>

</form>

<%

If SqlQuery = "" Then

%>

<% Else %>

<table BORDER="1">

<%

Set OraDatabase = OraSession.GetDatabaseFromPool(10)

Set OraDynaset = OraDatabase.CreateDynaset(SqlQuery,0)

Set Columns = OraDynaset.Fields

%>

<tr>

<td><table BORDER="1">

<tr>

<% For i = 0 to Columns.Count - 1 %>

<td><b><% = Columns(i).Name %></b></td>

<% Next %>

</tr>

<% while NOT OraDynaset.EOF %>

<tr>

<% For col = 0 to Columns.Count - 1 %>

<td><% = Columns(col) %>

</td>

<% Next %>

</tr>

<% OraDynaset.MoveNext %>

<% WEnd %>

</table>

<p></font><%End If%> </p>

<hr>

</td>

</tr>

</table>

</body>

</html>

3. Create a virtual directory from Microsoft Internet Service Manager whose access are both readable and executable, and place all .asp and .asa files in that directory.

4. Create an html page from which to launch oo4odemo.asp. Add a link in the page:

<a href="/<your_path>/OO4ODEMO.ASP">This link launches the demo!</a>

5. Load the page in a web browser and click on the link to the demo.

6. Enter a query, such as 'SELECT * FROM EMP', in the SQL SELECT Query field and select Submit Query. Do not include a ';' at the end of the query.

Text description of the illustration iissamp.gif