SQL*Plus Enhancements

PDB Support

SQL*Plus includes support for the multitenant architecture.

The SHOW command displays information about PDBs.

SQL> SHOW CON_ID

CON_ID
------------------------------
1
SQL> SHOW CON_NAME

CON_NAME
------------------------------
CDB$ROOT
SQL> SHOW PDBS

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO

Command History

Before we can use the HISTORY command we have to make sure it is turned on. In the example below we attempt to use the HISTORY command with no options and we are told the HISTORY command is not enabled, so we enable it as instructed.

SQL> HISTORY
SP2-1650: History is off, use "SET HIST[ORY] ON" to enable History.
SQL> SET HISTORY ON
SQL> HISTORY
SP2-1651: History list is empty.
SQL>

Checking the help for the HISTORY command, we get the following usage.

SQL> HELP HISTORY

 HISTORY
 -------

 Stores, lists, executes, edits of the commands
 entered during the current SQL*Plus session.

 HIST[ORY] [N {RUN | EDIT | DEL[ETE]}] | [CLEAR]

 N is the entry number listed in the history list.
 Use this number to recall, edit or delete the command.

 Example:
 HIST 3 RUN - will run the 3rd entry from the list.

 HIST[ORY] without any option will list all entries in the list.


SQL>

Let’s put some statements in the history and try a few commands.

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT SYSDATE FROM dual;
SELECT 'Banana' FROM dual;

SQL> HISTORY
  1  ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
  2  SELECT SYSDATE FROM dual;
  3  SELECT 'Banana' FROM dual;

SQL>


SQL> HISTORY 2 RUN

SYSDATE
--------------------
22-APR-2017 13:49:41

SQL>


SQL> HISTORY 1 DELETE
SQL> HISTORY
  1  SELECT SYSDATE FROM dual;
  2  SELECT 'Banana' FROM dual;

 

MARKUP CSV

The MARKUP option now includes a CSV option.

   MARK[UP] {HTML html_option|CSV csv_option}
     html_option;
       {ON|OFF} [HEAD text] [BODY text]
       [TABLE {ON|OFF}] [ENTMAP {ON|OFF}]
       [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
     csv_option:
       {ON|OFF} [DELIM[ITER] {c}]
       [QUOTE {ON|OFF}]

Here’s a quick example of it.

SET MARKUP CSV ON

SELECT level AS ID,
       'Description of ' || level AS description
FROM   dual
CONNECT BY level <= 5
ORDER BY 1;

"ID","DESCRIPTION"
1,"Description of 1"
2,"Description of 2"
3,"Description of 3"
4,"Description of 4"
5,"Description of 5"

 

SQLcl

The SQLcl tool is now shipped with the database. This is and alternative to SQL*Plus that you might want to try.

$ sql test/test@pdb1

SQLcl: Release 12.2.0.1.0 RC on Sat Apr 22 15:59:49 2017

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

Last Successful login time: Sat Apr 22 2017 15:59:50 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

1 thought on “SQL*Plus Enhancements

Leave a Reply

Your email address will not be published. Required fields are marked *