q=opp6

Oracle PL/SQL Programming, 6th Edition, Now Available!

  • feed_items
  • block_1
in

Oracle PL/SQL Programming, 6th Edition, Now Available!

OPP6

Oracle PL/SQL Programming: the book that changed my life (and seems to have helped lots of others). Six editions in 20 years....starting with Oracle7 and now covering all PL/SQL topics through Oracle Database 12c Release 1. It's here and it's time to stock up!

It's a bit late in hitting the bookshelves, 12.1 having gone production last Fall. But...well....what can I say? I was so busy writing quizzes for the PL/SQL Challenge and playing with granddaughter, Loey, that time slipped by. My apologies to all those PL/SQL developers who have wondering and waiting - at least I hope you've been waiting, and not buying all the other 12.1 PL/SQL books out there. No, I take that back. I wish nothing but the best for all my fellow PL/SQL authors!

So: this book builds upon the first five editions (keeping almost all content intact, except for the numerous improvements recommended by my able reviewers) by adding detailed information about the new PL/SQL features for 12.1. You can read all about those features below, but first I want to talk about why you should:

Order a Signed Copy of OPP6 From Me

Yes, that's right. You can click right here to order a copy of this hefty (4 lb) reference directly from me ($70 including shipping - list price of book is $69.99; $40 extra to ship internationally). Why would you want to do this? So I can sign it for you, of course!

Sure, you can probably get it for less from Amazon and with free shipping! (read what I think about that here) But I would like to think that you might want to support my granddaughter more than you'd want to put a few more bucks in the pocket of Jeff Bezos (net worth around $27B).

Convinced? I hope so! Click here to place an order.

More PL/SQL-Only Data Types Cross PL/SQL-to-SQL Interface

Prior to 12.1, you could not bind PL/SQL-specific datatypes (for example, an associative array) in a dynamic SQL statement.. Now it is possible to bind values with PL/SQL-only data types in anonymous blocks, PL/SQL function calls in SQL queries, CALL statements, and the TABLE operator in SQL queries.

ACCESSIBLE_BY Clause

You can now include an ACCESSIBLE_BY clause to your package specification, specifying which program units may invoke subprograms in the package. This feature allows you to ?expose? subprograms in helper packages that are intended to be consumed only by specific program units. This feature offers a kind of ?whitelisting? for packages.

Implicit Statement Results

Before Oracle Database 12c, a PL/SQL stored subprogram returned result sets from SQL queries explicitly, through an OUT REF CURSOR parameter or RETURN clause. The client program would then have to bind to those parameters explicitly to receive the result sets. Now, a PL/SQL stored subprogram can return query results to its client implicitly, using the PL/SQL package DBMS_SQL instead of OUT REF CURSOR parameters. This functionality will make it easy to migrate applications that rely on the implicit return of query results from stored subprograms (supported by languages like Transact SQL) from third-party databases to Oracle Database.

BEQUEATH CURRENT_USER Views

Before Oracle Database 12c, a view always behaved like a definer rights unit (AUTHID DEFINER), even if it was referenced inside an invoker rights unit (AUTHID CURRENT_USER). Now, a view can be either BEQUEATH DEFINER (the default), which behaves like a definer rights unit, or BEQUEATH CURRENT_USER, which behaves somewhat like an invoker?s rights unit.

Grant Roles to
Program Units

Prior to Oracle Database 12c, an invoker rights unit always ran with the privileges of its invoker. If its invoker had higher privileges than its owner, then the invoker rights unit might perform operations unintended by, or forbidden to, its owner.

As of 12.1, you can grant roles to individual PL/SQL packages and standalone subprograms. Instead of a
definer rights unit, you can create an invoker rights unit and then grant roles to it. The invoker rights unit then runs with the privileges of both the invoker and the roles, but without any additional privileges possessed by the definer'sschema.

An invoker rights unit can now run with the privileges of its invoker only if its owner has either the INHERIT PRIVILEGES privilege on the
invoker or the INHERIT ANY PRIVILEGES privilege. Note: the INHERIT PRIVILEGES is granted to all schemas on install/upgrade.

New Conditional Compilation Directives

In 12.1, Oracle has added two new predefined inquiry directives, $$PLSQL_UNIT_OWNER and $$PLSQL_UNIT_TYPE, which return the owner and type of the current PL/SQL program unit.

Optimizing Function Execution in SQL

Oracle now offers two ways of improving PL/SQL function performance in SQL statements: you can actually define the function itself inside the SQL statement using the WITH clause, and you can add the UDF pragma to the program unit, which tells the compiler that the function will be used primarily in SQL statements.

Using %ROWTYPE with Invisible Columns

Oracle Database 12c makes it possible to define invisible columns. From within PL/SQL, the %ROWTYPE attribute is aware of these types of columns and how to work with them.

FETCH FIRST Clause and BULK COLLECT

In 12.1, use the optional FETCH FIRST clause to limit the number of rows that a query returns, significantly reducing the SQL complexity of common ?Top-N? queries. FETCH FIRST will be of most benefit in the simplification of migration from third-party databases to Oracle Database. This clause can also, however, improve the performance of some SELECT BULK COLLECT INTO statements.

The UTL_CALLSTACK Package

Prior to 12.1, the DBMS_UTILITY package offered three functions (FORMAT_CALL_STACK, FORMAT_ERROR_STACK, and FORMAT_ERROR_BACKTRACE) to provide information about the execution call stack, error stack and error backtrace, respectively. Now in 12.1, a single package, UTL_CALLSTACK, provides that same information, plus much more ?fine-grained? access to the contents of these formatted strings.

You can, of course, check out these features yourself, by downloading the software or you can simply start with perusing the documentation set. I have also been publishing articles in Oracle Magazine on these features. Finally, I also encourage to visit Oracle-BASE , a wonderfully useful website from Tim Hall, which already contains a large number of in-depth articles on 12.1 features, PL/SQL and SQL.