Subscribe

Share

Database, SQL and PL/SQL

Nullify the NULL

Do you know how to use NULLs in PL/SQL?

By Steven Feuerstein Oracle ACE Director

November/December 2015

The concept and the reality of NULL in Oracle Database can cause you some real headaches. The main thing to remember is that NULL is never equal or not equal to anything else, even itself. A variable IS NULL or IS NOT NULL. (For reference information on working with NULLs in PL/SQL, refer to Oracle Database PL/SQL Language Reference.) Here are three questions drawn from the PL/SQL Challenge to test your knowledge of NULLs in PL/SQL.

 
Watch the video

Question 1

Which of the following choices display(s) “Done” after execution?

a.

declare
  type TInts is table of int;
  l_Coll TInts;
begin
  if not l_Coll.exists(1) then
    dbms_output.put_line('Done');
  end if;
end;
/

b.

declare
  type TInts is table of int;
  l_Coll TInts;
begin
  if l_Coll.first is null then
    dbms_output.put_line('Done');
  end if;
end;
/

c.

declare
  type TInts is table of int;
  l_Coll TInts;
begin
  if l_Coll is null then
    dbms_output.put_line('Done');
  end if;
end;
/


Quiz Summary

With a null collection, EXISTS is the only collection method that does not raise the predefined exception COLLECTION_IS_NULL.

Read a discussion of the question and answers—and look for more challenges—at the PL/SQL Challenge.

 

Question 2

Which of the answer choices can be used in place of #REPLACE# in the following block so that after execution, “1500” will be displayed?

DECLARE
   l_current    NUMBER := 1000;
   l_proposed   NUMBER := NULL;
   l_average    NUMBER := 1500;
BEGIN
   DBMS_OUTPUT.put_line (#REPLACE#);
END;
/'

a.

GREATEST (l_current, l_proposed, l_average)

b.

GREATEST ( NVL (l_current, -1),
   NVL (l_proposed, -1),
   NVL (l_average, -1))

c.

NVL (GREATEST (l_current, l_proposed, l_average), -1)


Quiz Summary

GREATEST returns the greatest value (example, largest number) from a list of expressions. You can pass a variable number of expressions to GREATEST, but if any of those expressions evaluate to NULL, the function will return NULL.

Read a discussion of the question and answers—and look for more challenges—at the PL/SQL Challenge.

 

Question 3

Which of the following choices display(s) “Done” (and perhaps other text as well) after execution?

a.

DECLARE
   l_lo   INTEGER := 1000;
   l_hi   INTEGER;
BEGIN
   FOR indx IN l_lo .. l_hi
   LOOP
      DBMS_OUTPUT.put_line (indx);
   END LOOP;
   DBMS_OUTPUT.put_line ('Done');
END;
/

b.

DECLARE
   l_lo   INTEGER := 1000;
   l_hi   INTEGER := 1001;
BEGIN
   FOR indx IN l_lo .. l_hi
   LOOP
      DBMS_OUTPUT.put_line (indx);
   END LOOP;
   DBMS_OUTPUT.put_line ('Done');
END;
/

c.

DECLARE
   l_lo   INTEGER := 1000;
   l_hi   INTEGER;
BEGIN
   FOR indx IN NVL (l_lo, 999) .. NVL (l_hi, -1)
   LOOP
      DBMS_OUTPUT.put_line (indx);
   END LOOP;
   DBMS_OUTPUT.put_line ('Done');
END;
/


Quiz Summary

If either of the expressions provided for the low and high values of a numeric FOR loop evaluates to NULL, Oracle Database will raise the VALUE_ERROR exception (ORA-06502).

Read a discussion of the question and answers—and look for more challenges—at the PL/SQL Challenge.


Next Steps

 TEST your PL/SQL knowledge

READ more Feuerstein
 bit.ly/omagplsql
 stevenfeuersteinonplsql.blogspot.com

 LEARN more about PL/SQL

 

Photography by Scott Webb, Unsplash