[Hint] Exceptions

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Tue, 22 Feb 2005 10:54 am

Some predefined exceptions, for the folks who like and everything in trinks!!!

Code: Select all
Exception                 ||  Error    ||  SQLCODE Value
-----------------------------------------------
ACCESS_INTO_NULL          || ORA-06530 || -6530
COLLECTION_IS_NULL        || ORA-06531 || -6531
CURSOR_ALREADY_OPEN       || ORA-06511 || -6511
DUP_VAL_ON_INDEX          || ORA-00001 || -1
INVALID_CURSOR            || ORA-01001 || -1001
INVALID_NUMBER            || ORA-01722 || -1722
LOGIN_DENIED              || ORA-01017 || -1017
NO_DATA_FOUND             || ORA-01403 || +100
NOT_LOGGED_ON             || ORA-01012 || -1012
PROGRAM_ERROR             || ORA-06501 || -6501
ROWTYPE_MISMATCH          || ORA-06504 || -6504
SELF_IS_NULL              || ORA-30625 || -30625
STORAGE_ERROR             || ORA-06500 || -6500
SUBSCRIPT_BEYOND_COUNT    || ORA-06533 || -6533
SUBSCRIPT_OUTSIDE_LIMIT   || ORA-06532 || -6532
SYS_INVALID_ROWID         || ORA-01410 || -1410
TIMEOUT_ON_RESOURCE       || ORA-00051 || -51
TOO_MANY_ROWS             || ORA-01422 || -1422
VALUE_ERROR               || ORA-06502 || -6502
ZERO_DIVIDE               || ORA-01476 || -1476
these exceptions are returned for any application that run command in a Oracle database a description of all errors NOW ... are found in the Manual oracle Errors8i which can be found at the address: http://www.oracle.com/pls/db92/db92.err ... ill=ORA-in ASP.NEt for example we could get this message in a block:
Code: Select all
try
{
   objConn.Open(); 
   OracleDataReader objReader = objCmd.ExecuteReader();
   while (objReader.Read())
   {
      Response.Write(objReader.GetString(0) + "<br>");
   }
   objReader.Close();
}
catch( OracleException ex )
{
   Response.Write( ex.Message );
}
finally
{
   objConn.Close();
}
falou!!!

[] 's
MuLtAnI
Location: Videira - SC

Poston Fri, 21 Aug 2009 10:16 am

Guys..I need to make the specific error handling.type:
Code: Select all
ORA-06502: PL/SQL: numeric or value error: character to number conversion error                                                               
ORA-06512: at "SIG.PRC_IMP_BASE_GENERICA", line 188                                                                                                 
ORA-00942: table or view does not exist 
and need to record in the log for the procedures ... as the structure below:
Code: Select all
exception
  when vException then
    vRet:= sig.fnc_add_log('PRC_CARGA_FTO_ACCOUNT_SRV_NEW'
                          ,10
                          ,'E'
                          ,vLog
                          ,null
                          ,'Tabelas auxiliares de carga não encontradas'
                          ,vValor);
    if vRet = -1 then
       dbms_output.put_line('ERRO NO INSERT DO LOG');
    end if;                           
--    raise;
  when others then
    vMsg:= sqlerrm;
    vRet:= sig.fnc_add_log('PRC_CARGA_FTO_ACCOUNT_SRV_NEW'
                          ,10
                          ,'E'
                          ,vLog
                          ,null
                          ,vMsg
                          ,vValor);
    if vRet = -1 then
       dbms_output.put_line('ERRO NO INSERT DO LOG');
    end if;                         
how can I do this-by the specific errors that I cited above and that no more than twenty, I wonder??
mariogus
Location: Porto Alegre - RS

Poston Fri, 21 Aug 2009 10:56 am

Beauty guy? Can you define your own exceptions using the compile directive PRAGMA EXCEPTION_INIT.
You put it in the section DECLARE your block, like this:
Code: Select all
DECLARE
  v_teste number;
  conversao_numero EXCEPTION;
  PRAGMA EXCEPTION_INIT(conversao_numero, -06502);
BEGIN
   v_teste := to_number('A'); -- converter A para número vai dar erro!
EXCEPTION
  WHEN conversao_numero THEN
    dbms_output.put_line('Erro de conversao: ' || SQLERRM);
  WHEN OTHERS THEN
    dbms_output.put_line('Erro: ' || SQLERRM);
    raise; -- propaga, por exemplo, se não for nenhum dos erros esperados...
END;
is just you create an exception for the ORA-code XXXXX that you want, and you can use as if it were the preset you have in the post above.
Can you set in package for reuse in other programs as well and is more elegant the code because you don't have to reset the exceptions in each procedure that you do, it's just reuse and change centered on the package:
Code: Select all
create or replace PACKAGE erros_pack AS
  erro_conversao_numero EXCEPTION;
  PRAGMA EXCEPTION_INIT(erro_conversao_numero, -06502);
  erro_select_into EXCEPTION;
  PRAGMA EXCEPTION_INIT(erro_select_into, -01422);
END erros_pack;
and then run the program like this:
Code: Select all
DECLARE
  v_teste number;
BEGIN
   --v_teste := to_number('A'); -- converter A para número vai dar erro!
   select 1 into v_teste
    from dual
   union select 2 from dual; -- select into retorna duas linhas, vai dar erro!
EXCEPTION
  WHEN erros_pack.erro_select_into THEN
    dbms_output.put_line('Erro ORA' || SQLCODE || ': Select Into retornou mais de uma linha');
  WHEN erros_pack.erro_conversao_numero THEN
    dbms_output.put_line('Erro de conversao: ' || SQLERRM);
  WHEN OTHERS THEN
    dbms_output.put_line('Erro: ' || SQLERRM);
    raise; -- propaga, por exemplo, se não for nenhum dos erros esperados...
END;
fsitja
Location: Gaúcho no Rio de Janeiro - RJ

"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist



Return to PL/SQL

Who is online

Users browsing this forum: No registered users and 8 guests