Oracle PL/SQL Tutorial/Object Oriented/DEREF function

DEREF operator

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT
  2              (line1 VARCHAR2(20),
  3               line2 VARCHAR2(20),
  4               city VARCHAR2(20),
  5               state_code VARCHAR2(2),
  6               zip VARCHAR2(13),
  7    MEMBER FUNCTION get_address RETURN VARCHAR2,
  8    MEMBER PROCEDURE set_address
  9              (addressLine1 VARCHAR2,
 10               addressLine2 VARCHAR2,
 11               address_city VARCHAR2,
 12               address_state VARCHAR2,
 13               address_zip VARCHAR2)
 14  );
 15  /
Type created.
SQL> CREATE OR REPLACE TYPE BODY address AS
  2    MEMBER FUNCTION get_address RETURN VARCHAR2
  3    IS
  4    BEGIN
  5      RETURN (SELF.line1||" "||SELF.line2||" "||SELF.city||", "|| SELF.state_code||" "||SELF.zip);
  6    END get_address;
  7    MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
  8                  addressLine2 VARCHAR2,
  9                  address_city VARCHAR2,
 10                  address_state VARCHAR2,
 11                  address_zip VARCHAR2)
 12    IS
 13    BEGIN
 14      line1 :=addressLine1;
 15      line2 :=addressLine2;
 16      city :=address_city;
 17      state_code :=address_state;
 18      zip :=address_zip;
 19    END set_address;
 20  END;
 21  /
Type body created.
SQL>
SQL>
SQL> CREATE TABLE employee(
  2   empid           number(10)PRIMARY KEY,
  3   lastname        varchar2(30)NOT NULL,
  4   firstname       varchar2(30)NOT NULL,
  5   middle_initial  varchar2(2),
  6   emp_address REF address);
Table created.
SQL>
SQL>
SQL> DECLARE
  2    addressValue_ref REF address;
  3    addressValue address;
  4  BEGIN
  5    SELECT DEREF(addressValue_ref)
  6    INTO addressValue
  7    FROM DUAL;
  8  END;
  9  /
PL/SQL procedure successfully completed.
SQL> DECLARE
  2    addressValue address;
  3  BEGIN
  4    SELECT DEREF(e.emp_address) INTO addressValue FROM employee e WHERE e.lastname ="LAKSHMAN";
  5
  6    DBMS_OUTPUT.PUT_LINE(addressValue.line1||" "||addressValue.line2);
  7
  8    DBMS_OUTPUT.PUT_LINE(addressValue.city||", "||addressValue.state_code||" "||  addressValue.zip);
  9
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

SQL>
SQL> UPDATE employee SET emp_address =NULL WHERE emp_address IS DANGLING;
0 rows updated.
SQL>
SQL> drop table employee;
Table dropped.
SQL>