PIPELINED FUNCTIONS and Table joins bugs
411850Oct 25 2005 — edited Oct 25 2005Hi guys,
I am using a lot of pipelined functions in my database, and recently I came across a strange bug. Oracle 9.2.0.5.
In a complex queries when I need to join the result of a pipelined function with other tables I am getting ORA-03113: end-of-file on communication channel and this has nothing to do with session timeout or anything of that kind because I am running a query from the TOAD and it is fast enough, it returns the response in merely 1-2 seconds. And what's more interesting, this seems to be a flowing error, it may get disconnected once in 5-10 executes of the same query. It doesn't happen every time I run it.
Query looks like
SELECT *
FROM table1 a, TABLE( CAST (my_pkg.get_pipelined_table (a.id) AS my_type)) b
WHERE a.id = b.id
The joins may be an outer joins, the number of tables may be much higher, but this is what caused my queries to fail.
Have you ever experiences anything like this? Is there any patch or guideline I should follow to fix this? I feel like this is not normal behaviour and must be some patch to fix it...
Thanks,
Alexey