Tratamento de exceções nas Procedures | GPO (Grupo de Profissionais Oracle)

Este tópico contém 2 respostas, 2 vozes e foi atualizado pela última vez 18 anos, 1 mês atrás por ronaldoxoliveira.

Visualizando 3 posts - 1 até 3 (de 3 do total)

Autor

Posts
28 de dezembro de 2006 às 8:40 pm #77844
ronaldoxoliveira
Participante
Qual a melhor forma de realizar tratamento de exceção ?
Atualmente trabalho assim:

EXCEPTION
WHEN INACTIVE_REASON_NOT_SPECIFIED THEN
p_DBMSErrCode := 5591;
p_sqlErrMessage := ‘Inactive reason not specified.’;
WHEN SUCCESS_OP_STATE_NOT_CHANGED THEN
p_DBMSErrCode := 5592;
p_sqlErrMessage := ‘Operation is done. But cannot change the state of the entity. Entity has another reason to maintain the status.’;
WHEN CANNOT_CHANGE_STATE THEN
p_DBMSErrCode := 5593;
p_sqlErrMessage := ‘Cannot change the state of the entity. Entity was already in the desired status.’;
WHEN NO_ENTITY_FOUND THEN
p_DBMSErrCode := 5595;
p_sqlErrMessage := ‘Entity not found.’;
WHEN ACCESS_DENIED_EXCEPTION THEN
p_DBMSErrCode := PKG_AC_SYSADMINUTIL.ACCESS_DENIED_EXCEPTION; –5596
p_sqlErrMessage := PKG_AC_SYSADMINUTIL.ACCESS_DENIED_EXCEPTION_TXT;
WHEN OTHERS THEN
p_DBMSErrCode := SQLCODE;
p_sqlErrMessage := DBMS_UTILITY.FORMAT_ERROR_STACK || ‘; ‘ ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;

Estou pensando em criar uma SP para tratar as Exceptions. Todo tratamento de erro seria passado para esta SP. Tipo:

(…)
EXCEPTION
WHEN OTHERS THEN
O_PROCURADO_ExceptionIdentifierCode := A_PROCURADA_GetExceptionIdentifierFunction; — Serah que eh uma funcao ou uma variavel global…?
spu_ac_DefaultExceptionHandler(O_PROCURADO_ExceptionIdentifierCode,p_DBMSErrCode,p_ApplicationErrorCode, p_ReturnCode, p_ReturnMsg);
END spu_ac_ProcedureChamadora;

PROCEDURE spu_ac_DefaultExceptionHandler
(
O_PROCURADO_ExceptionIdentifierCode IN NUMBER,
p_DBMSErrCode IN NUMBER,
p_ApplicationErrorCode IN NUMBER,
p_ReturnCode OUT NUMBER,
p_ReturnMsg OUT VARCHAR2
)
IS
BEGIN

CASE O_PROCURADO_ExceptionIdentifierCode
WHEN INACTIVE_REASON_NOT_SPECIFIED_CODE THEN
p_ReturnCode := 5591; — Substituir valor numerico por constante…
p_ReturnMsg := ‘Inactive reason not specified.’;
WHEN SUCCESS_OP_STATE_NOT_CHANGED_CODE THEN
p_ReturnCode := 5592; — Substituir valor numerico por constante…
p_ReturnMsg := ‘Operation is done. But cannot change the state of the entity. Entity has another reason to maintain the status.’;
WHEN CANNOT_CHANGE_STATE_CODE THEN
p_ReturnCode := 5593; — Substituir valor numerico por constante…
p_ReturnMsg := ‘Cannot change the state of the entity. Entity was already in the desired status.’;
WHEN NO_ENTITY_FOUND_CODE THEN
p_ReturnCode := 5595; — Substituir valor numerico por constante…
p_ReturnMsg := ‘Entity not found.’;
WHEN ACCESS_DENIED_EXCEPTION_CODE THEN
p_ReturnCode := PKG_AC_SYSADMINUTIL.ACCESS_DENIED_EXCEPTION; –5596
p_ReturnMsg := PKG_AC_SYSADMINUTIL.ACCESS_DENIED_EXCEPTION_TXT;
ELSE
p_ReturnCode := 5599; — Substituir valor numerico por constante…
p_ReturnMsg := DBMS_UTILITY.FORMAT_ERROR_STACK || ‘; ‘ ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
END CASE;

END spu_ac_DefaultExceptionHandler;

Alguem tem alguma idéia melhor ?
28 de dezembro de 2006 às 9:06 pm #77846
chduarte
Participante
Se o tratamento de suas exceptions forem todas iguais, acredito que o fonte esta bom mas o problema é que geralmente voce precisa tratar de forma diferente uma mesma exception.

