Powering on with SQL*Plus for Oracle Database 23ai

Published in

Oracle Developers

8 min readMay 8, 2024

With the fantastic announcement that Oracle Database 23ai is now generally available, I wanted to share some SQL*Plus 23ai enhancements.

Oracle’s traditional SQL*Plus command-line tool remains the go-to for many Oracle Database users. It lets you execute ad-hoc SQL statements against Oracle Database, or create scripts of statements to run. It is included in all database software installations, and is easily installed on other machines using the small, free Oracle Instant Client ZIP (or RPM) packages. No click-through, no heavy-weight runtime. You do a quick unzip, set PATH or LD_LIBRARY_PATH and start using it. (Linux installation instructions are here).

Here are some new SQL*Plus 23ai features.

Support for the VECTOR data type

A major feature of Oracle Database 23ai is AI Vector Search (which is part of the database and is available at no additional charge in Enterprise Edition, Standard Edition 2, Database Free, and all Oracle Database cloud services). SQL*Plus 23ai supports this feature with its native capabilities for binding and fetching the new VECTOR data type. For example, given the table:

SQL> drop table if exists mytab_v;

SQL> create table mytab_v (v64 vector(3, float64));

SQL> describe mytab_v

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 V64                                                VECTOR(3, FLOAT64)

You can easily insert data:

SQL> variable myvar vector = '[1.1, 2.2, 3.0]'

SQL> insert into mytab_v values(:myvar);

1 row created.

Selecting the row back returns the vector in appropriate floating point format:

SQL> select * from mytab_v;

V64
--------------------------------------------------------------------------------
[1.1000000238418579E+000,2.2000000476837158E+000,3.0E+000]

Support for the SQL BOOLEAN data type

Oracle Database 23ai’s new SQL BOOLEAN data type lets you store true and false values in database tables. (Previously this type was only available in PL/SQL).

For example, with the table:

SQL> drop table if exists mytab_b;

Table dropped.

SQL> create table mytab_b (b boolean);

Table created.

SQL> describe mytab_b

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 B                                                  BOOLEAN

You can then insert and query back boolean values:

SQL> var b boolean = true

SQL> insert into mytab_b (b) values (:b);

1 row created.

SQL> select * from mytab_b;

B
-----------
TRUE

A new command line -P option and PING command

One common task for database users is to check that the database is contactable, or a connection is still valid. A new SQL*Plus 23ai -P command line option and PING command make this easy . Documentation is here and here.

From the command line, you can use -P (case insensitive) to check that the Oracle Net network listener for a database service is up and running:

$ sqlplus -p localhost/orclpdb1
Attempting to contact: (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=orclpdb1))(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Ok (36.858 msec)

To check the validity of the current connection use PING at the SQL*Plus 23ai prompt. This confirms the listener and database are accessible with this connection:

SQL> connect cj/cj@localhost/orclpdb1
Connected.

SQL> ping
Ok (3.093 msec)

SQL> disconnect
Disconnected from Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.4.0.24.05

SQL> ping
SP2-0640: Not connected
Help: https://docs.oracle.com/error-help/db/sp2-0640/

When using PING inside SQL*Plus 23ai, you can optionally ping just the network listener:

SQL> ping localhost/orclpdb1
Attempting to contact: (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=orclpdb1))(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Ok (6.463 msec)

Initially it may seem odd that pinging just the listener is slower than when you issue a bare PING command which checks both the listener and the database. This is because ping localhost/orclpdb1 creates a new connection, whereas a bare ping reuses the already established connection.

The new ping functionality means it is no longer necessary to use a workaround or find Oracle’s tnsping command (which is not included in Instant Client) to check database availability.

A new ARGUMENT command

SQL*Plus substitution variables are a way of passing variable values into SQL*Plus commands. The variable values can be set explicitly in SQL*Plus, or passed as arguments to command line scripts.

SQL*Plus 23ai extends its support for substitution variables used for scripts with a new ARGUMENT command to set default values for script arguments.

The syntax is:

ARGUMENT argument_number [PROMPT text] [DEFAULT text] [HIDE]

Documentation is here.

If the script t.sql contains:

set verify off
argument 1 default "123"
select &1 as myval;
quit

Then, if you want to use a specific value for the argument, a command line parameter value can be passed, just like in previous versions:

$ sqlplus -s cj/cj@localhost/orclpdb1 @t 456

     MYVAL
----------
       456

But when you execute it without providing an argument, the ARGUMENT default value is used for the substitution variable:

$ sqlplus -s cj/cj@localhost/orclpdb1 @t

     MYVAL
----------
       123

The ARGUMENT command PROMPT option lets you specify a nice prompt if you want to prompt for a value at runtime, and there is a HIDE option so any text entered at a prompt is not displayed to the screen.

A new SET ERRORDETAILS setting

One recent database group project was a review and rewording of common database error messages. An error portal now contains database messages with their Cause and Action help information. SQL*Plus 23ai will show a link to the relevant portal entry when printing database error messages:

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

You can alter the output with the new SET ERRORDETAILS feature.

The syntax is:

SET ERRORDETAILS { OFF | ON | VERBOSE }

Documentation is here.

For example, to turn display of the help URL off:

SQL> set errordetails off

SQL> select * from fred;
select * from fred
              *
ERROR at line 1:
ORA-00942: table or view "CJ"."FRED" does not exist

(For Oracle Call Interface applications like SQL*Plus, the URL displayed by the database with ORA errors can also be suppressed by setting a new environment variable export ORA_SUPPRESS_ERROR_URL=TRUE)

A new OERR command and HELP error option

For users who prefer local help, SQL*Plus 23ai has a new OERR command to display helpful message information about errors. The syntax is:

OERR [error_code|<facility name> <error number>]

Documentation is here

For example:

SQL> oerr ORA-00942

Message: "table or view%s 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.

The HELP command has been enhanced with the same capability:

SQL> help ora-1017

Message: "invalid credential or not authorized; logon denied"
Help: https://docs.oracle.com/error-help/db/ora-01017/
Cause:  An invalid credential was provided when accessing the Oracle
        Database or you were not authorized to access this database.
Action: Depending on your authentication method, ensure that the correct
        credential is provided when logging in to Oracle Database.
        Retry your credential after checking it. If your credential is
        correct, you may not be authorized to access the database.
        You will need to contact your database administrator or
        identity management administrator to confirm that you are
        authorized to access the database.

A new SHOW CONNECTION command

If you connect using a network service name from a local tnsnames.ora file, the new SHOW CONNECTION command will list the Oracle Net service names present in that tnsnames.ora file. It can also show the related "full" connect descriptor for a given alias.

The syntax is:

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

Documentation is here

For example, if you have a file $HOME/test/tnsnames.ora on the "client" side, i.e. accessible on the file system where SQL*Plus is running:

$ export TNS_ADMIN=$HOME/test

$ cat $TNS_ADMIN/tnsnames.ora

WOMBAT =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = WOMBAT)
 )
)

