Oracle exception [ORA-22908: NULL Table value reference] Problem Analysis and Solution

The scenario is as follows:

 --  Creation type)  Create   Or   Replace Type list_obj Is   Table   Of   Number  ;  --  Create Table Structure  Create   Table  Test (name  Varchar2 (30 ) Primary   Key   Check (Regexp_like (name, '  ^ ([A-Z] | [0-9] | _) + $  '  ), ID  Number   Not   Null  , Version_list list_obj) nested  Table Version_list store As Version_list;

Scenario Description: The list_obj type is a list of numbers with equivalent elements. Table Test has three fields. The field name is a string consisting of only lowercase letters, numbers, and underscores. The field ID is a number and cannot be empty; the version_list field is a nested table of the list_obj type.

Operation 1:

 
--Insert a recordInsert IntoTest (name, ID)Values('Mophee',1);
 

The query result is as follows:

Currently, version_list is empty (Note: It is null). Now we need to maintain this field: Insert a version number 20130403112459 to the name = 'mophee 'record. The statement is as follows:

--Insert a version numberInsert Into Table(SelectVersion_listFromTestWhereName='Mophee')Values(20130403112459);
 

If you execute the preceding insert statement, an error is returned!

Why? The version_list field is currently null. The preceding insert statement attempts to perform this action: Table (null), that is, to visualize null into a table. This is against common sense. Oracle cannot do it!

How can this problem be solved? The version_list field is null because it is not initialized at all. So, can the problem be solved shortly after initialization of insert?

Method 1: Use the following statement to insert data to the test table:

--Insert a recordInsert IntoTest (name, ID, version_list)Values('Mophee',1, List_obj ());
 

Method 2: Initialize version_list before inserting version numbers.

 
--Initialize the version_list field as a list_obj object.UpdateTestSetVersion_list=List_obj ()WhereName='Mophee';
 

See:

This is the query output under PL/SQL developer. Note that the background color of the Data cell in the version_list column is not pale yellow (the field value is null at this time )! Although there is still no record for viewing the internal content of the version_list field, it is different in the sense that it is null before initialization and list_obj object after initialization.

Finished!