Dear Tom-successors,
I have a problem which I cannot track down as it seems to happen randomly.
First of all, I'm using:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
The problem is the following:
There is a function in plsql package. This function runs a big select and calls other functions, but nothing special there.
Sometimes this function returns an
ORA-00932: inconsistent datatypes: expected - got -
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
The following types are used by the function:
CREATE OR REPLACE TYPE STRINGLIST_O AS OBJECT
(
text VARCHAR2(4000)
)
/
CREATE OR REPLACE TYPE STRINGLIST_T as TABLE of STRINGLIST_O
/
CREATE OR REPLACE TYPE STRINGLIST_LIST_T as TABLE of STRINGLIST_T
/
In order to reproduce this, I executed
create table t1 (val varchar2(4000), room_uid integer )
/
insert into table t1 (val, room_uid ) values ('test text, 621)
/
commit
/
-- the following construct is basically what make sthe procedure sometimes(!) fail.
-- of course the thing in real life is a bit more complicated.
with X as
( select stringlist_t(stringlist_o(val)) t1_val, room_uid from t1 )
select room_uid,
cast(collect(t1_val) as stringlist_list_t) t1_val_collection
from X
group by room_uid;
The issue is that even that simple thing sometimes fails, and sometimes it doesn't. If I have a session where (for unclear reasons) the original function call fails, then subsequent executions of this select will fail as well. Closing the connection and reopening it "solves" the problem in so far that now that exact same select works now (until the next "by chance" failure.
Even if the above mentioned select fails, the one below may still work:
select room_uid, CAST(COLLECT(t1_val) AS STRINGLIST_LIST_T) from(
SELECT room_uid ,stringlist_t(stringlist_o(val)) t1_val FROM t1
)GROUP BY room_uid;
My question is, do you have any idea what could cause occasional failures of this select or do you have any advice of what I should check for.
Thank you,
Jochen
PS: as this doesn't happen on each execution, the provided livesql link may not show the problem.
Sorry, I'm not able to reproduce...
select * from v$version;
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
create or replace type stringlist_o as object( text varchar2(4000) )
/
create or replace type stringlist_t as table of stringlist_o
/
create or replace type stringlist_list_t as table of stringlist_t
/
create table t1 (val varchar2(4000), room_uid integer ) ;
insert into t1 (val, room_uid ) values ('test text', 621 ) ;
commit ;
with X as
( select stringlist_t(stringlist_o(val)) t1_val, room_uid from t1)
select room_uid,
cast( collect(t1_val) as stringlist_list_t) t1_val_collection
from X
group by room_uid;
ROOM_UID T1_VAL_COLLECTION
621 oracle.sql.ARRAY@771d8c9f
I see you're on Solaris. So it's possible you're hitting a platform specific bug. In any case contact support for help.