Re: Parallel slave terminated with error

Thank You So much Andy.

I got to know this procedure is called from a controlM job. And this error is getting printed in that log. So yes, as you suggested, it seems like a simpel RAISE keyword should be good to catch/log the exact error here.

On Sat, Jul 2, 2022 at 2:03 AM Andy Sayer <andysayer_at_gmail.com> wrote:

> 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