Debugging pipelined functions

Toad for Oracle - Beta
1

Are you under the impression that the debugger can debug pipelined functions in a package? I have been trying to use this for years and it still gives an almost immediate probe timeout.

14:32:50 Info: Starting debug session for *******
14:32:51 Info: Probe version 2.5 is being used
14:32:57 Error: ORA-06550: line 12, column 13: PLS-00653: aggregate/table functions are not allowed in PL/SQL scope
14:33:06 Info: Ending debug session for *******
14:33:06 Info: Done

2

Do you have an example that fails? Does it also fail outside of Toad as well? The probe debugger is rather limited. More recent versions of Toad include the JDWP debugger in them as well but that requires a bit of work to setup on the database.

3

As simple as it gets... compile the package and body, then execute the select. It works. Now debug the package and try to step into it. This is with 13.3.0.132, or basically any version GA or beta and a 12.1.0.2 database instance.

CREATE OR REPLACE PACKAGE DG_ABPPTM.test_pkg AS
TYPE rec_t IS RECORD(rec_date DATE);
TYPE tab_t IS TABLE OF rec_t;

FUNCTION test_debug
RETURN tab_t
PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY DG_ABPPTM.test_pkg AS
FUNCTION test_debug
RETURN tab_t
PIPELINED AS
BEGIN
FOR rec IN (SELECT SYSDATE FROM DUAL)
LOOP
PIPE ROW (rec);
END LOOP;
END;
END;
/

SELECT * FROM TABLE(test_pkg.test_debug);

4

After a few quick tests this seems to be a limitation of Oracle's debugger.

5

I had no problem debugging with TOAD for Oracle. I took your package, created a wrapper procedure to call it, then debugged the wrapper procedure. I was able to step into the table function. Here is the code for the wrapper procedure. Edit: I forgot to make clear I am not using the Beta release of TOAD, I am using 13.2.0.258.

CREATE OR REPLACE PROCEDURE test_proc AS
BEGIN
-- changed original package name to deleteme_pkg, no other changes made
FOR eachrow IN (SELECT * FROM TABLE( deleteme_pkg.test_debug ))
LOOP
dbms_output.put_line(eachrow.rec_date);
END LOOP;
END;

1 Like
6

Thanks Brian - I had only tested the snippet without creating a wrapper.

7

That appears to work. Outstanding! Thanks for the help!