Hi Connor, Chris
can you please help,
select * from table(cast(t_daz as daz_test_tab)) is not working here..
create table tb_test
as select object_name, object_id from user_objects where rownum <= 10;
create type daz_test as object (name varchar2(100));
create type daz_test_tab as table of Daz_test index by binary integer;
declare
t_daz daz_test_tab := daz_test_tab();
begin
select object_name
bulk collect into t_daz
from tb_test
where rownum <= 10;
for r_item in (select * from table(cast(t_daz as daz_test_tab)))
loop
dbms_output.put_line(r_item.id);
end loop;
end;
There's couple of problems here:
- "index by binary integer" is PL/SQL only, but you're creating it with SQL
- You need to select the object when bulk collecting your data
- ID isn't an attribute of your type, so the reference to this in put_line will fail
You can create daz_test_tab as a PL/SQL type. But you can only use variables of that type in the table() operator from 12.1 up. You're on 11.2 so you need to stick with SQL types:
create table tb_test
as select object_name, object_id from user_objects where rownum <= 10;
create type daz_test as object (name varchar2(100));
/
create type daz_test_tab as table of Daz_test;
/
declare
t_daz daz_test_tab := daz_test_tab();
begin
select daz_test(object_name)
bulk collect into t_daz
from tb_test
where rownum <= 5;
for r_item in (select * from table(cast(t_daz as daz_test_tab)))
loop
dbms_output.put_line(r_item.name);
end loop;
end;
/
AAA
ABC
ADDRESS_TY
ADDRESS_TYY
AGE_I
If you want to use the PL/SQL type in 12.1, you need to declare it in a package:
create table tb_test
as select object_name, object_id from user_objects where rownum <= 10;
create type daz_test as object (name varchar2(100));
/
create or replace package pkg as
type daz_test_tab is table of Daz_test;
end pkg;
/
declare
t_daz pkg.daz_test_tab;
begin
select daz_test(object_name)
bulk collect into t_daz
from tb_test
where rownum <= 5;
for r_item in (select * from table(t_daz))
loop
null;
dbms_output.put_line(r_item.name);
end loop;
end;
/
A
AAA
ABC
ABC_TEMP_TABLE
ACCOUNTS