end-of-file on communication channel

[KevinS]

Hello,

The query below (names changed so I don't give away too many company
secrets!!) works fine unless you uncomment the line 'AND mp.id =
5010000' when an 'end-of-file on communication channel' error occurs.

SELECT mp.ID AS ID, mp.a2 AS a2,
mp.a3 AS a3,
SUM(nvl(ocp.b1,0)) b1, SUM(nvl(ocp.b2,0)) b2,
SUM(nvl(ocp.b3,0)) b3, SUM(nvl(ocp.b4,0)) b4,
SUM(nvl(ocp.b5,0)) b5, SUM(nvl(ocp.b6,0)) b6,
SUM(nvl(ocp.b7,0)) b7, SUM(nvl(ocp.b8,0)) b8,
SUM(nvl(ocp.b9,0)) b9, SUM(nvl(ocp.b10,0)) b10,
MAX(cp.a4) a4
FROM alpha mp, alpha cp,
beta pt, gamma psp,
delta ocp
WHERE mp.ive = 1
AND mp.ate = 0
AND mp.ram = 0
AND mp.ID = pt.prpid
AND psp.prtaskid = pt.pid
AND cp.ID = psp.prefpid
AND cp.ive = 1
AND cp.ID = ocp.ID
AND ocp.code IN (SELECT code
FROM cl
WHERE cl.parent_lookup_code = 'A'
AND cl.ive = 1)
--AND mp.id = 5010000
GROUP BY mp.ID,
mp.unique_name,
mp.NAME;

Searching on google would suggest it's an issue with the database
rather than the sql but the DBA's are scratching their heads.

Any suggestions?

Thanks

[gazzag]

What version of Oracle and, for that matter, operating system?

-g

[KevinS]

Oracle9i Enterprise Edition Release 9.2.0.7.0
on
Unix AIX 5.2

[gazzag]

If upgrading to 10gR2 is out of the question, patch to version
9.2.0.8.

HTH

-g

[KevinS]

Thanks gazzag,

One of the DBA's came up witha work araound of altering the sql as
follows.

SELECT mp.ID AS ID, mp.a2 AS a2,
mp.a3 AS a3,

ocp.code,

SUM(nvl(ocp.b1,0)) b1, SUM(nvl(ocp.b2,0)) b2,
SUM(nvl(ocp.b3,0)) b3, SUM(nvl(ocp.b4,0)) b4,
SUM(nvl(ocp.b5,0)) b5, SUM(nvl(ocp.b6,0)) b6,
SUM(nvl(ocp.b7,0)) b7, SUM(nvl(ocp.b8,0)) b8,
SUM(nvl(ocp.b9,0)) b9, SUM(nvl(ocp.b10,0)) b10,
MAX(cp.a4) a4
FROM alpha mp, alpha cp,
beta pt, gamma psp,
delta ocp
WHERE mp.ive = 1
AND mp.ate = 0
AND mp.ram = 0
AND mp.ID = pt.prpid
AND psp.prtaskid = pt.pid
AND cp.ID = psp.prefpid
AND cp.ive = 1
AND cp.ID = ocp.ID
AND ocp.code IN (SELECT code
FROM cl
WHERE cl.parent_lookup_code = 'A'
AND cl.ive = 1)

AND mp.id = 5010000
GROUP BY mp.ID,
mp.unique_name,

mp.NAME,
ocp.code;

It seems to have done the trick so as it's now logged here, perhaps it
may help someone else.

Anyone got an explanation for this?

[Dan Blum]

Does that actually produce the same result?

Were any trace files produced when you got the error? Anything in the alert log?

If not, I would run the problem version again with tracing on to see if it
provides any clue.

--
_______________________________________________________________________
Dan Blum to...@panix.com
"I wouldn't have believed it myself if I hadn't just made it up."

[HumbleDBA]

KevinS,
ora-3113 is a catch all error. There should be another error in the
alert log when the client experiences the "end of file communication"?
I saw this in 10.2.0.2, but I also got a ora-7445 exception
encountered in the alert log. In my case it was a bug, it could be
anything in your case. Probably related to not enough resources when
doing something like sorting. Thats why when the query is changed,
the resultset coming back may be different, and so it does not produce
the same errors.