Archive for the ‘Tips’ Category

Oracle SQL and PL/SQL Bad Practices Document

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 […]

Two Quick and Simple Tips That Will Help You Write Better PL/SQL

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 […]

Beware of Comments in SQL

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 […]

Found or Not Found, That is The Question. Do You Have The Answer?

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 […]

PL/Scope in Oracle Database 11g

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 […]

Add Any Search Engine to Your Browser, The Easy Way

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 […]

Self Documenting Code is Not Enough

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 […]

Oracle Database 11g Old Feature: Internet Addressing

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 […]

Here is A Different And Handy Way to Search in Your Browser

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 […]

Did You Know That About PL/SQL Variables?

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 […]