Eu particularmente nao escrevo muito codigo para as exceptions nao. Somente o basico. Isso com certeza ira variar de cada um.

Grande []
28 de dezembro de 2006 às 10:52 pm #77849
ronaldoxoliveira
Participante
Novas versões:

— //////////////////////////////////////////////////////////////////////////
—
— Purpose : Stored procedure to handle error messages and codes.
— Name : spu_ac_DefaultExceptionHandler
— Created : 28/12/2006
— Resp. : Fulano da Silva
—
— //////////////////////////////////////////////////////////////////////////
PROCEDURE spu_ac_DefaultExceptionHandler
(
p_ExceptionIdentifierCode IN NUMBER,
p_AppCode IN NUMBER,
p_ArgumentLog IN VARCHAR2,
p_resultCode OUT INTEGER,
p_sqlCode OUT INTEGER,
p_sqlErrMessage IN OUT STRING

) IS
BEGIN
```
CASE p_ExceptionIdentifierCode
  WHEN pkg_ac_SysAdminUtil.PARAMETER_NOT_FOUND THEN
    p_resultCode    := p_AppCode;
    p_sqlCode       := SQLCODE;
    p_sqlErrMessage := pkg_ac_SysAdminUtil.FormatExceptionMsg(p_ArgumentLog, p_sqlErrMessage, pkg_ac_SysAdminUtil.PARAMETER_NOT_FOUND_TXT);
  WHEN pkg_ac_SysAdminUtil.NO_ENTITY_FOUND THEN
    p_resultCode    := p_AppCode;
    p_sqlCode       := SQLCODE;
    p_sqlErrMessage := pkg_ac_SysAdminUtil.FormatExceptionMsg(p_ArgumentLog, p_sqlErrMessage, pkg_ac_SysAdminUtil.NO_ENTITY_FOUND_TXT);
  WHEN pkg_ac_SysAdminUtil.ACCESS_DENIED_EXCEPTION THEN
    p_resultCode    := p_AppCode;
    p_sqlCode       := SQLCODE;
    p_sqlErrMessage := pkg_ac_SysAdminUtil.FormatExceptionMsg(p_ArgumentLog, p_sqlErrMessage, pkg_ac_SysAdminUtil.ACCESS_DENIED_EXCEPTION_TXT);
  WHEN pkg_ac_SysAdminUtil.ENTITYTYPE_NOT_ALLOWED THEN
    p_resultCode    := p_AppCode;
    p_sqlCode       := SQLCODE;
    p_sqlErrMessage := pkg_ac_SysAdminUtil.FormatExceptionMsg(p_ArgumentLog, p_sqlErrMessage, pkg_ac_SysAdminUtil.ENTITYTYPE_NOT_ALLOWED_TXT);
  ELSE
    p_resultCode    := pkg_ac_SysAdminUtil.GENERIC_EXCEPTION_HANDLER;
    p_sqlCode       := SQLCODE;
    p_sqlErrMessage := pkg_ac_SysAdminUtil.FormatExceptionMsg(p_ArgumentLog, p_sqlErrMessage, pkg_ac_SysAdminUtil.GENERIC_EXCEPTION_HANDLER_TXT);
END CASE;

IF p_sqlCode  0 THEN
  pkg_ac_Log.spu_ac_InsertLog
     (
       p_LOG_NUM_NUMBER         =&gt; 5599,
       p_LOG_DTM_EVENTTIME      =&gt; SYSDATE,
       p_LOG_TXT_SERVERNAME     =&gt; 'DBMS-Server',
       p_LOG_TXT_PROCESSNAME    =&gt; 'spu_ac_DefaultExceptionHandler',
       p_LOG_NUM_PID            =&gt; 0,
       p_LOG_NUM_SOURCECODELINE =&gt; 0,
       p_LOG_TXT_SOURCECODEFILE =&gt; 'spu_ac_DefaultExceptionHandler',
       p_LOG_TXT_DESCRIPTION    =&gt; p_sqlErrMessage
     );
END IF;
```
END spu_ac_DefaultExceptionHandler;

