How to clear error message buffer in SQLPlus?

Question

There's an issue when installing several objects via SQL*Plus.

Let's install two objects: package A and view B, and A has compilation errors

SET FEEDBACK OFF

prompt install PACKAGE_A spec
create or replace package package_a is
  procedure p;
end;
/
show errors

prompt install PACKAGE_A body
create or replace package body package_a is
  procedure p is begin hello; end;
end;
/
show errors

prompt install VIEW_B
create or replace view view_b as
  select * from dual;

show errors

Thus we get in the output:

install PACKAGE_A spec
No errors.

install PACKAGE_A body

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY PACKAGE_A:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/24     PL/SQL: Statement ignored
2/24     PLS-00201: identifier 'HELLO' must be declared

install VIEW_B
Errors for PACKAGE BODY PACKAGE_A:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/24     PL/SQL: Statement ignored
2/24     PLS-00201: identifier 'HELLO' must be declared

I had faced this problem not only in a "view after package" case, but currently I can not reproduce it in a short example for a "package after package" case (which bothers me mostly). So it seems to not appear for all types of compilation errors.

---

Documentation says When you specify SHOW ERRORS with no arguments, SQL*Plus shows compilation errors for the most recently created or altered stored procedure.

It is also clear that there is an option of specifying a particular object

SHOW ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY ...} [schema.]name]

But it does not look usable, because the requirement of giving an object type overcomplicates the situation (it is not obvious why the command cannot be satisfied with an object name alone).

Could you help me find a way to clear the buffer of SHOW ERRORS command, so it could be set in a pristine state before every object compilation?

Answer 1

If there is a way, I don't know it.

SHOW ERRORS is a SQL*Plus command. As fara as I can tell, it fetches data from USER_ERRORS (or, possibly, ALL_ERRORS or DBA_ERRORS). Its description is

SQL> desc user_errors;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE                                               VARCHAR2(12)
 SEQUENCE                                  NOT NULL NUMBER
 LINE                                      NOT NULL NUMBER
 POSITION                                  NOT NULL NUMBER
 TEXT                                      NOT NULL VARCHAR2(4000)
 ATTRIBUTE                                          VARCHAR2(9)
 MESSAGE_NUMBER                                     NUMBER

If I do something like this:

SQL> create or replace procedure ptest is
  2    l_Res number;
  3  begin
  4    l_res := 1 * A;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE PTEST:

LINE/COL ERROR
-------- --------------------------------------------------------
4/3      PL/SQL: Statement ignored
4/16     PLS-00201: identifier 'A' must be declared

then SHOW ERR returns the last errors I got. Does it mean that USER_ERRORS doesn't contain something else? No:

SQL> select name, line, substr(text, 1, 50) text from user_Errors;

NAME               LINE TEXT
------------ ---------- --------------------------------------------------
PTEST                 4 PL/SQL: Statement ignored
PTEST                 4 PLS-00201: identifier 'A' must be declared
ADD_COURSE            0 PL/SQL: Compilation unit analysis terminated
ADD_COURSE            2 PLS-00201: identifier 'COURSE.TILTE' must be decla

SQL>

So, can I simply delete its contents? Ideally, I'd do that between every two CREATE PROCEDURE (or whatever) statements in that .SQL script:

SQL> delete user_errors;
delete user_errors
       *
ERROR at line 1:
ORA-01031: insufficient privileges

Nope, I can't do that.

Therefore, I think you'll have to use that not-very-usable option and specify whose errors you want to see.