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:
Boston | MA |
San Francisco | CA |
Chicago | IL |
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:
Boston | MA |
San Francisco | CA |
Chicago | IL |
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.