The document below contains patterns of bad SQL and PL/SQL code that Gojko Adzic has repeatedly found in various applications and databases. Some of the bad practices include:

Use of WHEN OTHERS in exception handling. Embedding complex SQL inside PL/SQL code. Poor PL/SQL error handling. Hardcoding the size of PL/SQL variables. Not using bind variables. Storing ROWIDs for later reference. Storing […]

When invoking a PL/SQL procedure or function, you can specify the value of its parameters using either positional, named, or mixed notation. Let’s review what each notation means and then demonstrate the best way to add new parameters to an existing subprogram without breaking existing code.

Consider this simple package as an example:

CREATE OR REPLACE PACKAGE […]

In SQL and PL/SQL there are two comment styles you can use, single-line and multi-line:

Multi-line comments begin with a slash and an asterisk (/*) and end with an asterisk and a slash (*/). the comment can span multiple lines. single-line comments begin with — (two hyphens) and end with a line break. The comment text cannot […]

Here is a new puzzler from Steven Feuerstein:

Which of the following circumstances does not raise a NO_DATA_FOUND exception? (Hint: There is more than one)

A SELECT INTO statement that finds no rows A numeric FOR loop whose low value or high value is NULL An attempt to get the value of an element in a collection whose index […]

This new Oracle Database 11g feature is PL/Scope:

PL/Scope is a compiler-driven tool that collects data about user-defined identifiers from PL/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, assignment) and the location of […]

Whether you use Firefox or Internet Explorer, you probably know about this handy search box usually located at the top right corner of your browser window. This search box puts search engines at your fingertips and makes them easily available to you whenever you need them.

I have recorded a short three and a half minute […]

I stumbled upon an interesting article titled: Comments Are More Important Than Code. In this article, the author, Jef Raskin, writes:

Good documentation should be readable on its own, with bits of code showing how the design is implemented (and making it run, of course). Reconstructing code from good documentation is far easier than […]

No, this is not an Oracle database 11g new feature. If you are disappointed please move one to your next item on your reading list. Otherwise, the following is a quick refresher about a handy feature that has existed since 8.1.7 and will continue to exist in 11g. It is the Oracle supplied UTL_INADDR PL/SQL […]

Whether you use Firefox or Internet Explorer, you probably know about this handy search box usually located at the top right corner of your browser window.

And if you are a long time reader of my blog, you probably know about the handy Oracle related search plugins that allow you to search Oracle documentation, sites, forums […]

In his latest post titled Less is More, More or Less, John Russell, the man behind tahiti.oracle.com, reveals a very interesting fact about the space needed to store PL/SQL variables of varchar2 datatype. He writes:

If you don’t know exactly how much space is needed for a PL/SQL variable, the most space-efficient thing you can do […]