Oracle ORA-22908 (reference to null table values) anomaly analysis and solution _oracle

The scenario is as follows:

--Create a type (type) Create or replace type list_obj is table of number;--create a table structure created by (name VARCHAR2) primary key Che CK (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: Type List_obj A list of quite a number of elements. Table test has three fields, and the field name is a primary key that can only use lowercase letters, numbers, underscores, strings, field IDs are numbers, and field version_list are nested tables of type List_obj.

Operation 1:

--Inserts a record insert into test (name, id) VALUES (' Mophee ', 1);

The results of the query are as follows:

Currently version_list is null (note, NULL), now we need to start maintaining the field: Insert a version number 20130403112459 into the name= ' Mophee ' record. Statement as follows:

--Inserts a version number INSERT into table (select Version_list from Test where name= ' Mophee ') values (20130403112459);

Executing the INSERT statement above will be an error!

What's the reason? The Version_list field is currently NULL and the INSERT statement above attempts to do this: table (NULL), which is to convert the null object into a table. It's counterintuitive, Oracle can't do it!

So how to solve it? The Version_list field is NULL because we did not initialize it at all. So, is it possible to initialize the problem before you insert it?

Method One: Use the following statement when you insert to test table

--Inserts a record insert into test (name, ID, version_list) VALUES (' Mophee ', 1, list_obj ());

Method Two: Initialize the version number before inserting it into version_list

--Initializes the Version_list field to the List_obj Object Update test set Version_list=list_obj () where name= ' Mophee ';

Look at the picture below:

This is the query output under Pl/sql Developer, note that the background color of the Version_list column data cell (compared to the picture above) is not light yellow (at this point the field value is NULL)! Although the internal content of the View version_list field still does not have a record, the meaning is not the same-initialization is null before initialization is the List_obj object.