Nullify the NULL
Do you know how to use NULLs in PL/SQL?
By Steven Feuerstein
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.
|
|
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
READ more Feuerstein
|
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.