CODEWITHSUNDEEP
sqloracle-databasedebuggingplsqloracle-sqldeveloper
Lavandil
Lavandil

Reputation: 153

debugging pl/sql function with custom types

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

Upvotes: 1

Views: 1717

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

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.

Upvotes: 1

Related Questions