debugging pl/sql function with custom types

Question

I want to use function tq84_select_star_from_table from answer to this question EXECUTE IMMEDIATE in plsql

I created tq84_varchar2_tab,tq84_varchar2_tab_tab types and when I use function like in the answer to question, it works, but when I try to debug function I get this errors and debugging doesn't start.

ORA-06550: Row 13, column 16:

PLS-00382: expression is of wrong type

ORA-06550: Row 13, column  3:

PL/SQL: Statement ignored

When I change type of resultSet and return type of function to VARCHAR2, then debugging starts successfully. I'm using oracle sql developer. I want to know what is wrong. enter image description here

Alex Poole · Accepted Answer · 2014-05-22 17:35:26Z

1

This looks like a bug, or at least an inability to handle custom types as bind variables. As a workaround you can change the generated code to skip the assignment to the bind variable:

Instead of:

:v_Return := v_Return;

set a dummy value:

:v_Return := null;

enter image description here

You can't just comment the old line out as that gets an 'invalid column index' error, which is something to do with it expecting the bind variable to be set - it doesn't like it you don't refer to :v_Return at all.

And of course you have to have recompiled the function for debugging. With that done, and the generated debugger code modified, it stops on breakpoints as expected.

edited May 22, 2014 at 17:35

answered May 22, 2014 at 17:13

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

debugging pl/sql function with custom types

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related