In SQL Server a user-defined function can return a table (multiple rows). This allows you to execute a function in the FROM clause of a SELECT statement.
In Oracle you can use a pipelined function.
SQL Server:
-- Iterator procedure returning 3 rows to caller CREATE FUNCTION cities_list() RETURNS TABLE AS RETURN SELECT 'Boston' AS city UNION ALL SELECT 'San Francisco' AS city UNION ALL SELECT 'Chicago' AS city GO
Now you can execute this function in SELECT statement as follows:
SQL Server:
-- Calling table function in FROM clause SELECT * FROM cities_list();
Result:
Boston |
San Francisco |
Chicago |
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 (city VARCHAR2(30)); / -- Table CREATE OR REPLACE TYPE cities_list_tab AS TABLE OF cities_list_row; /
Note that if the table function returns only single VARCHAR2 column you can use the existing ODCIVARCHAR2LIST (varray of VARCHAR2) object type in Oracle so you do not need to create your own type.
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')); PIPE ROW (cities_list_row('San Francisco')); PIPE ROW (cities_list_row('Chicago')); END; /
Now 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 |
San Francisco |
Chicago |
For more information, see SQL Server to Oracle Migration.