Re: Parallel slave terminated with error

I don’t think you’ll find it in the traces, it got to your exception block and was parsed out. If you had a sql trace running then the error number may have been reported there. I say your best bet is to fix your error handling code and rerun it with the same input parameters so that the same error is hit (most likely some constraint violation).

Sqlerrm is only the top level of your exception stack - the ora-12801. I would just replace raise_application_error(..) with raise;

You could log the full stack (using the dbms_utility.format_error_backtrace call) to a table, and report the unique id for the inserted row in your raise_application_error message, but only do this if you want to obfuscate this detail from the caller. It seems to me like obfuscation was only an accident, so “raise” (on with no arguments) is probably what you want.

Thanks,
Andy

On Fri, Jul 1, 2022 at 1:23 PM, Lok P <loknath.73_at_gmail.com> wrote:

> Thank You Andy.
> We were trying to see if we can get the cause of the error from trace
> itself without code change. But it seems like this error may not be
> generated or get written to the trace automatically. or even alert log etc.
>
> And yes, the exception block doesn't look good. It's as below. So should
> we substring the higher length text from "sqlerrm" say from 100 to 500 i.e
> from current "SUBSTR (SQLERRM, 1, 100) " to "SUBSTR (SQLERRM, 1, 500) "
> and then the exact error will be captured?
>
> EXCEPTION
>
> WHEN OTHERS
>
> THEN
>
> ROLLBACK;
>
> EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL DML';
>
> EXECUTE IMMEDIATE 'ALTER SESSION SET workarea_size_policy=auto';
>
> raise_application_error ( -20001, SUBSTR (SQLERRM, 1, 100) || '
> Error Code: ' || TO_CHAR (SQLCODE));
>
> END;
>
>
>
> On Sat, Jul 2, 2022 at 1:22 AM Andy Sayer <andysayer_at_gmail.com> wrote:
>
>> Hi Lok,
>>
>> Sounds like your pl/sql code has removed the full error stack in an
>> exception block and replaced it with a raise application error and a single
>> line from the stack (with sqlerrm).
>>
>> I suggest changing your code so that the stack gets reported back, or
>> logged somewhere. You want to use dbms_utility.format_error_backtrace, or
>> you want to just use raise (which will send the whole stack) instead of
>> raise_application_error.
>>
>> Potentially, your exception block code isn’t doing anything useful and
>> you should just remove it and allow pl/sql to do the work of reporting back
>> the real error for you.
>>
>> Thanks,
>> Andy
>>
>> On Fri, Jul 1, 2022 at 12:44 PM, Lok P <loknath.73_at_gmail.com> wrote:
>>
>>> Hello All, We are seeing below error for one of our procedure execution
>>> and later the rerun completing successfully. It happened many times. The
>>> error says one of the parallel slave getting terminated but doesn't say
>>> anything about what exact Ora- error caused the parallel slave to
>>> terminate.
>>>
>>> We were thinking if we will get the parallel slave failure details
>>> automatically logged in the trace, but we didn't see anything in the trace
>>> file either when searched with *12801*. Any way to find the exact cause of
>>> this failure here?
>>>
>>> ORA-20001: ORA-12801: error signaled in parallel query server P005,
>>> instance db01.App1.com:App1u Error Code: -12801
>>>
>>> ORA-06512: at "APP_USER.APP1_SP", line 881
>>>
>>> ORA-06512: at line 2
>>>
>>> The line-881 in the procedure is pointing to inside 'raise application
>>> error', so it doesn't state the exact statement which failed, but anyway
>>> even if we will get the statement we need to understand what exact error is
>>> causing the parallel slave to get terminated.
>>>
>>

--
http://www.freelists.org/webpage/oracle-l