Various documentation has mentioned that parameters may be passed to stored procedures in any order and that the binding that you specify by using DBVARNAME correctly maps Value attributes to their corresponding database variables, but this is not the case in practice. You should always pass parameters to stored procedures in exactly the same order as they appear in the stored procedure’s interface; otherwise, ColdFusion throws an exception. Many, but not all, database servers can return result sets from stored procedures. For those that can, CFPROCRESULT binds those result sets to ColdFusion queries, which makes them available for use in your ColdFusion applications just as if they were returned from a CFQUERY call. And although some database servers can return multiple result sets from a single CFSTOREDPROC call, the driver with which ColdFusion accesses the database must support such a capability as well. For Oracle, you need a Type 4 JDBC Driver, such as the one that ships standard with ColdFusion MX Enterprise, or the free OIC driver from Oracle if you’re running ColdFusion MX Professional. Look back for a moment at the code. You expected back only one result set from this stored procedure, and you bound that result set to a ColdFusion query object named GetGeorgiaCompanies by using the following CFPROCRESULT tag:

<cfprocresult name=”GetGeorgiaCompanies” resultset=”1”>

This tag is instructing ColdFusion Server to take the first result set returned from the stored procedure and bind it to a ColdFusion query object named GetGeorgiaCompanies. After this tag executes, your ColdFusion application can use that GetGeorgiaCompanies query object just as if it came from a CFQUERY call. CFPROCRESULT binds result sets to ColdFusion query objects in the order in which they are created within the stored procedure. Mapping result sets in the stored procedure to query objects in ColdFusion may seem an obvious and trivial task—just number them in the order they appear, top to bottom—but this task can become confusing unless you follow the rules. In this article, you adapt the code to produce exactly the same results in Oracle. As you soon see, the differences are significant. Oracle has some amazing capabilities and is a very complex product to master. Oracle enables you to create stored procedures, for example, but it also enables you to create stored functions that, as do all functions, return single values to the programs that call them. Oracle even enables you to bundle together collections of stored procedures, stored functions, and user-defined data types into packages, which are another type of database object that can be called from an outside application such as ColdFusion. Some of these complexities become apparent if you write Oracle stored procedures that must return multiple result sets. Every time that you execute a query on an Oracle database, a workspace in memory called a cursor is created that contains the data result set. This data is a static constant, like a simple string or number, except that it is multivalued. Just as you can place a simple string or number value into a variable and then reference that variable by its name later on in your code, you can do the same thing with a cursor by placing it into a cursor variable. Cursor variables have the data type REF CURSOR, so named because it holds a reference (or pointer) to the original cursor in memory. By passing this cursor variable back to ColdFusion server, ColdFusion can gain access to the Oracle result set referred to by the cursor variable. Finally, by including a call to CFPROCRESULT, you can bind that cursor to a standard ColdFusion query object and make use of it in your ColdFusion applications. By using this method through the Type 4 JDBC Oracle database driver available in ColdFusion MX Enterprise or Oracle’s free OIC database driver in conjunction with ColdFusion MX Professional, Oracle can return multiple result sets from Oracle packages, as in the code.

CREATE OR REPLACE PACKAGE pkg_CompaniesEmployees AS
   TYPE recCompany IS RECORD (
   vCompanyName Company.CompanyName%TYPE,
   vZipCode Company.ZipCode%TYPE
   );
   TYPE recEmployee IS RECORD (
   vLastname Employee.Lastname%TYPE,
   vFirstname Employee.Firstname%TYPE
   );
   TYPE curCompanies IS REF CURSOR RETURN recCompany;
   TYPE curEmployees IS REF CURSOR RETURN recEmployee;
   PROCEDURE sp_GetCompaniesEmployees (
   vState IN Char,
   vName IN Char,
   rsCompanies OUT curCompanies,
   rsEmployees OUT curEmployees
   );
 END pkg_CompaniesEmployees;

