Oracle PL/SQL Programming
Oracle Database 12c Release 1 PL/SQL New Features
Steven Feuerstein PL/SQL Evangelist, Dell
[email protected]
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
