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;

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@x.washington.edu
(replace x with u to respond)

Hi Daniel,

Thanks for the link! I actually use array processing for some of our
stored procs but ...

First off, I believe the types are consistent. If I have the select
statement is "select * from test_view" then all is well. The problems
comes up when "test_view" is used twice - hence the union to
demonstrate the problem.

As for your question: The original problem uses JDBC and complex query
that uses an object hierarchy and nested tables. This example was just
thrown together to demonstrate the problem and to rule out JDBC or the
rest of the query as being the cause of the ORA-00932 exception. As I
said, the code works fine in 10g release 2 but I need it to work on 9i.
I know there is a bug where leaving out the "CAST" in my sample code
generates the same exception on 9i and 10g release 2. The "CAST" is a
work around for the problem - I was wondering if there was also a
workaround for this problem in 9i.

Thanks,

Dale

Should have said " I know there is a bug where leaving out the "CAST"
in my sample code
generates the same exception on 9i and 10g release 1", not " 10g
release 2"

Dale wrote:
> Should have said " I know there is a bug where leaving out the "CAST"
> in my sample code
> generates the same exception on 9i and 10g release 1", not " 10g
> release 2"

"SELECT *" can be a problem when querying TABLE() expressions in 9i
(ORA-22905 cannot access rows from a non-nested table item). The
workaround is to specify a column list (which is good practice in any
case).

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

Dale wrote:
> Hi Daniel,
>
> Thanks for the link! I actually use array processing for some of our
> stored procs but ...
>
> First off, I believe the types are consistent. If I have the select
> statement is "select * from test_view" then all is well. The problems
> comes up when "test_view" is used twice - hence the union to
> demonstrate the problem.
>
> As for your question: The original problem uses JDBC and complex query
> that uses an object hierarchy and nested tables. This example was just
> thrown together to demonstrate the problem and to rule out JDBC or the
> rest of the query as being the cause of the ORA-00932 exception. As I
> said, the code works fine in 10g release 2 but I need it to work on 9i.
> I know there is a bug where leaving out the "CAST" in my sample code
> generates the same exception on 9i and 10g release 2. The "CAST" is a
> work around for the problem - I was wondering if there was also a
> workaround for this problem in 9i.
>
> Thanks,
>
> Dale

From your demo I can't tell but often what shows up in 10g will also
show up in the latter patches to 9i. You might want to check the list
of things "fixed" in patches later than yours if you do not have the
most recent 9i patches installed.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Since it is working in 10g, either remove "with clause" and embed
nested table in the union query itself or check whether any bug is
reported related to this.

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.

Thanks William!!

This little piece of information turned out the be the biggest help.

Dale