Plsql select * from record is it possible?

Question

I have one record and I store into some values.

SELECT id BULK COLLECT INTO record from s;

Is it possible to

SELECT id BULK COLLECT INTO ids FROM record

I get record not table or view. Any solution? Thanks in advance.

Answer 1

How exactly are record and ids defined? And what version of Oracle are you on?

At least since 18c, assuming record is defined either in SQL or in a package specification, you can use it in subsequent queries.

create table foo( foo_id integer, foo_val varchar2(10));

insert into foo values( 1, 'a' );
insert into foo values( 2, 'b' );

create or replace package pkg_foo
as
  type foo_nt is table of foo%rowtype;
end;
/

declare
--  type foo_nt is table of foo%rowtype;
  type id_nt is table of number;
  l_foos pkg_foo.foo_nt;
  l_ids  id_nt;
begin
  select *
    bulk collect into l_foos
    from foo;
    
  select foo_id
    bulk collect into l_ids
    from table(l_foos);
end;
/

Here is a dbfiddle showing this code working.

Note that if you uncomment the line that declares the foo_nt in the declarations of the anonymous block, the code will not work correctly-- you need the collection definition to be separate.