Using ROWTYPE in PL/SQL VARRAY giving compilation errors

Question

If I try out the below code then it works fine:

declare
type v_varray is varray(50) of emp%rowtype;
v_emp_details v_varray;
begin
select * bulk collect into v_emp_details from emp where dept_id = 1;
for i in 1 .. v_emp_details.count
loop
    dbms_output.put_line('Emp Name : ' || v_emp_details(i).emp_name);
end loop;
end;

But if I try creating VARRAY as a database object like this:

create or replace type xyz is varray(20) of emp%ROWTYPE

Why is this giving compilation errors?

Answer 1

CREATE OR REPLACE TYPE v_varray IS VARRAY(50) OF employees%ROWTYPE;
-- Error(1,32): PLS-00329: schema-level type has illegal reference to HR.EMPLOYEES

You are not allowed to use ROWTYPE, because ROWTYPE is a PL/SQL construct and is not recognized in the SQL create type statement.

If you want to share a type you can do it this way:

CREATE OR REPLACE PACKAGE udt_types_pkg
AS
    TYPE v_varray IS VARRAY(50) OF employees%ROWTYPE;
END udt_types_pkg;
-- PACKAGE UDT_TYPES_PKG compiled

CREATE OR REPLACE PACKAGE working_pkg
AS
    l_varray  udt_types_pkg.v_varray;
END working_pkg;
-- PACKAGE WORKING_PKG compiled