oracle pl/sql exception handling for input parameter (bad argument i.e. non numeric value for number)

Question

I have stored procedure with input parameter of type number.

CREATE OR REPLACE PROCEDURE my_procedure (p_x number) 
AS

I included exception handling code as below, but that do not handle following:

execute my_procedure ('sads')

EXCEPTION
    WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);

is there any way to change exception for bad arguments?

The exception is in the calling code, not your procedure, so your procedure never sees the invalid value and can't do anything to handle it. — William Robertson, Dec 18 '21 at 11:48

score 0 · Answer 1 · answered Dec 18 '21 at 12:58

The error will happen when the procedure is invoked, not inside of the procedure so you can't capture the error as an exception within that procedure itself. A possible solution would be use an additional procedure that validates the arguments. In the example code below the procedure so_arg_test.my_procedure accepts a VARCHAR2 but it will only invoke my_procedure_hidden if the argument actually is a number. For anything other it will raise a value error.

CREATE OR REPLACE PACKAGE so_arg_test AS
  PROCEDURE my_procedure (
    p_x IN VARCHAR2
  );

END so_arg_test;
/

CREATE OR REPLACE
PACKAGE BODY SO_ARG_TEST AS

  procedure my_procedure_hidden  (p_x IN NUMBER) AS
  BEGIN
    -- your code
    dbms_output.put_line('Inside my_procedure_hidden');
  END my_procedure_hidden;
  
  procedure my_procedure (p_x IN VARCHAR2) 
  AS  
  BEGIN
    IF VALIDATE_CONVERSION(p_x AS NUMBER) = 0 THEN
      RAISE VALUE_ERROR;
    END IF;
    my_procedure_hidden (p_x => my_procedure.p_x);
  END my_procedure;  

END SO_ARG_TEST;
/

oracle pl/sql exception handling for input parameter (bad argument i.e. non numeric value for number)

1 Answers1