RETURN TABLE and Pipelined Function - SQL Server to Oracle Migration

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:

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:

For more information, see SQL Server to Oracle Migration.