Skip to Main Content
  • Questions
  • SELECT * FROM TABLE(PL/SQL TABLE) not working

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: April 11, 2017 - 1:12 pm UTC

Last updated: April 13, 2017 - 1:40 am UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

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;

and Chris said...

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

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks a lot Chris!!

A reader, April 11, 2017 - 3:01 pm UTC


Object of ROWID

A reader, April 12, 2017 - 6:47 am UTC

Hi Chris,

One more help,

I need to bulk collect ROWID's in PL/SQL, But I am not able to create TABLE type with ROWID, Can you suggest any alternative

CREATE OR REPLACE TYPE daz_test_tab as TABLE OF ROWID;
Connor McDonald
April 13, 2017 - 1:40 am UTC

No, you could transpose with character easily, eg

select rowidtochar(...) bulk collect into ...

and

where rowid = chartorowid( my_array_of_char(i) )



More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library