cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle Workaround: PIPELINED

 

SUMMARY

In Oracle, PIPELINED functions return a collections of rows (either a nested table or a varray) that can be queried like a physical database table.

PIPELINED functions are not available in EPAS, but similar functionality can be achieved by using  RETURN  TYPE, RETURN TABLE, and  RETURN SETOF RECORDS. 

EPAS VERSIONS AFFECTED

All
 

ERROR

ERROR:  syntax error at or near "PIPELINED"
 

WORKAROUND

EXAMPLE (ORACLE)

CREATE TYPE t_tf_row AS OBJECT (
  id           NUMBER,
  description  VARCHAR2(50)
);
/

CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;

-- Build a pipelined table function.
CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS
BEGIN
  FOR i IN 1 .. p_rows LOOP
    PIPE ROW(t_tf_row(i, 'Description for ' || i));
  END LOOP;

  RETURN;
END;
/

-- Test it.
SELECT *
FROM   TABLE(get_tab_ptf(10))
ORDER BY id DESC;

        ID DESCRIPTION
---------- --------------------------------------------------
        10 Description for 10
         9 Description for 9
         8 Description for 8
         7 Description for 7
         6 Description for 6
         5 Description for 5
         4 Description for 4
         3 Description for 3
         2 Description for 2
         1 Description for 1

10 rows selected.


EXAMPLE (EPAS)

CREATE TYPE t_tf_row AS OBJECT (
  id           NUMBER,
  description  VARCHAR2(50)
);

CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;

CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN SETOF RECORD
AS
BEGIN
  FOR i IN 1 .. p_rows LOOP
    RETURN NEXT t_tf_row(i, 'Description for ' || i);
  END LOOP;
  RETURN;
END;

hr=# SELECT * FROM get_tab_ptf(10) foo(id numeric, val varchar(50));
 id |        val
----+--------------------
  1 | Description for 1
  2 | Description for 2
  3 | Description for 3
  4 | Description for 4
  5 | Description for 5
  6 | Description for 6
  7 | Description for 7
  8 | Description for 8
  9 | Description for 9
 10 | Description for 10
(10 rows)

-- Using table type:
CREATE OR REPLACE FUNCTION get_tab_ptf_test (p_rows IN NUMBER) RETURN t_tf_tab
AS
DECLARE
   return_Val t_tf_tab;
BEGIN
  return_Val = t_tf_tab();
  FOR i IN 1 .. p_rows LOOP
    return_val.EXTEND;
    return_val(i):= t_tf_row(i, 'Description for ' || i);
  END LOOP;
  RETURN return_Val;
END;

hr=# SELECT * FROM table(get_tab_ptf_test (10));
 id |    description
----+--------------------
  1 | Description for 1
  2 | Description for 2
  3 | Description for 3
  4 | Description for 4
  5 | Description for 5
  6 | Description for 6
  7 | Description for 7
  8 | Description for 8
  9 | Description for 9
 10 | Description for 10
(10 rows)
Version history
Revision #:
1 of 1
Last update:
‎06-13-2017 08:27 PM
Updated by:
 
Labels (4)
Contributors