Accessing the object table by reference : Object Table « Object Oriented « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Object Oriented
3. Object Table

The built-in function ref returns a pointer to the object.

The variable you need to declare is not of type employeeType, but ref employeeType.

SQL>
SQL>
SQL> create type employeeType is object (
  2      empNo    NUMBER,
  3      eName    VARCHAR2(10),
  4      job      VARCHAR2(9),
  5      mgr      NUMBER,
  6      hireDate DATE,
  7      sal      NUMBER,
  8      comm     NUMBER,
  9      deptNo   NUMBER,
 10      member procedure p_changeName (i_newName_tx VARCHAR2),
 11      member function  f_getIncome_nr  return VARCHAR2
 12  )
 13  /

SP2-0816: Type created with compilation warnings

SQL>
SQL> create or replace type body employeeType as
  2      member function f_getIncome_nr return VARCHAR2 is
  3      begin
  4          return sal+comm;
  5      end f_getIncome_nr;
  6      member procedure p_changeName(i_newName_tx VARCHAR2) is
  7      begin
  8          eName:=i_newName_tx;
  9      end p_changeName;
 10  end;
 11  /

SP2-0818: Type Body created with compilation warnings

SQL>
SQL> create table t_emp of employeeType;

Table created.

SQL>
SQL>
SQL>
SQL> declare
  2      v_emp_oref ref employeeType;
  3      v_employeeType employeeType;
  4
  5  begin
  6      v_employeeType:=employeeType(100,'TestEmp',null,null,sysdate,1000,500,10);
  7      insert into t_emp values v_employeeType;
  8
  9
 10      select ref(t) into v_emp_oref from t_emp t where empNo=100;
 11
 12      update t_emp t set sal=sal+100 where ref(t)=v_emp_oref;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t_emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
       100 TestEmp                         09-JUN-07       1100        500         10

SQL>
SQL> drop table t_emp;

Table dropped.

SQL> drop type employeeType;

Type dropped.

SQL>