— //////////////////////////////////////////////////////////////////////////
—
— Purpose : Description of the code temaple.
— Name : spu_ac_ProcedureName
— Created : dd/mm/yyyy
— Resp. : Initials of the programmer that created the procedure
—
— Version : dd/mm/yyyy
— Resp. : Initials of the programmer that created the version/update the procedure
— Comments: If necessary.
—
— Version : dd/mm/yyyy
— Resp. : Initials of the programmer that created the version/update the procedure
— Comments: If necessary.
—
— Version : dd/mm/yyyy
— Resp. : Initials of the programmer that created the version/update the procedure
— Comments: If necessary.
—
— Version : dd/mm/yyyy
— Resp. : Initials of the programmer that created the version/update the procedure
— Comments: If necessary.
—
— //////////////////////////////////////////////////////////////////////////
PROCEDURE spu_ac_ProcedureName
(
p_UserName IN VARCHAR2,
p_UserPassword IN VARCHAR2,
```
p_Argument_1              IN NUMBER,
p_Argument_2              IN NUMBER,
p_Argument_3              IN NUMBER,
-- ...
p_Argument_n              IN NUMBER,

p_resultCode              OUT INTEGER,
p_sqlCode                 OUT INTEGER,
p_sqlErrMessage           OUT STRING
```
) IS
```
p_ArgumentLog           VARCHAR2(5000);

NO_ENTITY_FOUND         EXCEPTION;
PARAMETER_NOT_FOUND     EXCEPTION;
ENTITYTYPE_NOT_ALLOWED  EXCEPTION;
ACCESS_DENIED_EXCEPTION EXCEPTION;
```
BEGIN
```
p_resultCode    := 0;
p_sqlCode       := 0;
p_sqlErrMessage := '';

p_ArgumentLog    :=
    'p_UserName='    || p_UserName     || ';' ||
    'p_Argument_1='  || p_Argument_1   || ';' ||
    'p_Argument_2='  || p_Argument_2   || ';' ||
    'p_Argument_3='  || p_Argument_3   || ';' ||
    -- ...
    'p_Argument_n='  || p_Argument_n   || ';'
    ;

--------------------------------------------------------------------------------
IF pkg_ac_SysAdminUtil.VerifyUserOperationPermission(p_UserName         =&gt; p_UserName,
                                                     p_UserPassword     =&gt; p_UserPassword,
                                                     p_OperationCode    =&gt; 0,
                                                     p_SubOperationCode =&gt; 0)  0 THEN
  RAISE ACCESS_DENIED_EXCEPTION;
END IF;
--------------------------------------------------------------------------------

--Corpo do codigo!!!!!!


--------------------------------------------------------------------------------
```
EXCEPTION
WHEN PARAMETER_NOT_FOUND THEN
spu_ac_DefaultExceptionHandler
(
p_ExceptionIdentifierCode => pkg_ac_SysAdminUtil.PARAMETER_NOT_FOUND,
p_AppCode => pkg_ac_SysAdminUtil.PARAMETER_NOT_FOUND,
p_ArgumentLog => p_ArgumentLog,
p_resultCode => p_resultCode,
p_sqlCode => p_sqlCode,
p_sqlErrMessage => p_sqlErrMessage
);
WHEN NO_ENTITY_FOUND THEN
spu_ac_DefaultExceptionHandler
(
p_ExceptionIdentifierCode => pkg_ac_SysAdminUtil.NO_ENTITY_FOUND,
p_AppCode => pkg_ac_SysAdminUtil.NO_ENTITY_FOUND,
p_ArgumentLog => p_ArgumentLog,
p_resultCode => p_resultCode,
p_sqlCode => p_sqlCode,
p_sqlErrMessage => p_sqlErrMessage
);
WHEN ACCESS_DENIED_EXCEPTION THEN
spu_ac_DefaultExceptionHandler
(
p_ExceptionIdentifierCode => pkg_ac_SysAdminUtil.ACCESS_DENIED_EXCEPTION,
p_AppCode => pkg_ac_SysAdminUtil.ACCESS_DENIED_EXCEPTION,
p_ArgumentLog => p_ArgumentLog,
p_resultCode => p_resultCode,
p_sqlCode => p_sqlCode,
p_sqlErrMessage => p_sqlErrMessage
);
WHEN ENTITYTYPE_NOT_ALLOWED THEN
spu_ac_DefaultExceptionHandler
(
p_ExceptionIdentifierCode => pkg_ac_SysAdminUtil.ENTITYTYPE_NOT_ALLOWED,
p_AppCode => pkg_ac_SysAdminUtil.ENTITYTYPE_NOT_ALLOWED,
p_ArgumentLog => p_ArgumentLog,
p_resultCode => p_resultCode,
p_sqlCode => p_sqlCode,
p_sqlErrMessage => p_sqlErrMessage
);
WHEN OTHERS THEN
spu_ac_DefaultExceptionHandler
(
p_ExceptionIdentifierCode => pkg_ac_SysAdminUtil.GENERIC_EXCEPTION_HANDLER,
p_AppCode => pkg_ac_SysAdminUtil.GENERIC_EXCEPTION_HANDLER,
p_ArgumentLog => p_ArgumentLog,
p_resultCode => p_resultCode,
p_sqlCode => p_sqlCode,
p_sqlErrMessage => p_sqlErrMessage
);

END spu_ac_ProcedureName;

Tem alguma sugestão ?
Autor

Posts

Visualizando 3 posts - 1 até 3 (de 3 do total)

Você deve fazer login para responder a este tópico.