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.
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.
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;
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.
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.
[ref8] SQL*Plus® Release Notes Release 12.1 E18402-06
[ref9] SQL*Plus® User’s Guide and Reference Release 12.1 E18404-12