It is quite a long time I did shot the pianist in my blog… probably patience and perseverance are better than aggressive behavior, but today I have been transfixed by an answer from metalink.
Sometimes the issues I submit are a bit exotic but there it was a join with one view using one function and dual returning wrong results. Reproducible at will on any db 10.2.0.4 and below.
After 2 weeks the helpful engineer told me it is fixed with patch 6471020 and in 11g. I asked if he tried it but he said no. As the patch is not available on my platform I asked if could try it but then he came back with an amazing workaround he became from an ARE (advanced resolution engineer) :
Remove the function from the view.
What are those .plz dump files in my user dump directory ?
-rw-r--r-- 1 oracle dba 15168 Oct 6 14:34 _anon__3ca8c5e38__AB.plz
-rw-r----- 1 oracle dba 15883 Oct 6 14:45 db01_ora_10061.trc
-rw-r--r-- 1 oracle dba 15168 Oct 6 14:45 _anon__3c929b088__AB.plz
-rw-r----- 1 oracle dba 15895 Oct 6 14:47 db01_ora_10666.trc
-rw-r--r-- 1 oracle dba 15168 Oct 6 14:47 _anon__3c8651198__AB.plz
let’s check one, briefly :
*** ASSERT at file pdw4.c, line 2080; Type 0xffffffff7d79fb40 has no MAP method.
Source Location = _anon__3d2474b28__AB[1, 7]
== Dump of OPT Context Object ffffffff7c519ec8. ==
Tue Oct 6 16:31:11 2009
Event 10944 = 0
plsp = ffffffff7fff67a8
lu = 3c9c18210
Diana root = 0x20014 = 131092
Diana proc = 0x20012 = 131090
Graph = ffffffff7d774d70
Dump file = /app/oracle/admin/DB01/udump/_anon__3d2474b28__AB.plz
CG = 0
It is a kind of dump file, apparently. I could not find details on metalink. I generate the one above in 10.2.0.4 sparc with the following code
create or replace type t1 as object (x number)
create or replace type t2 as table of t1
exec if t1(1)member of t2()then null;end if
BEGIN if t1(1)member of t2()then null;end if; END;
Error at line 10
ORA-06550: line 1, column 7:
PLS-00801: internal error [*** ASSERT at file pdw4.c, line 2080; Type 0xffffffff7d7ba280 has no MAP method.; _anon__3c929b088__AB[1, 7]]