SQL Navigator 6.3.0.1833: VARCHAR2 assignment to > 999 characters

When you create a PL/SQL procedure that tries to assign more than 999 characters to a varchar2(2000), it compiles fine, but then when you try to execute it in SQL Navigator, it throws an “Unknown Error” and fails. It runs correctly in PL/SQL Plus.

Example

PROCEDURE wf_todo2

IS

sql_stmt2 VARCHAR2(2000);

BEGIN
sql_stmt2 := lpad(‘x’, 999, ‘x’);
sql_stmt2 := lpad(‘x’, 1000, ‘x’);

end;

Hi Bill

I was able to reproduce the issue by turning on debug and then using F7/F8 trace over / step into to run the procedure.

I have raised a CR for this to fix in a future release.

Regards
Lidia

Hi Bill,

The API we use to debug PL/SQL (Oracle Probe API) has a limitation of 999 characters max when retrieving the value of a variable. This has nothing to do with the size of the variable (2000 in your example; could be any number from 1000 onwards). The error is produced when debugger is trying to evaluate the values of local variables. We will remove the error message from the Output window in 6.5, however there’s nothing we can do to overcome the Probe API limitation, so the Locals/Watches tabs still won’t be able to display values longer than 999 characters.

Regards,
Roman

PS. You cannot really debug PL/SQL in SQLPlus, so your reference to SQLPlus isn’t valid in this context. If you simply run the procedure in SQL Navigator via F9, you will get the same result as SQL*Plus.