Oracle and Stored Procedures
\The Sandbox \PB History & Future \New in PB 4 \DW and Reporting \Specifying Data Sources \Oracle and Stored Procedures
Select Painter
Multi-Table
Quick Select
Stored Procedure
Data Source
Oracle and
Stored Procedures

Powersoft has implemented support for stored procedures as data sources for DataWindows and reports. Before you can use this feature, you must install the PBDBMS package on the Oracle7 database server (this is a renamed copy of the Oracle7 DBMS OUTPUT package with all comments removed).

To enable stored procedures, set the new DBParm variable PBDBMS to 1. Then write a stored procedure as you normally would, but code a PBDBMS.PUT_LINE to place the SQL SELECT statement in a holding area from which PowerBuilder can later retrieve it. Each PUT_LINE call can accept a 255 character string and you can call PUT_LINE repeatedly (up to 100 times) to build the entire SELECT statement. After the stored procedure executes, PowerBuilder issues a GET_LINE call to obtain the SQL SELECT statement. It then executes the SQL SELECT statement and fetches the results into the DataWindow or report.

Example

    // This procedure has two retrieval arguments (dbname and code)
    create procedure my_dw2 (dbname varchar2, code varchar2)is mystr varchar2(255);
    begin
    // Your code goes here.
    PBDBMS.PUT_LINE(SELECT COL1,COL2 FROM my_table);
    end ;

Advantages

  • It is upwardly compatible. If in a future release Oracle supports result sets from stored procedures, you will have to rewrite the stored procedure but you will not have to change the DataWindow or report definition.
  • Sites that use Watcom, Sybase, Informix, as well as Oracle can code applications with stored procedures that work in these DBMSs.

Disadvantages

  • You cannot use bind variables or cached statements.
  • It is slower than a standard SQL SELECT since a special request must be made to obtain the SQL
  • It is not supported in embedded SQL. 
PBL Peeper PB Help PB History
& Future About Us Feedback Site Map

Google
 
Web www.techno-kitten.com
www.sybase.com