How to create a type table of a the type representing the columns of a table?-CodePudding

I have a table a. I would like to create a table type of a%rowtype

create table a ( a1 integer);
CREATE TYPE t_a iS TABLE of a%ROWTYPE;

schema-level type has illegal reference to user.a

I've tried with

create table user.a ( a1 integer);
CREATE TYPE user.t_a iS TABLE of a%ROWTYPE;

same problem

But these 2 alternative solutions work

create package p_a
as
type t_a iS TABLE of a%ROWTYPE;
end;

create type o_a is object  (a1 integer);
CREATE TYPE t_oa iS TABLE of o_a;

Is there a way to make the 2 first lines of code work?

CodePudding user response：

Do it the other way round and create a object-derived table (rather than trying to create a table-derived object).

CREATE TYPE a_obj IS OBJECT (
  a1 INTEGER
);

CREATE TABLE a OF a_obj;

CREATE TYPE t_a IS TABLE OF a_obj;

Is there a way to make the 2 first lines of code work?

No, %ROWTYPE is PL/SQL syntax and it will not work in the SQL scope.

db<>fiddle here