create Oracle type of the same structure as an existing Oracle table

Question

I'm trying to save a few Java collections in Oracle PL/SQL stored procedure.

Does anyone know, if there is a way to create Oracle type of the same structure as an existing Oracle table? I would like to avoid creating a lot of CREATE OR REPLACE TYPE t_row AS OBJECT(...); , then CREATE OR REPLACE TYPE t_list AS TABLE OF t_row; for each Java collection.

In other words I would like to create something like:

CREATE OR REPLACE TYPE typename AS TABLE OF schema.existing_table;

I was trying to find some information over the web and it seems like there aren't any similar topics.

Any hint would be very much appreciated.

Answer 1

Does the type need to be a SQL type? If you're OK with a PL/SQL type, you can declare a collection of %ROWTYPE records. Presumably, you'd create the types in one or more packages and then use them wherever appropriate.

create table foo (
  col1 number,
  col2 number
);

create or replace package my_collections
as
  type foo_tbl is table of foo%rowtype;
end;
/

declare
  foos my_collections.foo_tbl := new my_collections.foo_tbl();
begin
  foos.extend(1);
  foos(1).col1 := 1;
  foos(1).col2 := 2;

  dbms_output.put_line( foos.count );
end;
/