We'll finish off with something of a holy grail. Unlike SQL Server, PL/SQL does not allow us to execute a SELECT statement within a stored procedure without a corresponding INTO statement . This means we cannot easily return a recordset back to the calling client whether it is an ASP script or another PL/SQL program.
Consider the following SQL Server stored procedure:
CREATE PROCEDURE sp_GetAuthors
AS
BEGIN
SELECT au_lname,
au_fname
FROM authors
ORDER BY au_lname,
au_fname
END
Try creating the following very similar stored procedure in Oracle:
CREATE PROCEDURE sp_GetAuthors
AS
BEGIN
SELECT ename
FROM emp
ORDER BY ename;
END;
You'll receive the following error messages:
Errors for PROCEDURE
SP_GETAUTHORS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PLS-00428:
an INTO clause is expected in this SELECT statement
4/3 PL/SQL:
SQL Statement ignored
Once upon a time, I searched Oracle's own PL/SQL documentation for an answer to this, and I got the impression that this will never be implemented. I believe the reason was, that they feel a calling program, X, should pass parameters into another program, Y, allowing Y to populate the results so that X can then deal with them. This approach doesn't really help us from an ADO point of view.
However, it can actually be achieved by using PL/SQL tables and the Microsoft ODBC for Oracle , or reference cursors with Oracle's Oracle Provider for OLE DB. We'll start off with PL/SQL tables and cover reference cursors in the next section.
PL/SQL Tables are somewhat of a misnomer as it might be easier if they were called PL/SQL Arrays. The following diagram shows three records from the emp table and how they would be represented in three PL/SQL Table variables:
|
|
We have three columns, ENAME, JOB and SAL in our source result set. For each column of data we have a corresponding PL/SQL table variable, o_ENAME, o_JOB and o_SAL, each mapping to a value of each column. The PL/SQL table variables are distinct entities in their own right. In order to populate the PL/SQL tables we need to scroll through the records in the source resultset, and add an entry for each column to the corresponding element in each PL/SQL table.
PL/SQL tables have the following characteristics:
will alter in size to accommodate new elements.
PL/SQL table. So, if you start off with a NUMBER data-type, then all other elements
must also be a NUMBER.
PL/SQL table types are defined using the TYPE statement , for example:
TYPE
tblFirstName IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
This would declare a PL/SQL table type called tblFirstName that could be used by a variable to store an array of strings up to 30 characters in length. A variable of this type could be declared as the parameter to a stored procedure, thus:
PROCEDURE GetEmployeeList(o_FirstName
OUT tblFirstName)
Each PL/SQL table type that you want to use must be defined within the specification section of an Oracle Package.
In the case of a stored procedure that returns a list of employee names and numbers, we must create an individual parameter for both the employee name and the employee number values, both being declared using the PL/SQL table type as defined in our package specification.
In order to populate the employee number and employee name PL/SQL tables with data, we can use a cursor that loops through a selection of records and transfers each item of data into the corresponding PL/SQL table element.
A cursor allows you to programmatically step through a result set of data, performing operations based on the current row until the end of the result set is reached.
The easiest way to implement an Oracle
cursor is by declaring it outside of a program block and
then opening it using a cursor FOR...LOOP.
The cursor FOR...LOOP
opens the cursor for you, repeatedly fetches rows of values from the result
set into fields and then closes the cursor once all
rows have been processed .
For example, the following cursor will calculate the total salary for all employees in the emp table:
DECLARE CURSOR emp_cur
IS SELECT sal FROM emp;
TotalSalary NUMBER;
BEGIN
FOR emp_rec
IN emp_cur
LOOP
TotalSalary
:= TotalSalary + emp_rec.sal;
END LOOP;
END;
We'll start our example off by creating a simple package that contains one stored procedure called EmployeeSearch. This will allow us to retrieve a list of employees from the emp table within the scott schema, based on their name.
Jump to your SQL editor and add the following package specification to the scott schema:
CREATE OR REPLACE PACKAGE
Employee_Pkg
AS
TYPE tblEmpNo IS
TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE tblEName IS
TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
TYPE tblJob
IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER;
PROCEDURE EmployeeSearch
(i_EName
IN VARCHAR2,
o_EmpNo
OUT tblEmpNo,
o_EName
OUT tblEName,
o_Job
OUT tblJob);
END Employee_Pkg;
Our package is called Employee_Pkg, which we will need to use when referencing the EmployeeSearch procedure. We will be returning three columns in our Recordset: employee number, name and job, so we have created a separate PL/SQL table type for each column.
Note that EmployeeSearch doesn't actually include any code – that's the job of the package body. If you try to define the implementation here you'll get an error from Oracle.
We've defined one input parameter, the name to search for, and a separate output parameter for each of the columns to return. Now we can create the package body – the bit that does the actual work, so execute the following SQL script :
CREATE OR REPLACE PACKAGE
BODY Employee_Pkg
AS
PROCEDURE EmployeeSearch
(i_EName
IN VARCHAR2,
o_EmpNo
OUT tblEmpNo,
o_EName
OUT tblEName,
o_Job
OUT tblJob)
IS
We start off by adding the word BODY before the package name, dropping the PL/SQL table definitions, and adding the word IS to start the implementation.
CURSOR cur_employee
(curName VARCHAR2) IS
SELECT empno,
ename,
job
FROM emp
WHERE UPPER(ename)
LIKE '%' || UPPER(curName) || '%'
ORDER BY ename;
RecordCount NUMBER
DEFAULT 0;
If you recall from our overview of PL/SQL
blocks, we need to declare any variables or cursors that
are going to be used by our procedure. We define a cursor called cur_employee
that has its own
input parameter called curName
and a number variable called RecordCount
to store a count of the records processed .
Our cursor isn't that sophisticated: it uses || to add the wildcard character '%' to the beginning and the end of the required search name. In SQL Server, we would have used the + string concatenation operator. This enables the LIKE statement to find any employee's names that contain the specified characters. As we populate each of the PL/SQL table parameters we need to keep a track of the current element being set, so we use RecordCount. PL/SQL tables are 1-based so we must increment the RecordCount first as it starts from 0 initially .
BEGIN
FOR
curRecEmployee IN cur_employee(i_EName)
LOOP
RecordCount:= RecordCount + 1;
o_EmpNo(RecordCount):=
curRecEmployee.empno;
o_EName(RecordCount):=
curRecEmployee.ename;
o_Job(RecordCount):=
curRecEmployee.job;
END
LOOP;
END EmployeeSearch;
END Employee_Pkg;
Here we have defined the actual implementation
of the EmployeeSearch
procedure. We simply
open the cursor and ask it to transfer each record into a cursor variable called
curRecEmployee.
Notice that we didn't actually define the variable curRecEmployee,
as this is simply a reference
name to the record structure for the cursor. We can still refer to it within
our cursor FOR...LOOP
as though it was declared.
Then it's just a case of moving through each record, incrementing the record count, and transferring each individual field into each output parameter in the identical element position using RecordCount.
Now we need to call the procedure from an ASP script to populate the data. This is where you're likely to have the most problems when writing your own procedures. The following rules must be remembered, otherwise it simply won't work and you could spend days and days trying to work out why – as I did!
If you try to use the OLE DB Provider for Oracle you'll get an error message saying "Catastrophic Error"! You should also try to ensure that you're using at least version 2.573.4202.00 of the driver.
When setting the Command object's CommandText, you must ensure that you use exactly the same name and same position for each parameter as you did when you declared each parameter in your stored procedure. If not, you'll get the rather misleading ODBC error message "Resultset column must be a formal argument".
You must use the resultset qualifier as part of your CommandText string to tell the driver which parameters are recordsets, such as:
"{call Employee_Pkg.EmployeeSearch("?, {resultset100, o_EmpNo, o_EName, o_Job})}"
The number after resultset indicates the maximum number of records to be returned in this call. The driver actually allocates a memory cache to store this amount of data. (There appears to be no documentation that confirms what happens when the number of records is a lot less than this number.) If you exceed this number, by even one record, then you will receive Oracle error ORA-06512. It is suggested that you limit the number of records within your cursor population by passing the required value as an additional parameter to your stored procedure and limiting the cursor FOR...LOOP. We didn't do this in our example but it might be a nice exercise to try.
|
So we can now create a simple ASP script to call our procedure. I'm going to use a single ASP script that contains a form that submits to itself and writes out the search results. |
|
<% Option Explicit
Response.Expires
= 0%>
<HTML>
<HEAD>
<TITLE>Stored
Procedure Recordset Demo</TITLE>
</HEAD>
<BODY>
<CENTER><H2>Stored
Procedure Recordset Demo</H2></CENTER>
<%
Dim strSearchName
Dim objConnection
Dim objCommand
Dim objRecordset
Dim varEmpNo
strSearchName = Request.Form("txtSearchName")
If strSearchName = ""
Then strSearchName = "%"
We transfer the txtSearchName input field from the form into a variable. If it was empty, which it will be the first time, we set it to % so that we get all matching names.
Set objConnection =
Server.CreateObject("ADODB.Connection")
With objConnection
.ConnectionString
= "driver={Microsoft ODBC for Oracle};" & _
"server=Oracle8_dev;UID=scott;PWD=tiger;"
.CursorLocation
= adUseClient
.Open
End With
Here we connect to the database using the Microsoft ODBC Driver for Oracle .
Now for the fun part:
Set objCommand = Server.CreateObject("ADODB.Command")
With objCommand
Set .ActiveConnection
= objConnection
.CommandText
= "{call Employee_Pkg.EmployeeSearch("
& _
"?, {resultset 100, o_EmpNo,
o_EName,
o_Job})}"
.CommandType
= adCmdText
.Parameters(0).Value
= strSearchName
Set objRecordset
= .Execute()
End With
%>
We are using the standard {call...} and ? syntax to define the first input parameter. Notice that we have included the {resultset 100….} string, as mentioned above, to define those parameters that are to be returned in the Recordset object and that we only want 100 records returned. We have simply pasted in the names of the parameters exactly as we declared them. The only parameter that we actually set is the first input parameter, the search name. Finally, we call the Execute statement to get our data.
What you do is navigate through the records
in the Recordset
and creating a nicely formatted
HTML table.
<FORM ACTION="StoredProcResultSetDemo.asp"
METHOD="POST">
<INPUT NAME="txtSearchName"
VALUE="<%=strSearchName%>">
<INPUT TYPE="SUBMIT"
VALUE="Search">
<P>
<TABLE BORDER=1>
<TR><TD>Employee</TD><TD>Job</TD></TR>
<%
Do While Not objRecordset.EOF
varEmpNo = objRecordset.Fields("o_EmpNo")
Response.Write
"<TR>" & _
" <TD><A HREF=EditEmp.ASP?EmpNo=" &
varEmpNo & ">" & _
objRecordset.Fields("o_EName")
& "</A></TD>" & _
" <TD>" & objRecordset.Fields("o_Job")
& "</TD>" & _
"</TR>"
objRecordset.MoveNext
Loop
Set objRecordset = Nothing
Set objCommand = Nothing
Set objConnection =
Nothing
%>
</TABLE>
</FORM>
</BODY>
</HTML>
Comments