Processing Result Sets in a SQL Procedure - IBM DB2 to Oracle Migration

In DB2, a stored procedure can process result set(s) returned by another stored procedure using RESULT_SET_LOCATOR type, ASSOCIATE RESULT SET LOCATORS and ALLOCATE CURSOR FOR RESULT SET statements.

In Oracle you can use SYS_REFCURSOR type to pass result sets between stored procedures.

First let's create a stored procedure that returns a result set from DB2:

DB2:

  --#SET TERMINATOR @
 
  CREATE PROCEDURE sp_colors
  BEGIN
  -- Cursor with RETURN TO CALLER clause
  DECLARE cur CURSOR WITH RETURN TO CALLER 
     FOR SELECT name FROM colors;
 
  -- Open the cursor and exit from procedure, so the result set is returned to the caller
  OPEN cur;
  END @

Now let's create a stored procedure that process the result set returned from sp_colors procedure:

DB2:

  --#SET TERMINATOR @
 
  CREATE PROCEDURE sp_colors_read (OUT p_name VARCHAR(30)) 
  BEGIN
    DECLARE result RESULT_SET_LOCATOR VARYING;
 
    -- Call the procedure returning the result set
    CALL sp_colors();
 
    -- Get a cursor for the result set
    ASSOCIATE RESULT SET LOCATOR (result) WITH PROCEDURE sp_colors;
    ALLOCATE cur CURSOR FOR RESULT SET result;
 
    -- Cursor already open, so you can start fetching rows
    FETCH cur INTO p_name;
    -- ...
    CLOSE cur;    
  END @

The corresponding stored procedure that returns a result set from Oracle:

Oracle:

  CREATE OR REPLACE PROCEDURE sp_colors (cur OUT SYS_REFCURSOR)
  AS
  BEGIN
  -- Open OUT cursor and exit
  OPEN cur FOR SELECT name FROM colors;
  END;
  /

Now you can create an Oracle PL/SQL stored procedure that process the result set returned from sp_colors procedure:

Oracle:

  CREATE OR REPLACE PROCEDURE sp_colors_read (p_name OUT VARCHAR2) IS
    cur SYS_REFCURSOR;
  BEGIN
    -- Call the procedure returning the result set
    sp_colors(cur);
 
    -- Cursor already open, so you can start fetching rows
    FETCH cur INTO p_name;
    -- ...
    CLOSE cur;    
  END;
  /

• IBM DB2 to Oracle Migration Tools and Reference
• Online SQL Conversion Tool

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - September 2013.