ora - 00604: error occured at recursive SQL level 1 ORA-01003

[sandygather]

Hello colleagues

we are getting this error while executing a stored proc.
The error doesn't occur every time but whenevr it happens the database goes down.
Query executed in the proc is attached

[Volker Borowski]

Wow,

that kind of monster statement suggest some
shared_pool shortage.

Do you get other errors than the 604 right before in the alertlog. I would not bes so supprised, if this error is originally thrown by a shared_pool overflow (was it -4043?).

Do you get a corresponding trace file in USERTRACE-Destination, which has more information?

Just to get an idea:
how long does the query run?
ever monitored the memory usage during execution?
Can you provide an execution plan?
What release and patchlevel are you working on?

Volker