Oracle PL/SQL Programming
Oracle Database 12c Release 1
PL/SQL New Features
Steven Feuerstein
PL/SQL Evangelist, Dell
steven@stevenfeuerstein.com
Oracle PL/SQL Programming
How to benefit most from this presentation
Watch, listen, ask questions, focus on concepts and principles.
Download and use any of my training materials:
PL/SQL Obsession
http://www.ToadWorld.com/SF
Download and use any of my scripts (examples,
performance scripts, reusable code) from the same
location: the demo.zip file.
12c*_filename_from_demo_zip.sql
You have my permission to use all these materials to do
internal trainings and build your own applications.
But remember: they are not production ready.
You must test them and modify them to fit your needs.
Copyright 2013 Feuerstein and Associates
Page 2
Oracle PL/SQL Programming
Lots of Goodies for PL/SQL Developers
More PL/SQL-Only Data Types Cross PL/SQL-to-SQL
Interface
Optimizing Function Execution in SQL
The UTL_CALLSTACK Package
The ACCESSIBLE_BY Clause
FETCH FIRST and BULK COLLECT
Privileges/Access Management for Program Units
Grant Roles to Program Units
And INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES
BEQUEATH CURRENT_USER for Views
New Conditional Compilation Directives
Implicit Statement Results
Goodbye, Tiny SQL VARCHAR2!
Copyright 2013 Feuerstein and Associates
Page 3
Oracle PL/SQL Programming
More PL/SQL-Only Data Types Cross
PL/SQL-to-SQL Interface
Prior to 12c, PL/SQL-only datatypes could not
be bound in dynamic SQL statements,
restricted what functions could be called in
SQL, etc.
Now, those rules are greatly relaxed.
Bind records and associative arrays
Use TABLE operator with associative arrays
Still can't do much with Booleans
Copyright 2013 Feuerstein and Associates
12c_table*.sql
12c_bind*.sql
Page 4
Oracle PL/SQL Programming
Optimizing Function Execution in SQL
That seems like an awfully good idea!
Two methods:
WITH clause that defines a function
UDF pragma that gives more options to the
compiler
WITH FUNCTION: define a function directly
within your SQL statement.
Say goodbye to nasty context switch!
Copyright 2013 Feuerstein and Associates
12c_with_function*.sql
12c_udf.sql & 12c_udf2.sql
Page 5
Oracle PL/SQL Programming
The UTL_CALLSTACK Package
Prior to 12c, you could obtain several kinds of
"stacks" through individual function calls:
DBMS_UTILITY.FORMAT_CALL_STACK - "How did I
get here?"
DBMS_UTILITY.FORMAT_ERROR_STACK - "What is
the error message/stack?"
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE - "On
what line was my error raised?"
Now, the UTL_CALLSTACK package supports all
that and a much better API to the info in the
stack.
12c_utl_callstack*.sql
Copyright 2013 Feuerstein and Associates
Page 6
Oracle PL/SQL Programming
FETCH FIRST N for BULK COLLECT
BULK COLLECT now supports an optional
FETCH FIRST clause.
Limits the number of rows that a query returns,
reducing the complexity of common "Top-N"
queries.
FETCH FIRST is provided primarily to simplify
migration from third-party databases to
Oracle Database.
12c_fetch_first.sql
Copyright 2013 Feuerstein and Associates
Page 7
Oracle PL/SQL Programming
The ACCESSIBLE_BY Clause
ACCESSIBLE_BY extends the concept of
"privacy" for package subprograms.
Use it to define a "whitelist" of program units
that can invoke a package's subprograms.
PACKAGE BODY public_pkg
IS
PROCEDURE do_only_this
IS
BEGIN
private_pkg.do_this;
private_pkg.do_that;
END;
END;
PACKAGE private_pkg
ACCESSIBLE BY (public_pkg)
IS
PROCEDURE do_this;
PROCEDURE do_that;
END;
12c_accessible_by.sql
Copyright 2013 Feuerstein and Associates
Page 8
Oracle PL/SQL Programming
Grant Roles to Program Units
You can now grant roles to program units, so you finetune the privileges available to the invoker of a
program unit.
Helpful when you don't want the invoker to inherit all of
the definer's privileges.
Roles granted to a program unit do not affect
compilation.
Instead, they affect the privilege checking of SQL
statements that the unit issues at run time.
So the program unit executes with the privileges of both its
own roles and any other currently enabled roles.
Most helpful when the program unit executes dynamic
SQL (no privilege checking till runtime).
Copyright 2013 Feuerstein and Associates
invdefinv.sql
12c_grant_roles_units.sql
12c_roles_for_program_units.sql
Page 9
Oracle PL/SQL Programming
BEQUEATH CURRENT_USER for Views
Prior to 12.1, if your view executed a function,
it would always be run under the privileges of
the view's owner, and not that of the function.
Even if the function was defined with AUTHID
CURRENT_USER
Add the BEQUEATH CURRENT_USER clause
and then the invoker right's mode of the
function will be "honored."
12c_bequeath.sql
Copyright 2013 Feuerstein and Associates
Page 10
Oracle PL/SQL Programming
INHERIT PRIVILEGES and INHERIT ANY
PRIVILEGES
More fine-tuning for privilege management!
You can override AUTHID and BEQUEATH
settings by revoking INHERIT PRIVILEGES.
On a schema-level basis
You can say, in effect: "All schemas but SCOTT
can use my privileges when running X."
After upgrade, all works as before.
INHERT PRIVILEGES granted to all schemas
Copyright 2013 Feuerstein and Associates
12c_inherit_privileges.sql
Page 11
Oracle PL/SQL Programming
New Conditional Compilation Directives
Oracle has added two new directives.
$$PLSQL_UNIT_OWNER
Returns the name of the owner of the current
program unit. Returns NULL if an anonymous
block.
$$PLSQL_UNIT_TYPE
Returns the type of the program unit
Inside an anonymous block or non-DML trigger,
returns "ANONYMOUS BLOCK".
Copyright 2013 Feuerstein and Associates
Page 12
Oracle PL/SQL Programming
Implicit Statement Results
I hate when a SQL Server developer says "Ha,
ha, I can do this and you can't."
Well, with 12.1, there's one less thing that
developer can talk about.
Not that there was ever all that much.
We can now create a procedure that will
implicitly display the result of a SQL
statement.
And it breathes some life back into DBMS_SQL!
12c_implicit_results.sql
Copyright 2013 Feuerstein and Associates
Page 13
Oracle PL/SQL Programming
Goodbye, Tiny SQL VARCHAR2!
It sure has been irritating that PL/SQL
supports VARCHAR2s up to 32K in size (after
which , you must switch over to CLOBs), while
in SQL, the maximum was 4000.
Now, SQL's VARCHAR2 and NVARCHAR2 have
been extended to 32K as well!
Note: SQL has these maximum sizes only if the
MAX_STRING_SIZE initialization parameter is
set to EXTENDED.
12c_sql_varchar2.sql
Copyright 2013 Feuerstein and Associates
Page 14
Oracle PL/SQL Programming
Oracle12c New PL/SQL Features
Nothing earth-shattering, but a solid
improvement on an already robust language.
Improved integration with SQL
Better support for migration from TransactSQL
and other database languages
Fine-tuning of privileges management and access
control.
My favorite by far: ability to use TABLE with
associative arrays.
Copyright 2013 Feuerstein and Associates
Page 15
