Re: ORA-00932: inconsistent datatypes: expected - got -; oracle 9i bug???

Dale wrote:
> Thanks William,
>
> But how do you not do a "select * " with a table or number(9)? I know
> it's not best practice, but the "select *" works if I remove the union.
> The problem comes up when the view is used twice.
>
> I re-did an example with and tried to take out the bits that people
> have commented on out, but it still does not work in 9i.
>
> /* data_type_table is a table of object (datatype varchar(16)) */
> declare
> var1 data_table_type := new data_table_type();
> begin
> var1.extend();
> var1(1) := new data_type('123');
>
> for x in (with test_view as (select datatype from table(cast(var1 as
> data_table_type))) select datatype from test_view union select datatype
> from test_view) loop
> dbms_output.put_line('VALUE ' || x.datatype);
> end loop;
> end;
>
> The only solution I can come up with is a stored proc or ps/sql block
> that breaks out the "with" block and stores the result in a collection;
> then use the collection in a in my select statement.
>
> Dale

SQL> CREATE TYPE integer9_tt AS TABLE OF NUMBER(9)   2 /

Type created.

SQL> SELECT column_value
  2 FROM TABLE(integer9_tt(8,2,6,54,3));

COLUMN_VALUE

           8
           2
           6
          54
           3

5 rows selected. Received on Thu Apr 20 2006 - 08:50:58 CDT