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

From: Dale <dalesingh_at_gmail.com>
Date: 19 Apr 2006 08:06:46 -0700
Message-ID: <1145459206.541949.93140@v46g2000cwv.googlegroups.com>

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 Received on Wed Apr 19 2006 - 10:06:46 CDT