[23ai] SQL*Plus

Today, we’re going to speak about SQL*Plus and it’s new features in 23ai such as config, argument, ping and so on. Everyone really know SQLPlus command-line tool remains the go-to for many DBA in order to iron out every issue in their environments.

So, let's get down to business!

ARGUMENT

Previous to 23ai, when we work with substitution variables in the command-line, these can be set explicitly in SQL*Plus, or passed as arguments our scripts. But from 23ai onwards, we can do it with the new command: argument.

The sintax is the following:

ARGUMENT <VALUE_NUMERIC> [PROMPT/DEFAULT] [HIDE]

Let’s doing a wee bit example in order to see the differences of every option.

• prompt.

SQL> ! vi argument.sql
SQL> ! cat argument.sql
set verify off 
argument 1 prompt "Enter the acount status in order to check:"
Select Username From Dba_Users Where Account_Status='&1';
undefine 1
SQL> @argument.sql
Enter the acount status in order to check:LOCKED

USERNAME
--------------------------------------------------------------------------------
XS$NULL
LBACSYS
OUTLN
DBSNMP
APPQOSSYS
VECSYS
DBSFWUSER
GGSYS
ANONYMOUS
FLOWS_FILES

As we can see, our script is straightforward enough. The first line is in order to not display old and new values and next line is the new command. Here it sees the value that we’ve typed in the command-line.

• default.

SQL> ! vi argument.sql
SQL> ! cat argument.sql
set verify off 
argument 1 default "OPEN"
Select Username From Dba_Users Where Account_Status='&1';
undefine 1

SQL> @argument.sql

USERNAME
--------------------------------------------------------------------------------
SYS
SYSTEM
APEX_PUBLIC_USER
APEX_PUBLIC_ROUTER
AV
PDBADMIN
HR
SYSRAC
ORDS_PUBLIC_USER
ORDS_METADATA

Here it doesn’t display any prompt because we’ve assigned a default value for our variable “argument 1 default "OPEN"“

• hide.

SQL> ! vi argument.sql
SQL> ! cat argument.sql
set verify off 
argument 1 prompt "Enter the acount status in order to check:" hide
Select Username From Dba_Users Where Account_Status='&1';
undefine 1

SQL> @argument.sql
Enter the acount status in order to check:

USERNAME
--------------------------------------------------------------------------------
SYS
SYSTEM
APEX_PUBLIC_USER
APEX_PUBLIC_ROUTER
AV
PDBADMIN
HR
SYSRAC
ORDS_PUBLIC_USER
ORDS_METADATA
CO

As we can see, here it has had a prompt but no way in order to see the value typing. This would be the difference respect to prompt.

PING or -P

To check availability our database.

On one hand, from the command line, we can use -p in order to see quickly whether our database is up or down.

Let’s doing a wee bit example:

SQL> ! sqlplus -p localhost/FREEPDB1
Attempting to contact: (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=FREEPDB1))(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Ok (4704.178 msec)

On the other hand, we can use ping in order to check the current connection. Here it can use the comand with or without arguments.

Again, let’s doing a wee bit example:

SQL> SQL> conn hr/oracle
Connected.
SQL> ping
Ok (0.267 msec)
SQL> disconnect
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.6.0.24.10
SQL> ping
SP2-0640: Not connected

Also, we can use ping with the alias in order to do ping the network listener:

SQL> ping FREEPDB1
Local Net Naming configuration 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 = FREEPDB1)))
Ok (7.032 msec)

In a nutshell, we can say the functionality is like tnsping, don’t you?

ERRORDETAILS

To displays us an url with information about the trouble. That page gives us information more detail about the mistake in order to try to iron out it.

The syntax is the following:

SET ERRORDETAILS [ ON / OFF / VERBOSE]

The default value is ON.

Below you can see a wee bit example:

SQL> Select * From pickle;
Select * From pickle
              *
ERROR at line 1:
ORA-00942: table or view "SYS"."PICKLE" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
SQL> set errordetails off
SQL> r
  1* Select * From pickle
Select * From pickle
              *
ERROR at line 1:
ORA-00942: table or view "SYS"."PICKLE" does not exist
SQL> set errordetails verbose 
SQL> r
  1* Select * From pickle
Select * From pickle
              *
ERROR at line 1:
ORA-00942: table or view "SYS"."PICKLE" 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.

We’re going to see the sequence that we’ve followed.

• First: As we know, the value of default is on. For this reason, we can see in the prompt the tag help with the url in order to get information about ORA error.

• Second: we apply off for our parameter. In this case, we don’t see the tag help, we only see the ORA error.

• Third: we apply verbose for our parameter. In this case, it displays us helpful message information about error.

OERR

This command would display us helpful message information about error.

The syntax is the following:

OERR [ ERROR_CODE ]

Below you can see a wee bit example:

SQL> OERR ORA-01422
Message: "exact fetch returned more than the requested number of rows %s"
Help: https://docs.oracle.com/error-help/db/ora-01422/
Cause: The cause is one of the following:
    1. A SELECT statement was executed in the exact fetch
       mode and returned more rows than requested.
    2. In PL/SQL, a SELECT INTO statement returned more than one row.
    3. In PL/SQL, a DML RETURNING INTO statement
       returned more than one row.
Action: Choose the action corresponding to the cause as numbered:
    1. Increase the number of rows requested to accommodate the
       number of rows returned;
       or omit the exact fetch mode on the fetch call.
    2. In PL/SQL, use a FOR loop to process the rows.
    3. In PL/SQL, use BULK COLLECT to return values into a table.
Params: 1) requested_rows: The number of requested rows.

SHOW CONNECTION

This command would display us information about our tnsnames.ora.

The syntax is the following:

SHOW [ CONNECTION / CONN ] [ NETSERVICENAMES / NETS] SERVICE_NAME

Below you can see a wee bit example:

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

Looking forward to seeing you in the next article.