Oracle8 and Oracle8i

Stored Procedures

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:

  • One-Dimensional: each PL/SQL table can contain only one column of data.
  • Integer-Indexed: Each element of the array is indexed by a single integer much like a VBScript array.
  • Unbounded Dimensions: There is no limit to the size of a PL/SQL table, as the structure
    will alter in size to accommodate new elements.
  • Uniform Data Type: Only a single uniform data type can be stored in a particular
    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!

  • Use the Microsoft ODBC Driver for Oracle.

    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.
  • Argument Naming and Positioning.

    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".
  • Maximum Records Returned.

    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>

    You might also like...

    Comments

    Contribute

    Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

    Our tools

    We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

    “A computer lets you make more mistakes faster than any other invention in human history, with the possible exceptions of handguns and tequila” - Mitch Ratcliffe