How to debug pipeline function in PL / SQL Developer?

Question

I have a PL / SQL package in an oracle database that contains a pipeline function called FN_GET_USERINFO_ROWS as below:

CREATE OR REPLACE PACKAGE PKG_USERINFO AS TYPE TY_USERINFO_RECORD IS RECORD( U_ID VARCHAR2(50), U_NAME VARCHAR2(50), DOB DATE); TYPE TY_USERINFO_TABLE IS TABLE OF TY_USERINFO_RECORD; FUNCTION FN_GET_USERINFO_ROWS(P_USER_ID IN NUMBER) RETURN TY_USERINFO_TABLE PIPELINED; END PKG_USERINFO; 

And I run the following test script to check the pipelined FN_GET_USERINFO_ROWS on PL / SQL Developer (File-> New-> Test Window)

 declare result PKG_USERINFO.TY_USERINFO_TABLE; begin -- calling pipelined function result := PKG_USERINFO.FN_GET_USERINFO_ROWS(P_USER_ID => :P_USER_ID); end; 

But it shows the following error:

ORA-06550: row 28, column 12: PLS-00653: aggregate / table functions are not allowed in the PL / SQL area

How to debug pipeline function using PL / SQL Developer ?

Answer 1

One way is to create a block with FOR-SELECT-LOOP and place a breakpoint in a function or just the contents of the log for each row selected (depending on what you mean by debugging). Thus, you can split each PIPE ROW execution and see its results.
Then in PL / SQL Dev select File-> Open-> TestScript and run the block from an open window.

 DECLARE result pkg_userinfo.ty_userinfo_table; BEGIN -- we call pipelined functions like this FOR rec IN (SELECT * FROM TABLE (pkg_userinfo.fn_get_userinfo_rows(:P_USER_ID)) -- WHERE rownum < 2 -- uncomment this line and vary amount of fetched rows ) LOOP dbms_output.put_line('another step : ' || rec.u_id); END LOOP; END; 

I also advise you to debug options when NO_DATA_NEEDED is NO_DATA_NEEDED . To do this, add a WHERE that limits the number of rows.