8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
SQL*Plus New Features in Oracle Database 23c
This article describes the new features of SQL*Plus in Oracle Database 23c.
Related articles.
ARGUMENT Command
The ARGUMENT command is described here.
SET ERRORDETAILS Command
The Oracle 23c version of SQL*Plus displays a help URL for many error messages. These links provide a full description of the error message, as well as potential actions. The amount of information available varies for each error message.
SQL> select * from banana;
select * from banana
*
ERROR at line 1:
ORA-00942: table or view does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
SQL>
The display of the URL is controlled by the ERRORDETAILS setting. The allowable values are OFF, ON and VERBOSE, with ON being the default.
SQL> show errordetails errordetails ON SQL>
We remove the URL using the OFF setting.
SQL> set errordetails off
SQL> select * from banana;
select * from banana
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
The VERBOSE setting displays lots of details about the error.
SQL> set errordetails verbose
SQL> select * from banana;
select * from banana
*
ERROR at line 1:
ORA-00942: table or view does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
Cause: The specified table or view did not exist, or a synonym
pointed to a table or view that did not exist.
To find existing user tables and views, query the
ALL_TABLES and ALL_VIEWS data dictionary views. Certain
privileges may be required to access the table. If an
application returned this message, then the table that the
application tried to access did not exist in the database, or
the application did not have access to it.
Action: Check each of the following:
- The spelling of the table or view name is correct.
- The referenced table or view name does exist.
- The synonym points to an existing table or view.
If the table or view does exist, ensure that the correct access
privileges are granted to the database user requiring access
to the table. Otherwise, create the table.
Also, if you are attempting to access a table or view in another
schema, make sure that the correct schema is referenced and that
access to the object is granted.
Params: 1) object_name: The table or view name specified as
SCHEMA.OBJECT_NAME, if one is provided.
Otherwise, it is blank.
SQL>
The ORA_SUPPRESS_ERROR_URL environment variable can be used to set the value of ERRORDETAILS to ON or OFF.
export ORA_SUPPRESS_ERROR_URL=TRUE SQL> show errordetails errordetails OFF SQL> export ORA_SUPPRESS_ERROR_URL=FALSE SQL> show errordetails errordetails ON SQL>
The ability to turn off the URL is a workaround for any backwards compatibility issues.
PING Command
The PING command tests network connectivity, in a similar way to the tnsping utility.
With no additional arguments it tests the current connection.
SQL> ping Ok (0.204 msec) SQL>
Passing a connection identifier allows it to test that connection using the same listener as the one used by the current connection.
SQL> ping free Network service name mapping file: /opt/oracle/product/23c/dbhomeFree/network/admin/tnsnames.ora Attempting to contact: (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREE))) Ok (38.743 msec) SQL>
Passing the listener name and the connection identifier allows it to test using an alternative listener, assuming more than one is running on the server.
SQL> ping listener free Network service name mapping file: /opt/oracle/product/23c/dbhomeFree/network/admin/tnsnames.ora Attempting to contact: (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREE))) Ok (5.992 msec) SQL>
OERR Command
The OERR command displays the cause and action for the specified error message. It works with or without the "-" separator. The OERR is also present in SQLcl.
SQL> oerr ORA 48003
Message: "out of process memory"
Help: https://docs.oracle.com/error-help/db/ora-48003/
Document: YES
Cause: Operating system memory was exhausted or a per-process limit
on private memory usage was reached. The database has an internal
limit of 32 GB per Oracle process.
Action: Check the use of memory by other Oracle instances and other
applications. Exceeding 32 GB in a single process may indicate a
memory leak. Consider reducing the size of shared memory to allow
more room for private memory.
Comment: The ORA facility mapping for this error is ORA-04030.
SQL>
SQL> oerr ORA-48003
Message: "out of process memory"
Help: https://docs.oracle.com/error-help/db/ora-48003/
Document: YES
Cause: Operating system memory was exhausted or a per-process limit
on private memory usage was reached. The database has an internal
limit of 32 GB per Oracle process.
Action: Check the use of memory by other Oracle instances and other
applications. Exceeding 32 GB in a single process may indicate a
memory leak. Consider reducing the size of shared memory to allow
more room for private memory.
Comment: The ORA facility mapping for this error is ORA-04030.
SQL>
The documentation suggests the error number can be used without the facility name, but that doesn't appear to work in the current release.
SQL> oerr 48003 SP2-1666: Unknown component name 48003. Help: https://docs.oracle.com/error-help/db/sp2-1666/ Usage: OERRSQL>
The HELP command has been amended to display similar information. The following variations all work.
help 48003 help ora 48003 help ora-48003
BOOLEAN Support
The Oracle 23c version of SQL*Plus now support BOOLEAN
datatypes.
SQL> select true, false from dual; TRUE FALSE ----------- ----------- TRUE FALSE SQL>
For more information see:
Hope this helps. Regards Tim...