The SHOW command allows you to look at the current state of your SQL*Plus environment. You can use it to display the current value of any setting controlled by the SET command. SHOW may also be used to look at current page titles, page footers, and so forth.

SHO[W] setting
       ALL
       BTI[TLE]
       ERR[ORS] [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY|
                  TRIGGER|TYPE|TYPE BODY|VIEW} [owner.]object_name]
       LNO
       PARAMETERS [parameter_name]
       PNO
       REL[EASE]
       REPF[OOTER]
       REPH[EADER]
       SGA
       SPOO[L]
       SQLCODE
       TTI[TLE]
       USER

where:

The following few examples demonstrate how the SHOW command may be used to display the value of one item, such as a setting or a page title:

SQL> SHOW LINESIZE
linesize 80
SQL> SHOW USER
USER is "JEFF"
SQL> SHOW TTITLE
ttitle OFF and is the 1st few characters of the next SELECT statement

The ALL option may be used to display the value of all settings at once. Here is an example:

SQL> SHOW ALL
appinfo is ON and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autotrace OFF
shiftinout INVISIBLE
blockterminator "." (hex 2e)
...

As you can see, when you use SHOW ALL, the settings are displayed in alphabetical order.

The SHOW ERRORS command has more parameters than the other options, so it’s a bit more complex. SHOW ERRORS is used to display error listings for stored procedures, stored functions, packages, triggers, and other such objects. Typically, you first issue a CREATE statement, and then, if errors are reported, you follow that with a SHOW ERRORS command. The following example uses SHOW ERRORS to display an error listing for the most recent creation attempt:

SQL> CREATE OR REPLACE TRIGGER employee_set_key
  2  BEFORE INSERT ON employee
  3  FOR EACH ROW
  4  BEGIN
  5    :new.employee_id := employee_seq.nextval;
  6  END;
  7  /

Warning: Trigger created with compilation errors.

SQL> SHOW ERRORS
Errors for TRIGGER EMPLOYEE_SET_KEY:

LINE/COL
-----------------------------------------------------------------
ERROR
-----------------------------------------------------------------
2/3
PL/SQL: Statement ignored

2/23
PLS-00201: identifier 'EMPLOYEE_SEQ.NEXTVAL' must be declared

SQL>

You can show errors for a specific object by telling SQL*Plus both the object type and the object name:

SQL> CREATE OR REPLACE PROCEDURE JEFF.DISABLE_TRIGGER AS
  2  BEGIN
  3    ALTER TABLE EMPLOYEE DISABLE TRIGGER EMPLOYEE_SET_KEY;
  4  END;
  5  /

Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS PROCEDURE JEFF.DISABLE_TRIGGER
Errors for PROCEDURE JEFF.DISABLE_TRIGGER:

LINE/COL
-----------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------------------
3/3
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:

   begin declare exit for goto if loop mod null pragma raise
   return select update while <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   close current delete fetch lock insert open rollback
   savepoint set sql commit <a single-quoted SQL string>

The error listings stick around even after you end the session in which you tried to create the object. You can come back later, display the errors, and pick up where you left off. But when you do that, you must explicitly name the object you are working with.