Review – Oracle Database 12c New SQL*Plus commands


SQL*Plus is the Command Line User Interface that is installed with every Oracle Database installation.
It allows the user to connect to the database, perform database administration, create and execute batch scripts, get the description of any schema object, and format, save, and print query result sets.

Every new release of the Oracle Database brings to the users a new SQL*Plus set of commands and options.

task#8 For this task we will use the table DEMO.TEXTS that was introduced in the previous post task#7. Also, the new procedure proc_impl_res was created to demonstrate the SQL*Plus support for implicit results.
step#1 Last Login Time

By default, SQL*Plus displays the time the user last logged on.
This security feature can be turned off with a SQLPLUS command option -NOLOGINTIME.

SQL*Plus Last Login Time

SQL*Plus Last Login Time

step#2 New Administrative Privileges

The new user privileges SYSBACKUP, SYSDG, and SYSKM are supported by SQLPLUS and CONNECT commands.
The existing privileges SYSASM, SYSDBA, and SYSOPER are supported too.

SQL*Plus new user privileges SYSBACKUP, SYSDG, and SYSKM

SQL*Plus new user privileges SYSBACKUP, SYSDG, and SYSKM

step#3 Support for Implicit Results

SQL*Plus can iteratively return results from a PL/SQL Statement without using a local ref cursor.

create or replace procedure proc_impl_res
authid current_user
as
  l_cursor_min sys_refcursor;
  l_cursor_max sys_refcursor;
begin
  open l_cursor_min for SELECT * FROM TEXTS where winter > 100;
  dbms_sql.return_result(l_cursor_min);
  open l_cursor_max for SELECT * FROM TEXTS where summer > 1000000;
  dbms_sql.return_result(l_cursor_max);
exception
  when others then
  dbms_output.put_line(SQLERRM);
end;
SQL*Plus Support for Implicit Results

SQL*Plus Support for Implicit Results

step#4 Displaying Invisible Columns

SQL*Plus has the command SET COLINVI[SIBLE] to allow invisible column
information to be viewed with the SQL*Plus DESCRIBE command.

SQL*Plus Displaying Invisible Columns

SQL*Plus Displaying Invisible Columns

step#5 Pluggable Database Support

The STARTUP command options support pluggable databases.
There are also SHOW command options to display information about pluggable
databases: SHOW CON_ID, SHOW CON_NAME and SHOW PDBS.

SQL*Plus Pluggable Database Support

SQL*Plus Pluggable Database Support

References

[ref8] SQL*Plus® Release Notes Release 12.1 E18402-06
[ref9] SQL*Plus® User’s Guide and Reference Release 12.1 E18404-12

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s