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?

Answer 1

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;
/