Oracle 23ai——SQL*Plus新特性

oracle database 23ai发布也有一段时间了，带来了很多具有亮点的新特性。那么今天我们来分享下oracle 23ai最明显的一个新特性。SQLPlus输错命令你会明显的感觉到与之前版本不同了，下面我们来介绍下SQLPlus的新特性！

设置ERRORDETAILS命令

Oracle 23ai 版本的 SQL*Plus 显示许多错误消息的帮助 URL。这些链接提供了错误消息的完整描述以及潜在的操作。

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

SQL> 

URL 的显示由 ERRORDETAILS 设置控制。允许的值为 OFF、ON 和 VERBOSE，其中 ON 为默认值。

SQL> show errordetails
errordetails ON
SQL> 

我们使用OFF参数时，URL就不会提示了

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

SQL> 

如果我们设置VERBOSE将会显示有关错误的详细信息（有点详细）。

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

当然也可以通过环境变量ORA_SUPPRESS_ERROR_URL来实现ERRORDETAILS的值设置，设置为ON或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>

不过我执行下来，似乎有问题

[oracle@localhost admin]$ export ORA_SUPPRESS_ERROR_URL=TRUE
[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Mon May 13 06:19:06 2024
Version 23.4.0.24.05

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> show errordetails 
errordetails ON
SQL> exit
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
[oracle@localhost admin]$  export ORA_SUPPRESS_ERROR_URL=FALSE
[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Mon May 13 06:19:25 2024
Version 23.4.0.24.05

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> show errordetails 
errordetails ON
SQL> 

ping 命令

PING 命令测试网络连接，其方式与tnsping实用程序类似。
没有其他参数，它会测试当前连接。[你可以理解为就是oracle讲tnsping集成到了sqlplus里面，可以用ping代替tnsping命令]。这也是一个进步，我不用切窗口tnsping了。

SQL> ping 192.168.239.254
Local Net Naming configuration file: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
Attempting to contact: (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.239.254)(PORT=1521)))
SP2-1683: Ping failed with error TNS-12541.
Help: https://docs.oracle.com/error-help/db/sp2-1683/
SQL> ping
Ok (0.270 msec)
SQL> ping abc
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 (0.784 msec)
SQL> 

当然如果存在多个监听服务的情况下我们也可以用来测试监听是否正常：

SQL> ping listener free
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 = FREE)))
Ok (0.868 msec)

OERR 命令

之前大家知道OERR 命令显示指定错误消息的原因和操作。现在无论有或没有“-”分隔符，它都可以工作。

SQL> oerr ORA 00600    
Message: "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
Help: https://docs.oracle.com/error-help/db/ora-00600/
Cause: This is the generic internal error number for Oracle program
       exceptions. It indicates that a process has encountered a low-level,
       unexpected condition. The first argument is the internal message
       number. This argument and the database version number are critical in
       identifying the root cause and the potential impact to your system.
SP2-0642: SQL*Plus internal error state 2590, context 45378:32768:0
Help: https://docs.oracle.com/error-help/db/sp2-0642/
Unable to proceed

SQL> oerr ORA-00600
Message: "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
Help: https://docs.oracle.com/error-help/db/ora-00600/
Cause: This is the generic internal error number for Oracle program
       exceptions. It indicates that a process has encountered a low-level,
       unexpected condition. The first argument is the internal message
       number. This argument and the database version number are critical in
       identifying the root cause and the potential impact to your system.
SP2-0642: SQL*Plus internal error state 2590, context 45378:32768:0
Help: https://docs.oracle.com/error-help/db/sp2-0642/
Unable to proceed

ps：这里执行会碰到Unable to proceed，执行窗口无法终止，不知道大家的版本会不会碰到同样问题呢？

HELP命令，三种方式都可以执行出结果，效果一样与OERR

help 00600
help ora 00600
help ora-00600

支持BOOLEAN （布尔）类型

Oracle 23ai 版本的 SQL*Plus 现在支持 BOOLEAN 数据类型。

SQL> select true, false from dual;

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

参考文档：

https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/SET-system-variable-summary.html#GUID-E9FD57A8-26FA-4E13-9D57-B68818330E33

https://docs.oracle.com/en/database/oracle/oracle-database/23/lnoci/miscellaneous-functions.html#GUID-4B99087C-74F6-498A-8310-D6645172390A