Just a quick word of warning on a cool feature of SQL*Plus that came along from 18c onwards. I’ve posted before about using supplied database PL/SQL routines to derive the SQL_ID from a SQL statement. In SQL*Plus, that feature is built into the software itself, using an extension to SET FEEDBACK. Here’s an example of that in action
SQL> set feedback on sql_id SQL> select count(*) from emp; COUNT(*) ---------- 14 1 row selected. SQL_ID: g59vz2u4cu404
It works for PL/SQL blocks as well, because these too end up in V$SQL with a SQL_ID.
SQL> begin 2 null; 3 end; 4 / PL/SQL procedure successfully completed. SQL_ID: 5ptswvnju4wkf
But lets look at what happens when you run an invalid piece of SQL. Whilst theoretically it has a SQL_ID (because it is just a hash value derived from the SQL text), we do not get the SQL_ID returned to us.
SQL> select count(*) from emp_wrong_name; select count(*) from emp_wrong_name * ERROR at line 1: ORA-00942: table or view does not exist
That all makese sense, since its unlikely we’d ever need that SQL_ID for any kind of ongoing diagnosis, because we know that the SQL is invalid. However, look what happens when we run a piece of invalid PL/SQL when the SET FEEDBACK ON SQL_ID setting is in effect.
SQL> begin 2 some_nonsense; 3 end; 4 /
No errors are returned. If I reset FEEDBACK to its default, you can see what we were supposed to get back from the client.
SQL> set feedback on SQL> begin 2 some_nonsense; 3 end; 4 / some_nonsense; * ERROR at line 2: ORA-06550: line 2, column 3: PLS-00201: identifier 'SOME_NONSENSE' must be declared ORA-06550: line 2, column 3: PL/SQL: Statement ignored
This impacts versions 19 and 21 of SQL*Plus. I’ll log a bug on this, but until its fixed, take care when combining PL/SQL and SET FEEDBACK ON SQL_ID because a failure of an routine may go unnoticed.
There’s good news if you’re using SQLcl – it does not have the same issue.