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 an empty LOB instead of NULL.
- Use of COMMIT or ROLLBACK inside stored procedures or functions.
Use of magic numbers and strings instead of NULL.
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 ed_test_pkg
AS
PROCEDURE proc1 (
param1_in IN NUMBER,
param2_in IN NUMBER,
param1_out OUT NUMBER
);
END ed_test_pkg;
/
CREATE OR REPLACE PACKAGE BODY ed_test_pkg
AS
PROCEDURE proc1 (
param1_in IN NUMBER,
param2_in IN NUMBER,
param1_out OUT NUMBER
)
AS
BEGIN
param1_out := param1_in + param2_in;
END proc1;
END ed_test_pkg;
/
Positional Notation:
When you invoke a procedure using positional notation, you specify the parameters in the same order as the procedure declares them. For example:
DECLARE
l_param1_out NUMBER;
BEGIN
ed_test_pkg.proc1 (1, 2, l_param1_out);
DBMS_OUTPUT.put_line ('l_param1_out = ' || l_param1_out);
END;
Named Notation:
When you invoke a procedure using named notation, you specify the name and value of each parameter, using the association operator =>. The order of parameters is irrelevant. For example:
DECLARE
l_param1_out NUMBER;
BEGIN
ed_test_pkg.proc1 (param2_in => 2,
param1_out => l_param1_out,
param1_in => 1
);
DBMS_OUTPUT.put_line ('l_param1_out = ' || l_param1_out);
END;
Mixed Notation:
When you invoke a procedure using mixed notation, you start with positional notation, then use named notation for the remaining parameters. For example:
DECLARE
l_param1_out NUMBER;
BEGIN
ed_test_pkg.proc1 (1, param2_in => 2, param1_out => l_param1_out);
DBMS_OUTPUT.put_line ('param1_out = ' || l_param1_out);
END;
Which one to use?
After this quick notations refresher, the question is which one to choose as your preferred style when invoking PL/SQL programs? For me, I prefer, and almost always use, the named notation, and you should too. OK, I admit there is more typing involved in named notation, but it is easier to read and maintain than the other two. Moreover, if the program’s parameter list changes, you avoid changing the code that invokes the program using named notation.
Adding a new parameter
Speaking of changing the parameter list, let’s say you have to add a new param3_in IN parameter to the procedure ed_test_pkg.proc1. Which one of the following two approaches do you use?
Approach 1:
PROCEDURE proc1 (
param1_in IN NUMBER,
param2_in IN NUMBER,
param3_in IN NUMBER DEFAULT 1,
param1_out OUT NUMBER
);
Approach 2:
PROCEDURE proc1 (
param1_in IN NUMBER,
param2_in IN NUMBER,
param1_out OUT NUMBER,
param3_in IN NUMBER DEFAULT 1
);
As you know, adding DEFAULT to the IN parameter means that you do not have to pass in a value to this parameter, and if you don’t provide a value, the default value is used.
Now, you may be tempted to keep all the IN parameters together, however, the second approach is better. Adding the parameter to the end of the parameter list is safer. Let me show you why.
Let’s say you have a program that calls ed_test_pkg.proc1 using the positional notation. When you add a new IN parameter using the first approach, and if you do not make any further modifications to the code, you’ll get an error:
SQL> DECLARE
2 l_param1_out NUMBER;
3 BEGIN
4 ed_test_pkg.proc1 (1, 2, l_param1_out);
5 DBMS_OUTPUT.put_line ('l_param1_out = ' || l_param1_out);
6 END;
7 /
ed_test_pkg.proc1 (1, 2, l_param1_out);
*
ERROR at line 4:
ORA-06550: line 4, column 4:
PLS-00306: wrong number or types of arguments in call to 'PROC1'
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored
But, you will not get this error if you use approach 2 or if you use the named notation when calling ed_test_pkg.proc1.
Conclusion
So, in general, as a best practice, always use named notation when invoking PL/SQL procedures and functions. To avoid breaking existing code, add new parameters with DEFAULT values to the end of the parameter list of existing procedures or functions.
Source and Resources:
- Adding IN parameter
- Using Positional, Named, or Mixed Notation for PL/SQL Subprogram Parameters
- 11g New Features: Efficient PL/SQL Coding
Better SQL Formatter in Oracle SQL Developer, Finally a Reality
As mentioned on OracleCommunity.net, Oracle SQL Developer 1.5 Early Adopter 1 (EA1) is now available for download. As with any new release, there are new features and bug fixes. But, they are not yet detailed in the release notes.
However, I know that the one feature that I have been eagerly waiting for is finally here, it is the brand new and enhanced SQL Formatter (screenshots below). Not bad at all!
I’ll be using SQL Developer 1.5 as my main SQL and PL/SQL development tool for the next few days and weeks. Let’s see if this release will convince me to ditch Quest’s SQL Navigator.
New Oracle PL/SQL Error Management Framework Released
Error management in Oracle PL/SQL should be part of your application design and should follow a standard process for handling and raising errors. The good news is that you do not have to design and code your error management system yourself because Steven Feuerstein has done all the work for you.
During the seminar that I attended a couple of weeks ago, Steven announced the early release of a new product called the Quest Error Manager or QEM for short.
So, what is QEM? From the product documentation:
QEM is a framework that will help you standardize the management of errors in Oracle PL/SQL-based applications. It consists of the q$error_manager package and several underlying tables that store information about errors that occur in an application.
Moreover, QEM offers a single package to help you trace the execution of your application. The basic features are as follows:
- Write trace information out to the q$log table or to the DBMS_OUTPUT buffer.
- Selectively turn on and off tracing through calls to the q$error_manager procedures.
- Trace execution of code by a context string, and then also include strings, numbers, dates or Boolean values.
The Quest Error Manager is a freeware product and is not supported by Quest Software. You can download it as a zip file from oracleplsqlprogramming.com/downloads/qem.zip.
Lastly, I suggest you read Steven’s PowerPoint presentation Making the Most of PL/SQL Error Management Features. If you do not have Microsoft PowerPoint, you can still read the document from within your browser on scribd.
3 Comments | Filed in Oracle | Tags: pl/sqlOracle Database 11g New PL/SQL Features in your 10g Code Today
So, you are all excited about the new PL/SQL features in Oracle database 11g and you cannot wait until you upgrade your 10g database. Well, you do not have to wait. I will show you a simple way to put, and successfully compile, any new 11g PL/SQL feature inside your 10g PL/SQL code.
Let’s say you have just read about this new feature in the Oracle 11g database called “Function Result Cache”, which allows you to request that the function’s result be cached after the first call resulting in a much faster execution of this function in subsequent calls.
Function Result Cache is a great new 11g feature. But, too bad you are still on 10g. You have two options, the first is to wait until you upgrade to 11g, which, I suspect, may not be anytime soon. The second option is to prepare your 10g PL/SQL code to use this 11g feature now, while it is fresh in your mind. To be able to do this, you will have to use the combination of DBMS_DB_VERSION and conditional compilation.
Here is an example (tested in 10g XE in the HR schema):
SQL> CREATE OR REPLACE FUNCTION all_regions
2 RETURN sys_refcursor
3
4 $IF DBMS_DB_VERSION.VER_LE_10_2
5 $THEN
6 $ELSE
7 result_cache relies_on (regions)
8 $END
9 IS
10 l_regions_cur sys_refcursor;
11 BEGIN
12 OPEN l_regions_cur FOR
13 SELECT *
14 FROM regions;
15 RETURN l_regions_cur;
16 END all_regions;
17 /
Function created.
Lines four to eight mean that if the database version is less than or equal to 10.2 then do nothing. If the database version is greater than 10.2, like 11.0 for example, then instruct the compiler to include result_cache relies_on (regions) in the compilation of the program.
Let’s test calling the function:
SQL> DECLARE
2 l_regions_cur sys_refcursor;
3 l_regions_rt regions%ROWTYPE;
4 BEGIN
5 l_regions_cur := all_regions;
6 LOOP
7 FETCH l_regions_cur
8 INTO l_regions_rt;
9 EXIT WHEN l_regions_cur%NOTFOUND;
10 DBMS_OUTPUT.put_line (l_regions_rt.region_name);
11 END LOOP;
12 CLOSE l_regions_cur;
13 END;
14 /
Europe
Americas
Asia
Middle East and Africa
PL/SQL procedure successfully completed.
So, when you upgrade to 11g, all what you have to do is to re-compile your stored function and, boom!, the 11g new feature will be activated. Nice! Of course, in real world, you will have stored packages instead of standalone functions and procedures.
Note that I have not tested the above code in 11g. If you are an 11g beta tester, I would appreciate it if you let me know if the above works as expected.
Here are a few new PL/SQL features in 11g that Jurgen Kemmelings from AMIS has blogged about and that you may want to “conditionally compile” in your 10g database:
And here are a couple of white papers from Oracle (PDF):
5 Comments | Filed in Oracle | Tags: pl/sqlWhat you Ought to Know About CASE in Oracle PL/SQL
Oracle PL/SQL evangelist Steven Feuerstein‘s latest PL/SQL puzzler The Mysteries of CASE in PL/SQL is interesting. It touches on one “less-than-obvious aspect of PL/SQL’s CASE”. So, what is this “mysterious” thing about CASE? Continue reading…
6 Comments | Filed in Oracle | Tags: pl/sqlRousing Entertainment With the Guru of Oracle PL/SQL
I received the following invitation from NWOUG:
On behalf of Quest Software, NWOUG invites you to attend a free breakfast seminar that will be held in Portland, OR on Tuesday, July 24, 2007.
Join Steven Feuerstein, PL/SQL Evangelist of Quest Software, to explore key best practices for PL/SQL development and learn how they fit into a development workflow that produces high quality code. In addition to the rousing entertainment, you might even win a signed copy of one of his books, courtesy of O’Reilly Media!
A free breakfast and a “rousing entertainment” with Steven Feuerstein just around the corner of where I work, it does not get much better than that! So, I’m planning to attend.
The agenda includes a Q & A session at the end of the seminar, so I may have the opportunity to ask Steven a few questions.
Do you have a specific question you would like me to ask Steven? I do not guarantee I’ll be able to ask him your question, but I’ll try.
3 Comments | Filed in Oracle | Tags: pl/sqlDid 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 is counter intuitive: declare a gigantic variable… Once you go above 2000 characters, the variable stops being statically allocated to accommodate its maximum size, and starts being dynamically allocated every time it’s assigned, based on the exact size that’s really needed.
He gives an example:
– Rest of 512 characters are wasted empty space
email_address varchar2(512) := ‘someone@example.com’;
– Although declared with length 32000, only 24 characters are allocated
email_address varchar2(32000) := ‘someone_else@example.com’;
I know that, using DUMP, you can return the length of a variable in bytes, but the question is how do you really calculate the total space allocated to a certain PL/SQL variable?
John also gives an additional tip:
When you want a variable to hold the contents of a table column, there is a simple way to match the right length: var1 my_table.my_column%type;
Using anchored type declarations is one of PL/SQL’s best practices. But, what if you have a variable that is a concatenation of two or more table columns? Here is the trick:
declare
cursor my_cursor is select col1 || col2 concatenated from my_table;
var2 my_cursor.concatenated%type;
We let Oracle figure out the maximum length of the concatenated columns, then piggyback on that result to make a variable of the same type.
John always has interesting tips. Here are 3 Useful SQL*Plus Tips I found on his blog a few weeks ago.
6 Comments | Filed in Oracle, Tips | Tags: pl/sql, sqlplus5 Links from Around the Web (2007-05-25)
-
SQL injection is one of the most common security vulnerabilities for web applications today. This article shows ways to solve the problem, including a new technique called “Disabling Literals”, and lists new attacks.
-
Oracle PL/SQL Code Generator for Table Encapsulation that would handle all DML operations. It would generate production level code for a table that could be incorporated in any application.
-
One way to avoid “deja code”, that feeling you’d written this algorithm before, is to generate code rather than write it manually.
-
Choice between stored procedures, functions, views, triggers and Inline SQL.
-
Database design is more art than science. An excerpt from Professional Oracle Programming.
More from my bookmarks on del.icio.us
4 Comments | Filed in Links | Tags: pl/sql, sql-injectionTwo Oracle PL/SQL Features You Probably Don’t Know About
Oracle PL/SQL has a neat and little known feature called forward declaration. In this post I’m going to do a quick review of what forward declaration is, how it can be used and a situation in which forward declarations are absolutely required, mutual recursion.
As you already know, PL/SQL requires that you declare elements (variables, procedures and functions) before using them in your code. For example: Continue reading…
12 Comments | Filed in Oracle | Tags: pl/sql