KOALA =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1522))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = KOALA)
 )
)

Then in SQL*Plus 23ai you can check the available aliases:

$ sqlplus /nolog
. . .
SQL> show connection netservicenames
Local Net Naming configuration file: /home/cjones/test/tnsnames.ora
WOMBAT
KOALA

You can then use an alias to connect:

SQL> connect cj@wombat

Or you can then drill down and see all the Oracle Net settings that an alias uses:

SQL> show connection netservicenames koala
Local Net Naming configuration file: /home/cjones/test/tnsnames.ora
koala = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = KOALA)))

A new CONFIG command

Oracle Database connection configuration information can now be stored in a Centralized Configuration Provider. This makes it easy to distribute connection information to large numbers of client machines. The supported providers are Azure App Configuration Store and OCI Object Storage.

The new SQL*Plus 23ai CONFIG command will read an exisiting tnsnames.ora file and generate JSON syntax suitable for use in a centralized configuration provider.

The syntax is:

CONFIG EXPORT TNS [<tnsnames.ora file location>] [ AZURE ] [ [ FILE ] [<JSON filename>] ]

Documentation is here.

By default the format for OCI Object Storage is created:

SQL> config export tns
Generating config store JSON for Local Net Naming configuration file /home/cjones/test/tnsnames.ora

{
    "WOMBAT": {
        "connect_descriptor": "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = WOMBAT)))"
    },
    "KOALA": {
        "connect_descriptor": "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = KOALA)))"
    }
}

You can use this to construct your entries for OCI Object Storage, which, with any other optional configuration information such as the username also stored, can then be used when connecting, for example:

$ sqlplus /@config-ociobject://objectstorage.us.phoenix1.oraclecloud.com/n/xxxxxxx/sales

DESCRIBE support for column annotations

Annotations provide a mechanism to store application metadata about tables centrally in the database so that they can be shared across applications, modules and microservices.

The SQL*Plus 23ai DESCRIBE command can show column annotations:

SQL> create table annotation_tab
    (c1 number annotations(empgroup2 'Emp_Info', hidden),
     c2 number primary key);

Then use SET DESCRIBE to enable this additional annotation information to be shown (note you might need to scroll the code pane below to see the new information on the right of the output):

SQL> set describe annotation on

SQL> describe annotation_tab
 Name                                    Null?    Type                           Annotations (Key: Value)
 --------------------------------------- -------- ------------------------------ ---------------------------------------
 C1                                               NUMBER                         EMPGROUP2: Emp_Info
                                                                                 HIDDEN:
 C2                                      NOT NULL NUMBER

DESCRIBE support for domains

A use case domain is high-level dictionary object that belongs to a schema and encapsulates a set of optional properties and constraints. In Oracle Database 23ai you can define table columns to be associated with a domain, thereby explicitly applying the domain’s optional properties and constraints to the columns.

The SQL*Plus 23ai DESCRIBE command can now show domain information associated with columns:

Name                Null?   Type
------------------  -----   -----------------------------
CUST_EMAIL                  VARCHAR2(100) DOMAIN EMAIL

Summary

Oracle Database 23ai has many great new database, SQL and PL/SQL features that you can access through SQL*Plus. The Oracle Database New Features is a very impressive read.

The Oracle Database 23ai and SQL*Plus 23ai enhancements make development easier, and let you be more productive and effective at your tasks. To try it out, you can install the database locally or access it via the Free edition in Oracle Cloud.

SQL*Plus remains the most popular scripting interface for Oracle Database, with a capable, easy to use, and solid feature set. The new enhancements continue a long tradition of developer features.

Finally, if you want more powerful scripting capabilities, I strongly recommend looking at our python-oracledb driver for Python. This is a 'Thin' driver that installs into Python with one command and is immediately usable. Check out the most recent release announcement for its feature set that supports Oracle Database 23ai.