print Printer View e-mail E-mail this page Bookmark Bookmark
Purpose
To inform migration specialists of the need to verify the number of refcursors returned from a stored procedure.
Scope & Application
The audience for this document is Oracle partners and customers that are using the Migration Workbench to migrate stored procedures to Oracle.
Verify Number Of Returned Reference Cursors
In Sybase and SQL Server, result sets are returned to the application implicitly. The Oracle Migration Workbench emulates this by passing back a weak ref cursor to the application through an IN OUT parameter.

If 2 select statements are present in the stored procedure code, then 2 reference cursor are passed back. A problem arises when the select statements are in IF statements so only 1 or the other result set is passed back. A sample piece of code is listed below.

In this case, we must only have 1 reference cursor and open both select statements with it.
Before After
PROCEDURE USP_BANK_HIST(
    filterHier       IN VARCHAR2,
    RC1_CALL    IN OUT Omwb_emulation.globalPkg.RCT1,
    RC2_CALL    IN OUT Omwb_emulation.globalPkg.RCT1)
AS

BEGIN

IF filterHier = 'MLIR' THEN
     dummy_var1 := func1(var1,RC1 => RC1_CALL);
ELSE
     dummy_var2 := func2(var1,RC1 => RC2_CALL);
END IF;

END 		PROCEDURE USP_BANK_HIST(
    filterHier       IN VARCHAR2,
    RC1_CALL    IN OUT Omwb_emulation.globalPkg.RCT1)

AS

BEGIN

IF filterHier = 'MLIR' THEN
    dummy_var1 := func1(var1,RC1 => RC1_CALL);
ELSE
    dummy_var2 := func2(var1,RC1 => RC1_CALL);
END IF;

END
E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy