Pl/Sql Casting Objects: error using the TABLE operator

From: <uneboite_at_hotmail.com>
Date: 4 Jul 2005 11:53:46 -0700
Message-ID: <1120503226.325527.214080@o13g2000cwo.googlegroups.com>

Hi!

I'm trying to use the TABLE operator in a pl/sql block with a pl/sql nested table of objects.

I've succeeded using CASTING, TABLE and MULTISET with a table of dates.

Giving these types and table definitions:
/* creation of a table */

whenever sqlerror continue;
drop table my_table
/

create table my_table (col1 date, col2 date)
/

insert into my_table values (to_date('1900-01-01', 'yyyy-mm-dd'), to_date('1900-01-01', 'yyyy-mm-dd'));
insert into my_table values (to_date('1900-01-02', 'yyyy-mm-dd'), to_date('1900-01-02', 'yyyy-mm-dd'));

/* creation of types */

drop type my_type_table_date
/

drop type my_type_table_object
/

drop type my_type_object
/

create type my_type_table_date is table of date
/

create type my_type_object is object(ddb date, ddf date)
/

create type my_type_table_object is table of my_type_object
/

I would appreciate a lot if someone could tell me why (oh why) this works:

declare

   vtbl my_type_table_date;
begin

   SELECT CAST(MULTISET(select col1

                        from my_table
                       )
               AS my_type_table_date
              )

   INTO VTBL
   from dual
   ;

   for vrow in (select column_value as col1

                from table(cast(vtbl as my_type_table_date
                               )
                          )
               ) loop
      dbms_output.put_line(to_char(vrow.col1, 'yyyy-mm-dd'));

   end loop;
end;
/

but not this:

declare

   vtbl my_type_table_object;
begin

   SELECT CAST(MULTISET(select col1, col2

                        from my_table
                       )
               AS my_type_table_object
              )

   INTO VTBL
   from dual
   ;

   for vrow in (select column_value as col1

                from table(cast(vtbl as my_type_table_object
                               )
                          )
               ) loop
      dbms_output.put_line(to_char(vrow.col1, 'yyyy-mm-dd'));

   end loop;
end;
/

1. column_value "seems" to be the problem: I get a PL/SQL: ORA-00904: "COLUMN_VALUE": invalid identifier at runtime, in sqlplus
2. in the "SELECT CAST(MULTISET(select col1, col2... statement, should I use the constructor of my object or is it done implicitly?

Thanks in advance

Joe001 Received on Mon Jul 04 2005 - 13:53:46 CDT