CODEWITHSUNDEEP
oracle-databaseplsql
Frnk
Frnk

Reputation: 137

OTHERS exception does not catch errors

When I run the procedures, it throws me the following errors (the first is correct):

BEGIN SP_REGI_OFIC('AD_PRO','Profesor',2000,'Hola'); END;
Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

'OTHERS' shouldn't you catch this error?

CREATE OR REPLACE PROCEDURE SP_REGI_OFIC
(codigo_jobs IN jobs.job_id%TYPE,
titulo_jobs IN jobs.job_title%TYPE,
min_salario  IN jobs.min_salary%TYPE,
max_salario IN jobs.max_salary%TYPE)
AS
   
BEGIN

INSERT INTO JOBS(job_id, job_title, min_salary, max_salary)
VALUES (codigo_jobs,titulo_jobs,min_salario,max_salario);
COMMIT;
Dbms_output.put_line('Empleado creado con éxito');

EXCEPTION  
    WHEN DUP_VAL_ON_INDEX THEN
        dbms_output.put_line('Ya existe un oficio con el código ingresado');
    WHEN OTHERS THEN 
        dbms_output.put_line('Error:'||TO_CHAR(SQLCODE));
        dbms_output.put_line('Ha ocurrido un error:' ||SUBSTR(SQLERRM, 1,110));  
END SP_REGI_OFIC;
   
EXECUTE SP_REGI_OFIC('AD_PRES','President',20080,40000);
EXECUTE SP_REGI_OFIC('AD_PRO','Profesor',2000,'Hello');

Upvotes: 1

Views: 614

Answers (2)

Andrew Sayer
Andrew Sayer

Reputation: 2336

Firstly you shouldn't ever want to use the when others then especially when you're only using it to put some limited detail into the dbms_output buffer. This buffer is not going to be read by your applications, you're not going to find out about errors. This will also mess with the transaction handling that you get when SQL knows a statement has errored - you will end up with logically corrupted data.

That said, to answer your question directly: The exception block will only catch exceptions thrown from within the scope of that procedure (between the begin and exception statements).

The obvious way to capture this runtime error would be to use an exception block in the scope that the execution error is happening.

BEGIN 
  SP_REGI_OFIC('AD_PRO','Profesor',2000,'Hola'); 
exception when others then
  dbms_output.put_line('Errors shmerrors');
END;
/

But it's seriously worth putting in the effort - should the caller know there was an error? Does it need to be logged somewhere? Does a transaction need cleaning up? If you don't know for certain then usually the best answer is to leave it to the default behaviour of Oracle throwing the error to the caller.

Upvotes: 3

Justin Cave
Justin Cave

Reputation: 231651

Exception handlers in PL/SQL can only catch runtime errors. The error you are getting is a compilation error. Presumably, jobs.max_salary%TYPE is a numeric data type but you are passing a string that can't be cast to a number instead.

It is virtually always possible to convert a compilation error to a runtime error. In this case, you could define all the input parameters as strings and try to convert them to the proper data types internally before using them in a query. Be aware, though, that it is almost always a mistake to convert compilation errors to runtime errors. Compilation errors are relatively easy to exhaustively detect and resolve before code gets deployed. Runtime errors generally depend on the input parameters and so are very hard to exhaustively detect. Runtime errors also tend to get detected much later in the development process when reworking code is more costly. You're generally much better off getting told by the compiler immediately that you're calling a method incorrectly than to get to the point where you've developed a bunch of code, deployed it to a test environment, and a QA person comes back with a runtime error that tells you that you passed a parameter of the wrong type to some API and you have to debug how that happened.

Upvotes: 2

Related Questions