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> 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> 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>