Constructor Methods (Cont.)

Insert and update statements can also use object constructors to specify values for new rows. For example, create a table named scientists of object type name_t, and insert a row object for Albert Einstein into it: 

SQL> CREATE TABLE  scientists  OF  name_t; 

Table created.

SQL> INSERT INTO  scientists  VALUES( 'Einstein', 'Albert', 'E' ); 

1 row created.

SQL> INSERT INTO  scientists  name_t( 'Einstein', 'Albert', 'E' ); 

INSERT INTO  scientists  name_t( 'Einstein', 'Albert', 'E' )
                               *
ERROR at line 1: ORA-00928: missing SELECT keyword

However, the last SQL does not not fit the insert form. It works to update scientists, and we can insert the Einstein name object into people_tbl, using the VALUES keyword and an object constructor. 

SQL> UPDATE  scientists s 
  2    SET  s = name_t( 'Einstein', 'Andrew', 'E' ) 
  3    WHERE  VALUE( s ) = name_t( 'Einstein', 'Albert', 'E' ); 

1 row updated.

SQL> INSERT INTO  people_tbl  VALUES 
  2    ( 890123456, name_t( 'Einstein', 'Albert', 'E' ), 136 ); 

1 row created.

SQL> INSERT INTO  scientists  SELECT  p.pname  FROM  people_tbl p; 

3 rows created.

SQL> INSERT INTO  people_tbl  VALUES ( 901234567, null, null ); 

1 row created.

A null value can also be assigned to objects. For example, the last SQL adds a row to people_tbl with ssno 901234567, null pname, and null age.




      An apple a day keeps the doctor away.