Hi, I found an interesting edge case in exception handling in SQL Dev for VS Code running against an Oracle 19c database. I am running version 24.3, and using an anonymous bloc, that calls a small procedure that accepts a number ID_client as a parameter, it is doing a SELECT INTO and issuing a DBMS_OUTPUT.put_line with the result. It is working great if we don't hit an exception, if we pass a non existent ID, on version 19c (Version 19.19.0.0.0) of the db we get an odd error in VS Code script output:
BEGIN
*
ERROR at line 1:
null
More Details :
(there is no detail this is me just pointing it out)
The same thing happen in the embedded version (24.3) of SQLcl in VS Code.
Doing the same thing (connected to db 19c) in SQL Developper 23.1.1.345 or standalone SQLcl 24.2 produces the expected results:
BEGIN
PROC_AFFI_CLIENT(22);
END;
Error report -
ORA-01403: no data found
ORA-06512: at "COMMANDE.PROC_AFFI_CLIENT", line 6
ORA-06512: at line 2
01403. 00000 - "no data found"
*Cause: No data was found from the objects.
*Action: There was no data from the objects which may be due to end of fetch.
More Details :
https://docs.oracle.com/error-help/db/ora-01403/
https://docs.oracle.com/error-help/db/ora-06512/
I did the same tests on SQL Dev for VS Code with db 23ai (23.5.0.24.07) and the error is almost correct still this odd BEGIN * that starts the error but the stack trace now shows with the proper ORA-1403 error.:
BEGIN
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "COMMANDE.PROC_AFFI_CLIENT", line 6
ORA-06512: at line 2
https://docs.oracle.com/error-help/db/ora-01403/
More Details :
https://docs.oracle.com/error-help/db/ora-01403/
https://docs.oracle.com/error-help/db/ora-06512/
In short the issue is the combination of 19c database and SQL Dev for VS Code (including embeded SQLcl) that does not return the stack trace when hitting an exception.
Thanks
Jean-François