Most Oracle professionals are aware of the importance of keeping database statistics up to date. However, contrary to common knowledge, these statistics are not limited to tables, columns and indexes! PL/SQL functions also have a number of associated statistics, namely costs (CPU, I/O, network), selectivity, and cardinality (for functions that return collections). These statistics have…

Each individual sql statement submitted from a client (even sqlplus running directly on the database server) generates a pair of sqlnet waits. That is no big deal if you are submitting a single large set operation transaction and the sqlnet waits are a tiny fraction of the time of executing the statement. But if you…

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.