Sunday, October 7, 2018

SQL Plus 12.2 new features




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>

No comments:

Post a Comment