RETURN WITH RESUME and Pipelined Function - Informix to Oracle Migration

In Informix you can create a stored procedure that returns multiple rows using RETURN WITH RESUME statement. This allows you to execute the procedure in the FROM clause of a SELECT statement. When you convert such procedures to Oracle you can use a pipelined function (table function).

Let's create a simple example to demonstrate how to return multiple rows from an Informix procedure:

Informix:

  -- Iterator procedure returning 3 rows to caller
  CREATE PROCEDURE cities_list()
      RETURNING VARCHAR(30), CHAR(2)
 
     -- Return rows
     RETURN 'Boston', 'MA' WITH RESUME;
     RETURN 'San Francisco', 'CA' WITH RESUME;
     RETURN 'Chicago', 'IL' WITH RESUME;
  END PROCEDURE;

Now you can execute this procedure in SELECT statement as follows:

Informix:

  -- Calling iterator procedure in FROM clause
  SELECT * FROM TABLE(cities_list());

Result:

In Oracle you can create a pipelined function that uses PIPE ROW statement to define rows returned to the caller. Before you create the function, you have to create an object and table that describe the returned data:

Oracle:

  -- Row
  CREATE OR REPLACE TYPE cities_list_row AS OBJECT (name VARCHAR2(30), state CHAR(2));  
  /
  -- Table 
  CREATE OR REPLACE TYPE  cities_list_tab AS TABLE OF cities_list_row; 
  /

Now you can create a pipilened function as follows:

Oracle:

  -- A table function returning 3 rows to caller
  CREATE OR REPLACE FUNCTION cities_list
      RETURN cities_list_tab PIPELINED
  AS
  BEGIN
     -- Return rows
     PIPE ROW (cities_list_row('Boston', 'MA'));
     PIPE ROW (cities_list_row('San Francisco', 'CA'));
     PIPE ROW (cities_list_row('Chicago', 'IL'));
  END;
  /

Now similar to Informix WITH RESUME procedure you can execute this function in SELECT statement as follows:

Oracle:

  -- Calling a table function in FROM clause
  SELECT * FROM TABLE(cities_list());

Result:

• Informix 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 - August 2013.