what's new in pl/sql and ebr in c - rainfocus · pdf filetitle: new pl/sql features...
TRANSCRIPT
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
What's New in PL/SQL and EBRinOracle Database 12c Release 2
Bryn LlewellynDistinguished Product ManagerDatabase DivisionOracle HQtwitter: @BrynLiteSeptember 2016
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
Safe Harbor Statement
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
• Available now
– Exadata Express Cloud Service
• Coming soon
– Database Cloud Services
– Exadata Cloud Machine
Announcing Oracle Database 12c Release 2 on Oracle Cloud
Oracle is presenting features for Oracle Database 12c Release 2 on Oracle Cloud. We will announce availability of the On-Prem release sometime after Open World.
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
Why use PL/SQL?
- brief reminder
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
commit
insert
delete
update
select
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
commit
insert
delete
update
select
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
commit
insert
delete
update
select
sN
s5
s2
s4
s7s3
s1
s6
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
commit
insert
delete
update
select
ORDS
sN
s5
s2
s4
s7s3
s1
s6
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
Agenda
Language enhancements
Tools enhancements
Edition-Based Redefinition (EBR) enhancements
1
2
3
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
Agenda
Language enhancements
Tools enhancements
Edition-Based Redefinition (EBR) enhancements
1
2
3
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
Static PL/SQL expressions are allowedwhere previously only literals were allowed...
-- Ora_Max_Name_Len is defined in DBMS_Standard
One_Part_ID varchar2(Ora_Max_Name_Len + 2);Two_Part_ID varchar2(2*(Ora_Max_Name_Len + 2) + 1);
begin
One_Part_ID := '"My Table"';
Two_Part_ID := '"My Schema"'||'.'||One_Part_ID;
...
• Many extant programs will have declarationslike varchar2(30), varchar2(32) and so on
• In 12.2, this will be varchar2(128), varchar2(130) and so on
• Use this new feature to make these changes properly!
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
The “accessible by” clause can be appliedto a subprogram in a package spec
package p authid definer is
function a return varchar2 deterministic accessible by (x);procedure b;
end p;
package body q is
procedure a is
begin
DBMS_Output.Put_Line(p.a());
end a;
...
end q;
• Given package spec p thus:
• then, with the obvious package spec q, this:
• causes this compilation error:
PLS-00904: insufficient privilege to access object A
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
New pragma allows an element of any kindto be marked as deprecatedprocedure p authid Definer is
pragma deprecate(p, 'p is deprecated. You must use p2 instead.');begin
DBMS_Output.Put_Line('p');
end p;
• causes this compilation warning:
PLW-06019: entity P is deprecated
procedure q authid Definer is
begin
p();
DBMS_Output.Put_Line('q');
end q;
• causes this compilation warning:
PLW-06020:reference to a deprecated entity: p is deprecated. You must use p2 instead.
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
The ability to bind values of PL/SQL-only datatypesis extended to DBMS_Sql
procedure p(i in boolean) is...
...
x constant boolean not null := true;begin
DBMS_Sql.Parse(Cur, 'call p(:b)', DBMS_Sql.Native);
DBMS_Sql.Bind_Variable(Cur, 'b', x);Dummy := DBMS_Sql.Execute(Cur);
DBMS_Sql.Close_Cursor(Cur);
...
• Given procedure p thus:
• then, this compiles and runs without error to give the expected result:
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
The ability to bind values of PL/SQL-only datatypesis extended to DBMS_Sql – cont
package Pkg authid Definer is
type r is record(a integer, b integer);procedure p(i in r);
end Pkg;
...
x Pkg.r;begin
x.a := 17;
x.b := 42;
DBMS_Sql.Parse(Cur, 'call Pkg.p(:b)', DBMS_Sql.Native);
DBMS_Sql.Bind_Variable_Pkg(Cur, 'b', x);Dummy := DBMS_Sql.Execute(Cur);
DBMS_Sql.Close_Cursor(Cur);
...
• And given package Pkg thus:
• then, this compiles and runs without error to give the expected result:
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
Support for JSON
• 12.1 introduced JSON SQL functions
• In 12.2, these SQL functions are available in PL/SQL expressions
• Of course, these functions cannot change the JSON
• Oracle-supplied object types allow JSON construction and within-JSON editing
– An object is construct from a JSON document
– The JSON is parsed once and the parsed representation is hidden inside the object
– Function methods let you access attribute values
– Procedure methods let you change attribute values
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
Agenda
Language enhancements
Tools enhancements
Edition-Based Redefinition (EBR) enhancements
1
2
3
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
Brand-new code coverage tool• Start coverage, run the tests, stop coverage
• Blocks
– A block is a run of one or more consecutive characters in the source textof a PL/SQL unit. Every character of the text is in exactly one block.
– If, during a coverage run, the point of execution enters a particular unit,then information about each of its blocks is recorded in a table
• Each block is labeled with the unit it’s in, the line/column where it starts,the covered yes/no status, and the “feasibility” yes/no status
• Can calculate percentage of [feasible] blocks covered
• Can mark up source code with coverage coloring
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
function Three_Condition_If(i in number) return number authid Definer is
begin
if i < 1 or i > 10 or i is null then
return 0;
else
return 1;
end if;
end Three_Condition_If;
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
function Recip(i in number) return number authid Definer is
begin
return 1.0/i;
exception
when Zero_Divide then
return 42;
when others then
...
raise;
end Recip;
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
function Recip(i in number) return number authid Definer is
begin
return 1.0/i;
exception
when Zero_Divide thenreturn 42;
when others then...raise;
end Recip;
declare
Run_ID pls_integer not null := -1;
begin
Run_ID := DBMS_Plsql_Code_Coverage.Start_Coverage(Run_Comment=>'Run_001');
DBMS_Output.Put_Line(Recip(17));
DBMS_Plsql_Code_Coverage.Stop_Coverage();end;
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
function Recip(i in number) return number authid Definer is
begin
return 1.0/i;
exception
when Zero_Divide then
return 42;
when others then...raise;
end Recip;
declare
Run_ID pls_integer not null := -1;
begin
Run_ID := DBMS_Plsql_Code_Coverage.Start_Coverage(Run_Comment=>'Run_001');
DBMS_Output.Put_Line(Recip(17));DBMS_Output.Put_Line(Recip(0));
DBMS_Plsql_Code_Coverage.Stop_Coverage();end;
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
function Recip(i in number) return number authid Definer is
begin
return 1.0/i;
exception
when Zero_Divide then
return 42;
when others thenpragma Coverage('NOT_FEASIBLE');raise;
end Recip;
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
function Recip(i in number) return number authid Definer is
begin
return 1.0/i;
exception
when Zero_Divide then
return 42;
when others then
pragma Coverage('NOT_FEASIBLE');
raise;
end Recip;
declare
Run_ID pls_integer not null := -1;
begin
Run_ID := DBMS_Plsql_Code_Coverage.Start_Coverage(Run_Comment=>'Run_001');
DBMS_Output.Put_Line(Recip(17));DBMS_Output.Put_Line(Recip(0));
DBMS_Plsql_Code_Coverage.Stop_Coverage();end;
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
procedure Case_Stmt_1(i in boolean := true) authid Definer is
begin
case i
when true then
DBMS_Output.Put_Line('i is true');
when false then
DBMS_Output.Put_Line('i is false');
else
DBMS_Output.Put_Line('i is null');
end case;
end Case_Stmt_1;
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
procedure Case_Stmt_2(i in boolean := true) authid Definer is
begin
case i
when true then
DBMS_Output.Put_Line('i is true');
when false then
DBMS_Output.Put_Line('i is false');
-- Notice that there's no "else" leg.
end case;
exception when Case_Not_Found then
DBMS_Output.Put_Line('Case_Not_Found');
end Case_Stmt_2;
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
PL/Scope enhancements
• Reports the SQL_ID for static SQL statements
• Annotates static SQL with insert, update, delete, or select(a.k.a. CRUD analysis)
• The identifiers in static SQL statements are now tracked(so the CRUD analysis reports what columns are affected)
• Reports where native dynamic SQL is used (look for SQL injection risks)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
Agenda
Language enhancements
Tools enhancements
Edition-Based Redefinition (EBR) enhancements
1
2
3
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
Edition-Based Redefinition (EBR)
• Introduced in 11.2
– Successfully deployed by various Oracle internal groups and customers despite limitations in initial release
• 12.1 added features to make EBR adoption easier
– Editionability of objects became a per object property avoiding need for schema reorganization
• 12.2 automates retiring of unused editions
– Editioned objects no longer in use are cleaned up automatically in the background
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
Automated retiring of unused editions
• “Drop edition” is re-implemented to formalize retiring an edition
• You can always drop the root edition, even when it containsactual editioned objects that are inherited by a descendent edition
• It remains in place, but it is marked “unusable” so that you cannever use it again
• An editioned object in an unusable edition that is not visible in a usable edition is dropped safely and automatically by a background process
• When an unusable edition contains no actual editioned objects, it is dropped safely and automatically by a background process
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
Summary
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
• Static PL/SQL expressions are allowed where previously only literals were allowed
• The “accessible by” clause can be applied to a subprogram in a package spec
• New pragma allows an element of any kind to be marked as deprecated
• The ability to bind values of PL/SQL-only datatypes is extended to DBMS_Sql
• New supplied subprograms convert a PL/SQL variable to a JSON representation and vice versa______________________________________________________________________________
• Brand-new code coverage tool
• PL/Scope enhancements______________________________________________________________________________
• Automated retiring of unused editions
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.
General PL/SQL and EBR resources
• PL/SQL page on OTN: oracle.com/plsql
• EBR page on OTN: oracle.com/ebr
• Oracle Database PL/SQL and EBR blog: blogs.oracle.com/plsql-and-ebr/
Copyright © 2016, Oracle and/or its affiliates. All rights reserved.