There are quite a
new features in the latest SQL*Plus client that looks like they could be super
useful, the documentation
has the changes in so review the docs for the facts.
History – sql*plus
now support command history, the command history feature enables users to
run/edit or delete previously used sql*plus, sql and pl/sql commands from the
history list in the current session (as bonus tip – if you are on Window
platform – you can make use of F7)
C:\Users\ admin
>sqlplus -version
SQL*Plus:
Release 12.2.0.1.0 Production
C:\Users\admin>sqlplus
demo/demo@ora12c
SQL*Plus:
Release 12.2.0.1.0 Production on Thu Oct 4 17:24:31 2018
Copyright (c)
1982, 2016, Oracle. All rights reserved.
Last Successful
login time: Thu Oct 04 2018 17:22:52 +05:30
Connected to:
Oracle Database
12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
demo@ORA12C>
set history 5
demo@ORA12C>
select * from dual ;
D
-
X
demo@ORA12C>
select * from dept;
DEPTNO DNAME LOC
----------
-------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
demo@ORA12C>
select dummy from dual;
D
-
X
demo@ORA12C>
history
1
select * from dual ;
2
select * from dept;
3
select dummy from dual;
demo@ORA12C>
history 1 run
D
-
X
demo@ORA12C>
history 2 edit
demo@ORA12C>
history
1
select * from dual ;
2
select * from dept;
3
select dummy from dual;
4
select * from dep2;
demo@ORA12C>
history 4 run
select * from
dep2
*
ERROR at line
1:
ORA-00942:
table or view does not exist
demo@ORA12C>
demo@ORA12C>
history clear
demo@ORA12C>
history
SP2-1651:
History list is empty.
demo@ORA12C>
Support for input
binding using the VARIABLE command, the VARIABLE command now support the input
binding which can be used in SQL and PL/SQL statements.
Prior to 12.2
sql*plus, in order to use bind variables, we need to declare them and then run
a pl/sql block in order to set them.
demo@ORA12C>
variable x number
demo@ORA12C>
exec: x := 55;
PL/SQL
procedure successfully completed.
demo@ORA12C>
print x
X
----------
55
Starting with 12.2,
Oracle has extended the syntax of variable command to allow you to set the
value of the bind as you declare it, so our simple example becomes like this.
demo@ORA12C>
variable y number = 55
demo@ORA12C>
print y
Y
----------
55
However with the
new syntax we can’t use defined variables, so if you have script that allows
for input from the users, we can’t use that for binds without the pl/sql calls.
demo@ORA12C>
variable x number = &1
SP2-0425:
"&1" is not a valid NUMBER
To resolve that
error, we need to switch back to old pl/sql calls
demo@ORA12C>
variable x number
demo@ORA12C>
exec :x := &1
Enter value for
1: 55
PL/SQL
procedure successfully completed.
demo@ORA12C>
print x
X
----------
55
demo@ORA12C>
The next new
feature is Statement caching. Statement caching is well known in application
programming, basically it makes soft parsing into no parsing at all.
A quick demo of
that old behavior (and default 12.2 behavior)
rajesh@ORA12C>
variable HELLO number = 42
rajesh@ORA12C>
select * from dual where :HELLO = 55;
no rows
selected
rajesh@ORA12C>
exec :HELLO := 55;
PL/SQL
procedure successfully completed.
rajesh@ORA12C>
select * from dual where :HELLO = 55;
D
-
X
rajesh@ORA12C>
select sql_id,executions,parse_calls,sql_text
2 from
v$sql
3
where upper(sql_text) like '%HELLO%'
4 and
upper(sql_text) not like '%V$SQL%';
SQL_ID EXECUTIONS PARSE_CALLS SQL_TEXT
-------------
---------- ----------- ----------------------------------------
2rj8twfx0gnzv 2 2 select * from dual where :HELLO
= 55
6nbqpb1f8vssa 1 1 BEGIN :HELLO := 55; END;
rajesh@ORA12C>
rajesh@ORA12C>
Without statement
caching, we parse once per execution and with statement caching enabled it goes
like this.
rajesh@ORA12C>
set statementcache 50
rajesh@ORA12C>
variable HELLO1 number = 42
rajesh@ORA12C>
select * from dual where :HELLO1 = 55;
no rows
selected
rajesh@ORA12C>
exec :HELLO1 := 55;
PL/SQL
procedure successfully completed.
rajesh@ORA12C>
select * from dual where :HELLO1 = 55;
D
-
X
rajesh@ORA12C>
rajesh@ORA12C>
select sql_id,executions,parse_calls,sql_text
2 from
v$sql
3
where upper(sql_text) like '%HELLO1%'
4 and
upper(sql_text) not like '%V$SQL%';
SQL_ID EXECUTIONS PARSE_CALLS SQL_TEXT
-------------
---------- ----------- ----------------------------------------
6nhah7j0ctggf 2 1 select * from dual where
:HELLO1 = 55
fr03kw5dzpuqs 1 1 BEGIN :HELLO1 := 55; END;
rajesh@ORA12C>
Support for CSV format
data is available now and here is a quick demo of it.
rajesh@ORA12C>
set markup csv on
rajesh@ORA12C>
select * from dept;
"DEPTNO","DNAME","LOC"
10,"ACCOUNTING","NEW
YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"
rajesh@ORA12C>
set markup csv on delimiter '|'
rajesh@ORA12C>
select * from dept;
"DEPTNO"|"DNAME"|"LOC"
10|"ACCOUNTING"|"NEW
YORK"
20|"RESEARCH"|"DALLAS"
30|"SALES"|"CHICAGO"
40|"OPERATIONS"|"BOSTON"
rajesh@ORA12C>
set markup csv on delimiter '|' quote off
rajesh@ORA12C>
select * from dept;
DEPTNO|DNAME|LOC
10|ACCOUNTING|NEW
YORK
20|RESEARCH|DALLAS
30|SALES|CHICAGO
40|OPERATIONS|BOSTON
rajesh@ORA12C>
set markup csv off
rajesh@ORA12C>
select * from dept;
DEPTNO DNAME LOC
---------- --------------
-------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
rajesh@ORA12C>
New option
introduced to suppress the result sets with SET FEEDBACK ONLY
rajesh@ORA12C>
show feedback
FEEDBACK ON for
6 or more rows
rajesh@ORA12C>
select * from dept;
DEPTNO DNAME LOC
----------
-------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
rajesh@ORA12C>
set feedback only
rajesh@ORA12C>
select * from dept;
4 rows
selected.
rajesh@ORA12C>
