Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-00932: inconsistent datatypes: expected - got -; oracle 9i bug???

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

From: William Robertson <william.robertson_at_bigfoot.com>
Date: 20 Apr 2006 06:50:58 -0700
Message-ID: <1145541058.272168.12360@e56g2000cwe.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US