Toad World® Forums

Newbie: Comparing Pipelined Table Functions


#1

Hi there,

I just started using CodeTester. I need to compare piplined table functions, but can’t find an example how to do it. When I try to compare them using collections i get errors running the generated code.

Could someome provide an example?


#2

Pipelined table functions can only be called from within a query. We do not offer direct support for testing this kind of function yet.

So you will need to build a wrapper procedure or function around the pipelined function to return the dataset returned by the pipelined function, and then test that dataset (most likely a cursor variable. Here is an example…

CREATE TYPE tickertype AS OBJECT (
ticker VARCHAR2 (20)
, pricedate DATE
, pricetype VARCHAR2 (1)
, price NUMBER
);
/

CREATE TYPE tickertypeset AS TABLE OF tickertype;
/

CREATE OR REPLACE PACKAGE refcur_pkg
IS
TYPE refcur_t IS REF CURSOR
RETURN stocktable%ROWTYPE;
END refcur_pkg;
/

CREATE OR REPLACE FUNCTION stockpivot (dataset refcur_pkg.refcur_t)
RETURN tickertypeset PIPELINED

END stockpivot;
/

/* The wrapper function, which can be directly tested. */

CREATE OR REPLACE FUNCTION run_stockpivot (dataset refcur_pkg.refcur_t)
RETURN sys_refcursor
IS
cv sys_refcursor;
BEGIN
OPEN cv FOR SELECT * FROM (TABLE (stockpivot (dataset));
RETURN cv;
END stockpivot;
/