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> 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> DECLARE
2 addressValue address;
3 BEGIN
4 SELECT emp_address INTO addressValue FROM employee WHERE lastname ='LAKSHMAN';
5 DBMS_OUTPUT.PUT_LINE('The address of the employee LAKSHMAN is');
6 DBMS_OUTPUT.PUT_LINE(addressValue.line1||' '||addressValue.line2);
7 DBMS_OUTPUT.PUT_LINE(addressValue.city||', '||addressValue.state_code||' '||addressValue.zip);
8 END;
9 /
SELECT emp_address INTO addressValue FROM employee WHERE lastname ='LAKSHMAN';
*
ERROR at line 4:
ORA-06550: line 4, column 10:
PL/SQL: ORA-00932: inconsistent datatypes: expected REF
RNTSOFT.ADDRESS got RNTSOFT.ADDRESS
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored
SQL>
SQL> SELECT * FROM employee e WHERE e.emp_address.city ='Vancouver';
no rows selected
SQL>
SQL> SELECT e.emp_address.get_address() FROM employee e;
no rows selected
SQL>
SQL> DROP TABLE employee;
Table dropped.