Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

Assignments, Initializations and NULLs

We have already seen that the assignment operator is the colon equal (:=) syntax and we have seen how this is used to assign values to a variable both within the code and upon declaration.  It is a good practice to initialize variables to a value when they are declared.  This is especially important with number variables to avoid null math errors.

Many programming languages avoid NULLs by requiring that a variable be initialized before use.  However, in a database, NULLs are commonly used and are not considered errors.  Anytime a row is inserted into a table with only some of the column values defined, NULLs are used for the undefined columns.  Thus NULLs are values in the database.

The value of NULL is ‘undefined’.  That means it is not zero, or one, or anything.  It is undefined.  Because the value of NULL is undefined it can be tricky to work with.

Null math is using any NULL value in a mathematical equation. Null math always returns a NULL.  This is a simply concept, but NULL math is a very common PL/SQL bug.

SQL> declare
  2    n_1 number;  -- null
  3    n_2 number := 0;
  4    n_3 number := 1;
  5  begin
  6    dbms_output.put_line ('Addition:       '|| n_1 + 5);
  7    dbms_output.put_line ('Subtraction:    '|| n_1 - 5);
  8    dbms_output.put_line ('Multiplication: '|| n_1 * 5);
  9    dbms_output.put_line ('Division:       '|| n_1 + 5);
 10  end;
 11  /
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to
number conversion error
ORA-06512: at line 6

The example above fails when dbms_output.out_line attempts to convert n_1 + 5 from a number to a character because it has the value of NULL.

In the example below, the value of n_2 is shown.  Notice that there is no value when n_1 is added because it is NULL .

SQL> declare
  2    n_1 number;  -- null
  3    n_2 number := 0;
  4    n_3 number := 1;
  5  begin
  6    n_2 := n_2 + n_3;
  7    dbms_output.put_line ('Results: '|| n_2);
  8    n_2 := n_1 + n_3;
  9    dbms_output.put_line ('Results: '|| n_2);
 10  end;
 11  / 

Results: 1

This is a very common code bug and it can be hard to locate.  So with PL/SQL , the developer must contend not only with division by zero, but also null math.

NULLs can also be problematic with comparisons.  Since NULL  is undefined the developer must insure that the correct comparison is used.  In the example below, n_1 is a null while n_2 is a number.

n_1 number;              -- null
n_2 number := 4;

A comparison results in a true or a false.  Here are some comparisons using the variables above.

(n_2 > 2)    true;  --4>2.
(n_1 > 2)    false; --null>2 undefined therefore false.
(n_1 < n_2)  false; --null < 4;
                             undefined therefore false.
(n_1 = n_2)  false; --null = 4;
                             undefined therefore false.
(n_1 <> n_2) true;  --null <> 4;
                           undefined is not equal to 4.

No matter how n_1 is used in the comparison, it is still an undefined comparison and therefore false, except if the “not equal” comparison is used.  The “not equal” comparison returns true because undefined can never be equal to a value.  This is important in the if/then and loop flow control discussed in chapter two.  If the developer selects the wrong comparison, it may return a “false” due to a variable not being initialized or return a “true” using the “not equal” comparison.  If the variable n_x has not been initialized then the following will always branch to the ELSE clause.

if (n_x < 10)
  then v_line := ‘Never get here’;
else v_line := ‘always end up here’;
end if;

The variable v_line will always be ‘always end up here’ because n_x < 10 will always be false.

if (n_x <> 10)
  then v_line := ‘this is true’;
else v_line := ‘this is false’;
end if;

In the above example, v_line will always be ‘this is true’ because n_x <> 10 will always be true.  If this is the behavior desired then the code will work.  It will be very confusing to the code maintainer, who has to figure out what the initial coder was trying to accomplish.  You should use PL/SQL’s natural language structure to make code understandable and avoid “tricks” that accomplish a task but are confusing or difficult to understand.  If the code is confusing to read, it’s going to be hard to maintain.  Good PL/SQL developers get in the habit of using comments to explain what actions are being performed and why you are making calculations.  Next we will examine nested blocks and learn about the “scope” of PL/SQL variables.

The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.