The built-in function value returns an object that can be stored in the local variable.
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_employeeType employeeType;
3 begin
4 v_employeeType:=employeeType(100,'TestEmp',null,null,sysdate,1000,500,10);
5 insert into t_emp values v_employeeType;
6 select value(t) into v_employeeType from t_emp t where empNo=100;
7 DBMS_OUTPUT.put_line('Name: '||v_employeeType.eName);
8 end;
9 /
Name: TestEmp
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t_emp;
Table dropped.
SQL> drop type employeeType;
Type dropped.
SQL>