Careful with SQL*Plus and SQL_ID feedback

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.