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:
Is the command, which may be abbreviated to SHO.
Is any one of the settings you can set using the SET command.
Displays an error listing for a stored object. The command SHOW ERRORS by itself causes the error listing for the most recently created object to be displayed. You can get the error listing for a specific object by specifying the object type (function, procedure, and so forth) and the object name.
Used with SHOW ERRORS to specify the object type of interest. This is only necessary if you are specifying the name of the object.
Used with SHOW ERRORS to name the object for which you want to display an error listing.
Displays the current value of one or more initialization parameters. Chapter 10 provides detailed examples of SHOW PARAMETERS in use.
Displays the release number (version) of the Oracle database to which you are connected.
Displays information about the current state of the System Global Area. See Chapter 10 for more information about this option.
Tells you whether or not output is currently being spooled to a file.
Displays the SQL code returned by the most recent SQL statement.
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
2BEFORE INSERT ON employee
3FOR EACH ROW
4BEGIN
5:new.employee_id := employee_seq.nextval;
6END;
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
2BEGIN
3ALTER TABLE EMPLOYEE DISABLE TRIGGER EMPLOYEE_SET_KEY;
4END;
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.