Need an equivalent function for this T-Sql function in PL/SQL Oracle. If there is other solution then Pipelining

Question

 ALTER  FUNCTION [dbo].[MyFun]()
 RETURNS  TABLE 
 AS
 RETURN 
 (
 WITH addd(CITY_NAME,CITY_ID)
 AS
 (
    select CITY_NAME,CITY_ID from city
 )
  select * from addd
 )

Pipelining is not required because I want to call it direct as called in T-SQL for example

Select * from myfun();

Are you asking how to return have a function return a table in Oracle? — Gordon Linoff, Dec 15 '13 at 17:43
yes it should return a table by direct calling the function not as in http://stackoverflow.com/questions/2829880/create-an-oracle-function-that-returns-a-table — YOusaFZai, Dec 15 '13 at 17:46
If your approach is to select once and access results many times then I would suggest inserting to global temporary table. Why don't you explain more specifically why you are trying to avoid pipelined functions? — Yaroslav Shabalin, Dec 15 '13 at 20:26
Why don't create a view ? Retrieving records from a function is much slower than from a view. — krokodilko, Dec 15 '13 at 22:08

score 3 · Answer 1 · answered Mar 03 '14 at 20:56

You can use the Oracle pipelined function functionality:

Step 1: Create an object the represents one row of the results:

CREATE OR REPLACE TYPE city_type 
AS OBJECT
(
    city_id VARCHAR(6),
    city_name VARCHAR(60)    
);

Step 2: Create a collection (table type) of the object type created in step 1

CREATE OR REPLACE TYPE city_table_type 

AS TABLE OF city_type;

Step 3: Create a function that returns the table type from step 3

CREATE OR REPLACE FUNCTION fnc_Get_Cities_Pipelined
  RETURN city_table_type
  PIPELINED
AS
BEGIN

    FOR v_Rec IN (SELECT * FROM city) LOOP

        PIPE ROW (city_type(v_Rec.City_Id, v_Rec.City_Name));

    END LOOP;

    RETURN;
END;

Step 4: Consume the function as if it was a “table” or “view”

SELECT city_id, city_name
FROM TABLE(fnc_Get_Cities_Pipelined());

Need an equivalent function for this T-Sql function in PL/SQL Oracle. If there is other solution then Pipelining

1 Answers1