A SQL statement that calls a PL/SQL function is slower than one that defines the same result by using only SQL expressions. The culprit is the SQL to PL/SQL round-trip. Its cost can be reduced by marking the function deterministic, by invoking it in a scalar subquery, by using the PL/SQL function result cache, or…

  Releasing code changes to PL/SQL in heavily executed packages on databases with high uptime requirements brings many challenges. It’s not possible to gain the DDL lock to compile a package while it’s being executed, so how do you do so without downtime? In 11gR2, Oracle introduced edition-based redefinition (EBR). This allows you to deploy…

PL/SQL includes a versatile exception handling facility. Using it effectively can yield robust and efficient code. When mis-used, it can make troubleshooting harder or mask issues. This session will explore how to use PL/SQL Exceptions to your advantage and avoid common pitfalls. Whitepaper PowerPoint

Oracle Database 12c brings PL/SQL enhancements in these categories: transparent performance improvements; new PL/SQL language features for better performance, new functionality, and improved programming usability; and new supplied package APIs. This session explains improvements in PL/SQL-to-SQL interoperability, improvements in how PL/SQL subprograms can be invoked from outside of the database, new constructs to improve modularization,…

It is impossible to think of PL/SQL without also thinking about SQL operations because the main purpose of PL/SQL is to provide the fastest and most efficient way of extending the relational database environment into the realm of procedural logic. The most important thing to remember is that SQL and PL/SQL are completely separate languages, each with its own strengths and weaknesses. SQL and PL/SQL have different memory structures, optimization mechanisms, etc. The critical difference between them is that PL/SQL is a procedural language, whereas SQL is a declarative, set-based language. This fundamental difference between the two languages means that you cannot effectively utilize SQL and PL/SQL together unless you stop thinking exclusively in terms “line-by-line” and “row-by-row”.

The first PL/SQL code elements to review when deciding how to properly use SQL inside of PL/SQL are cursors. The battle between proponents of implicit vs. explicit cursors is as old as Oracle databases. Sadly, online fighters often miss the key feature of database cursors, namely that they are nothing more than pointers to SQL…

Learn how to use Oracle SQL Developer PL/SQL Unit Test capabilities to define and create PL/SQL unit tests. Harper will also show readers how to add reusable test components to a unit test library and how to define and handle test exceptions.

Bulusu describes a framework for debugging PL/SQL application code.

Increasingly, companies are considering public cloud computing solutions, including Oracle's on demand and other software-as-a-service (SaaS) applications. However, the adoption of this new breed of applications introduces a technological divide between privately owned datacenters and third-party public clouds, resulting in an array of new challenges to overcome and a new level of management complexity to…

Too many Oracle shops start from scratch when beginning PL/SQL development, learning the hard way from novice developers, books, bugs and expensive trial and error. We will look in-depth at an open source framework that can save you 4 to 8 weeks of development time, and months of future refactoring and maintenance enhancements. It can…