/
   CREATE OR REPLACE PACKAGE BODY pkg_CompaniesEmployees AS
   PROCEDURE sp_GetCompaniesEmployees (
   vState IN Char,
   vName IN Char,
   rsCompanies OUT curCompanies,
   rsEmployees OUT curEmployees
   )
   AS
   BEGIN
   OPEN rsCompanies FOR
   SELECT
   CompanyName,
   ZipCode
   FROM
   Company
   WHERE
   State = sp_GetCompaniesEmployees.vState
   ORDER BY
   ZipCode ASC;
   OPEN rsEmployees FOR
   SELECT
   Firstname,
   Lastname
   FROM
   Employee
   WHERE
   Lastname LIKE sp_GetCompaniesEmployees.vName || ‘%’
   ORDER BY
   Lastname ASC,
   Firstname ASC;
   END sp_GetCompaniesEmployees;
   END pkg_CompaniesEmployees;
 /

First, an Oracle package is just what its name implies: a big package or container for individual components—in this case, four user-defined data types and a stored procedure. A package has two parts: a header, where user-defined data types and the interfaces into any stored procedures and functions contained in the package are defined, and the body, where the stored procedures and functions themselves are programmed. So the first task in creating an Oracle package is to define its interface, which you do by using the following code:

CREATE OR REPLACE PACKAGE pkg_CompaniesEmployees AS
   TYPE recCompany IS RECORD (
   vCompanyName Company.CompanyName%TYPE,
   vZipCode Company.ZipCode%TYPE
   );
   TYPE recEmployee IS RECORD (
   vLastname Employee.Lastname%TYPE,
   vFirstname Employee.Firstname%TYPE
   );
   TYPE curCompanies IS REF CURSOR RETURN recCompany;
   TYPE curEmployees IS REF CURSOR RETURN recEmployee;
   PROCEDURE sp_GetCompaniesEmployees (
   vState IN Char,
   vName IN Char,
   rsCompanies OUT curCompanies,
   rsEmployees OUT curEmployees
   );
   END pkg_CompaniesEmployees;
   /

But to see exactly what the header must contain, you need to look ahead—in the body of the Oracle package—to the actual cursor that contains the first query result set, as follows:

OPEN rsCompanies FOR
   SELECT
   CompanyName,
   ZipCode
   FROM
   Company
   WHERE
   State = sp_GetCompaniesEmployees.vState
   ORDER BY
   ZipCode ASC;

Every row retrieved into this cursor contains two columns from the Company table: CompanyName and ZipCode; which define the dimension of the cursor variable that references this cursor. Next, you need a data type that is of this dimension. Fortunately, Oracle has the capability to create complex user-defined data types called records. You take advantage of this capability and create a RECORD data type named recCompany that holds the CompanyName and ZipCode columns of each row retrieved from the Company table, as follows:

TYPE recCompany IS RECORD (
   vCompanyName Company.CompanyName%TYPE,
   vZipCode Company.ZipCode%TYPE
   ); 

The constituent parts of the recCompany data type must also have their data types defined. Appending %TYPE to a data element returns its data type, so the preceding record definition is equivalent to the following code:

TYPE recCompany IS RECORD (
   vCompanyName VARCHAR2(40),
   vZipCode VARCHAR2(10)
   );

After you have a data type of the same dimension as the rows to be contained by the cursor, you can define a cursor variable to refer to the cursor, as follows: TYPE curCompanies IS REF CURSOR RETURN recCompany; What this TYPE definition is saying is, “Define a cursor variable named curCompanies that returns rows that have the same dimension as the recCompany data type.” You repeat the same programming for the second cursor variable—curEmployees—and you’re ready to move on to the package header’s interface into the stored procedure. The interface of the stored procedure that returns your two result sets is similar to the stored procedures that you created, in that it contains parameters of specific data types. You declare two input parameters that are used in the WHERE clauses of your two queries, plus you declare two output parameters of the REF CURSOR data types that you defined as follows:

rsCompanies OUT curCompanies,
   rsEmployees OUT curEmployees

The entire code block that defines the interface into the stored procedure is as follows:

PROCEDURE sp_GetCompaniesEmployees (
   vState IN Char,
   vName IN Char,
   rsCompanies OUT curCompanies,
   rsEmployees OUT curEmployees
   );

It defines the interface of the sp_GetCompaniesEmployees stored procedure. Finally, you have the following code:

END pkg_CompaniesEmployees;
   /

It concludes the formal definition of the interface into the pkg_CompaniesEmployee Oracle package.