# [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**. ```plaintext 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**. ```plaintext 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**. ```plaintext 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: ```plaintext 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: ```plaintext 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: ```plaintext 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: ```plaintext 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: ```plaintext 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: ```plaintext 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.