How to use Oracle Collection of Record type in table?

Question

I was trying to explore Oracle Collection and Record type. How do they work ? I wrote below script, but unable to compile them. What is wrong here ?

create or replace package pkg is
type ty_rec is record(empno emp.empno%type,ename emp.ename%type,sal emp.sal%type);
end pkg;
/
create or replace package body pkg is 
end pkg;
/
create or replace type ty_varray is varray(5) of pkg.ty_rec;
/
create or replace type ty_table is table of pkg.ty_rec;
/
create table tab1(
id number,
col_arr ty_varray,
col_tab ty_table
) nested table col_tab store as tab1_col_tab;
/

Also, anyone could explain the Nested Table vs Varray when we are using them in table column. How do they stores data and which one is faster ?

Note: I'm using scott schema, which has default emp table

Answer 1

Records are a PL/SQL construct. This means they cannot be used in pure SQL statements. So you need to create the "record" type as a pure SQL object:

create or replace type ty_emp_rec as object 
(empno number
  ,ename varchar2(20)
  ,sal number);
/

create or replace type ty_emp_varray is varray(5) of ty_emp_rec;
/
create or replace type ty_emp_table is table of ty_emp_rec;
/

I agree it would be highly neat if your code worked, not least because the ability to define attributes using the %TYPE syntax would be extremely useful. But alas that's restricted to the PL/SQL engine too.

This limitation is down to the ability to declare heap table columns with user-defined types, as your last example shows. Oracle requires its columns to be strongly-typed. The %TYPE syntax would create all sorts of problems here: consider what would happen if emp.empno changed from NUMBER to VARCHAR2, or vice versa.