Constructor Methods 

 SQL> CREATE TYPE  emp_typ  AS OBJECT (
   2    emp_number  NUMBER(5),
   3    emp_name    VARCHAR2(32) );
   4  /
 Type created.

 SQL> CREATE TABLE  emp  OF  emp_typ;
 Table created.

The constructor method 
 SQL> INSERT INTO  emp  VALUES ( emp_typ( 101, 'Kids Next Door' ) );
 1 row created.

 SQL> SET SERVEROUTPUT ON;

 SQL> DECLARE
   2    new_emp  emp_typ := emp_typ( 202, 'Powerpuff Girls' );
   3    alt_emp  emp_typ;
   4  BEGIN
   5    DBMS_OUTPUT.PUT_LINE( new_emp.emp_name );
   6    alt_emp := emp_typ( 331, 'Digimon' );
   7    DBMS_OUTPUT.PUT_LINE( alt_emp.emp_number );
   8    SELECT  VALUE( e )  INTO  alt_emp
   9      FROM  emp e  WHERE  e.emp_number = 101;
  10    DBMS_OUTPUT.PUT_LINE( alt_emp.emp_name );
  11  END;
  12  /

 Powerpuff Girls
 331
 Kids Next Door

 PL/SQL procedure successfully completed.

Question: Display the full emp_typ object for the employee 101 of “Kids Next Door.” 

 SQL> SELECT  VALUE( e )
   2    FROM  emp e  WHERE  e = emp_typ( 101, 'Kids Next Door' );

 FROM  emp e  WHERE  e = emp_typ( 101, 'Kids Next Door' )
                     *
 ERROR at line 2:
 ORA-00904: "E": invalid identifier

 SQL> SELECT  VALUE( e )
   2    FROM  emp e  WHERE  VALUE( e ) = emp_typ( 101, 'Kids Next Door' );

 VALUE(E)(EMP_NUMBER, EMP_NAME)
 ------------------------------------------------------
 EMP_TYP(101, 'Kids Next Door')




      Why should you never date a tennis player?    
      Because love means nothing to them.