Calling Stored Procedures

The ODBC shorthand syntax for calling stored programs supports Microsoft SQL Server stored procedures, and Oracle stored procedures, functions, and packages. The optional “?=“ captures the return value for an Oracle function or a SQL Server procedure. The parameter syntax is used to pass and return values to and from the called program. In most situations, the same syntax can be generically applied to Oracle and SQL Server applications.

In the following example, the SHOW_RELUCTANT_STUDENTS function is part of the Oracle package P1. This function must exist in a package because it returns multiple rows from a PL/SQL cursor. When you call a function or procedure that exists in a package, the package name must be placed in front of the program name.

The SHOW_RELUCTANT_STUDENTS function in the package P1 uses a package cursor to retrieve multiple rows of data. Each row must be requested with a call to this function. If there are no more rows to retrieve, the function returns the value of 0, indicating that there are no more rows to retrieve. The resulting performance of this sample Oracle package and its function might be less than satisfactory. SQL Server procedures are more efficient with this type of operation.