19 December 2011

EXTEND and NOT NULL

The 6 December quiz tested your knowledge of the effect of a NOT NULL constraint on a variable declaration, in this case through the declaration of a collection whose datatype is declared as NOT NULL.

The last choice (8773) used a nested table as follows:
DECLARE
   l_string   VARCHAR2 (10);

   TYPE nt IS TABLE OF VARCHAR2 (10) NOT NULL;

   l_nt       nt := nt ();
BEGIN
   l_nt.EXTEND;
   l_nt (1) := l_string;
END;
When Oracle attempts to assign l_string to an element in l_nt, a VALUE_ERROR exception is raised. So far, so good.

But a player noticed something odd here. Marcus checked the documentation on EXTEND and found this:


Note:
EXTEND(n,i) is the only form that you can use for a collection whose elements have the NOT NULL constraint.

Yet the call to EXTEND did not raise an exception. Either the documentation is wrong or there is a bug in EXTEND. If it's the latter, it's a bug that has gone unnoticed for many versions. I have logged a comment to the Oracle Documentation team about this.

Thanks, Marcus!

2 comments:

  1. There is nothing about any exception in the doc! Only about possibility of using :)

    ReplyDelete
  2. Hello All,

    Please see this even stranger one:

    DECLARE
    TYPE nt IS TABLE OF VARCHAR2 (10) NOT NULL;

    l_nt nt := nt ();
    BEGIN
    l_nt.EXTEND(2);

    DBMS_OUTPUT.put_line('Count='||l_nt.COUNT);

    IF l_nt(l_nt.FIRST) IS NULL
    THEN
    DBMS_OUTPUT.put_line('NULL in a NOT NULL element !');
    END IF;

    l_nt(2) := l_nt(1);

    DBMS_OUTPUT.put_line('Assigning a NULL is OK !');
    END;
    /
    Count=2
    NULL in a NOT NULL element !
    Assigning a NULL is OK !

    PL/SQL procedure successfully completed.


    I think that if we will keep playing for another few years, we will probably uncover all the pl/sql bugs :) :)

    Thanks & Best Regards,
    Iudith Mentzel

    ReplyDelete