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.
sql_stmt2 := lpad(‘x’, 999, ‘x’);
sql_stmt2 := lpad(‘x’, 1000, ‘x’);
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.
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.
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.