The Six Commandments

David Clement

September 2004

Oracle versions 7-8


Thou shalt use bind variables and not hard-code thy values into SQL statements, that hard parsing may not increase the processing time.

Thou shalt not commit and fetch again from the one cursor, for the kernel will give thee an ORA-01555 error, until the last iteration.

Honor thy cursor, and keep it open for as long as thou needest it, and then close it at once. Close it not unnecessarily; neither leave it open when thou droppest thy connection.

Tune thy SQL statements, using the facilities that Oracle provideth, lest the optimizer give thee unexpected results.

One is the number of rows that thou mayest fetch without fetching into an array; if thou fetchest two, and ever more the more thou fetchest, declare thou thy host variables as arrays.

Submit no SQL statements in-line from a host language; package thy SQL, and call the packaged routines. Many are the benefits thereof.