📋Steps in this guide1/6

SQL*Plus New Features in Oracle Database 23ai/26ai

This article describes the new features of SQL*Plus in Oracle Database 23ai/26ai.

oracle 23configurationintermediate

by OracleDba

14 views

ARGUMENT Command

The command is described here. - ARGUMENT Command in SQL*Plus 23ai/26ai and SQLcl 22.4

SET ERRORDETAILS Command

The Oracle 23ai/26ai 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. The display of the URL is controlled by the setting. The allowable values are , and , with being the default. We remove the URL using the setting. The setting displays lots of details about the error. The environment variable can be used to set the value of to or . The ability to turn off the URL is a workaround for any backwards compatibility issues.

Code/Command (click line numbers to comment):

SQL> select * from banana;
select * from banana
              *
ERROR at line 1:
ORA-00942: table or view "TESTUSER1"."BANANA" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/

SQL>

SQL> show errordetails
errordetails ON
SQL>

SQL> set errordetails off
SQL> select * from banana;
select * from banana
              *
ERROR at line 1:
ORA-00942: table or view "TESTUSER1"."BANANA" does not exist

SQL>

SQL> set errordetails verbose
SQL> select * from banana;
select * from banana
select * from banana
              *
ERROR at line 1:
ORA-00942: table or view "TESTUSER1"."BANANA" 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>

export ORA_SUPPRESS_ERROR_URL=TRUE

SQL> show errordetails
errordetails OFF
SQL>


export ORA_SUPPRESS_ERROR_URL=FALSE

SQL> show errordetails
errordetails ON
SQL>

PING Command

The command tests network connectivity, in a similar way to the utility. With no additional arguments it tests the current connection. Passing a connection identifier allows it to test that connection using the same listener as the one used by the current connection. 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.

Code/Command (click line numbers to comment):

SQL> ping
Ok (0.204 msec)
SQL>

SQL> ping free
Network service name mapping file: /opt/oracle/product/23ai/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>

SQL> ping listener free
Network service name mapping file: /opt/oracle/product/23ai/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 command displays the cause and action for the specified error message. It works with or without the "-" separator. The is also present in SQLcl. The documentation suggests the error number can be used without the facility name, but that doesn't appear to work in the current release. The command has been amended to display similar information. The following variations all work.

Code/Command (click line numbers to comment):

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>

SQL> oerr 48003
SP2-1666: Unknown component name 48003.
Help: https://docs.oracle.com/error-help/db/sp2-1666/
Cause:  The OERR command did not recognize the specified component name.
Action: Verify the correct component name was used. For example, for the
        error SP2-750 the component name is SP2. Not all components are
        supported by OERR. Refer to the Oracle Database Error message
        documentation for information on those messages.
Usage: OERR
SQL>

help 48003
help ora 48003
help ora-48003

BOOLEAN Support

The Oracle 23ai/26ai version of SQL*Plus now support datatypes.

Code/Command (click line numbers to comment):

SQL> select true, false from dual;

TRUE        FALSE
----------- -----------
TRUE        FALSE

SQL>

SHOW CONNECTION Command

The command displays information about the contents of the "tnsnames.ora" file. With no arguments we display the location of the "tnsnames.ora" file, and the list of Oracle net service names. If we pass a service name as an argument we get the definition of that service. For more information see: - ARGUMENT Command in SQL*Plus 23ai/26ai and SQLcl 22.4 - SET ERRORDETAILS - PING - OERR - HELP Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

SHOW CONN[ECTION] NETS[ERVICENAMES] [net_service_name_1 net_service_name_2 .. net_service_name_n]

SQL> show connection netservicenames
Local Net Naming configuration file: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
FREE
LISTENER_FREE
SQL>

SQL> show connection netservicenames free
Local Net Naming configuration file: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
free = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREE)))
SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!