Oracle Database stored procedures

Oracle stored procedures

If your database is Oracle Version 7.2 or higher, you can use an Oracle stored procedure that has a result set as an IN OUT (reference) parameter.

Procedures with a single result set

You can use stored procedures that return a single result set in DataWindow objects, reports, and embedded SQL, but not when using the RPCFUNC keyword to declare the stored procedure as an external function or subroutine.

Procedures with multiple result sets

You can use stored procedures that return multiple result sets only in embedded SQL. Multiple result sets are not supported in DataWindow objects, reports, or with the RPCFUNC keyword.

The O90 database interface supports SQL CREATE TYPE and CREATE TABLE statements for Oracle user-defined types (objects) in the ISQL view of the Database painter. It correctly handles SQL SELECT, INSERT, UPDATE, and DELETE statements for user-defined types in the Database and DataWindow painters. For more information, see Using Oracle

Methods for using Oracle stored procedures

There are three methods for using Oracle stored procedures in a PowerBuilder application:

  • As a data source

    for DataWindow objects.

  • RPCFUNC keyword (Recommended)

    Use the RPCFUNC keyword to declare the stored procedure as an external function or external subroutine. You cannot use the RPCFUNC keyword with Oracle stored procedures that return result sets. Using the RPCFUNC keyword to declare the stored procedure provides the best performance and has more supported features and fewer limitations than the DECLARE Procedure and PBDBMS methods.

  • DECLARE Procedure statement

    Use the DECLARE Procedure (Oracle DECLARE and EXECUTE) statement to declare the stored procedure as an external function or external subroutine. This includes support for fetching against Oracle stored procedures that return result sets.

See also

Supported features when using Oracle stored procedures

Using DECLARE, EXECUTE, FETCH, and CLOSE with Oracle stored procedures