How to debug a pipelined function in PL/SQL Developer?

Question

I have a PL/SQL package in oracle database which contains a pipelined function named FN_GET_USERINFO_ROWS as bellow:

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'm running following test script to test pipelined FN_GET_USERINFO_ROWS at 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 is showing following error:

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

How can I debug a pipelined function using PL/SQL Developer ?

diziaq · Accepted Answer · 2016-02-01T11:14:14.893

One of the ways is to create a block with FOR-SELECT-LOOP and put a breakpoint into the function or just log contents for each fetched row (depends on what you mean by debugging). So you can separate each PIPE ROW execution and see its results.
Then in PL/SQL Dev choose File->Open->TestScript and run the block from opened 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;

Also I advise you to debug variants when NO_DATA_NEEDED is being thrown. To do it add WHERE clause limiting number of rows.

How to debug a pipelined function in PL/SQL Developer?

1 Answers1