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

Dale wrote:
> Hi,
>
> I'm getting an "ORA-00932: inconsistent datatypes: expected - got -"
> message with the pl/sql code at the bottom. This is just an over
> simplication of the original query that was generating the message.
> Turns out that when "test_view" is used twice in the query, the oracle
> exception occurs.
>
> We had to put the "CAST(...)" in as a work around to a bug that was
> finally fixed in 10g release 2. So is this another bug in 9i? The code
> works fine in 10g release 2. Does anyone know of a work around other
> that using multiple with blocks or repeating the view multiple times?
>
> Thanks,
>
> Dale
>
> /** CREATE TYPE NUMERIC_ID_TABLE_TYPE AS TABLE OF NUMBER(9) **/
> declare
> cursor c1(arg1 numeric_id_table_type) is
> with test_view as
> (
> select * from table(cast(arg1 as numeric_id_table_type))
> )
> select * from test_view union select * from test_view;
> var1 number(9);
> begin
> open c1(numeric_id_table_type(1, 2, 3, 4, 5));
>
> fetch c1 into var1;
>
> while c1%found loop
> dbms_output.put_line('var1 = ' || var1);
> fetch c1 into var1;
> end loop;
>
> close c1;
> end;

Granted this is an oversimplified example the error message is rather straight-forward. Your data types are inconsistent. Look at the underlying objects and fix whatever inconsistencies you find.

But why are you using a cursor loop at all? Go to Morgan's Library at www.psoug.org and look up Array Processing.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)