Exceptions handling in SQL?

Question

Is there any way to handle exceptions in sql(ORACLE 9i) not talking about PL/SQL only sql ?

Since I was trying to divide values of a column that contains both numbers and literals ,I need to fetch out only numbers from it ,as if it divisible by any number then its number else if contains literals it would not get divided it will generate error.

how to handle those errors? Please suggest!!

Answer 1

Excetions in oracle are handled with an exception when ... then clause. All you need is to find out the exception code.

begin
  --Your code here
exception when YOUR_EXCEPTION_CODE then
  --Exception handling here
end;

Answer 2

Write a simple IS_NUMERIC PL/SQL function to filter your data:

CREATE OR REPLACE FUNCTION IS_NUMERIC (p_input varchar2)
RETURN NUMBER
DETERMINISTIC
IS
  l_checkvar number;
BEGIN
  l_checkvar := coalesce(to_number(p_input), 'X');  -- maps null as non-numeric
  return 1;
EXCEPTION
  WHEN OTHERS THEN
    return 0;
END;
/

Then your query would be:

SELECT MY_NUMBER / MY_MIXED_VALUES
  FROM MY_TABLE
 WHERE IS_NUMERIC (MY_MIXED_VALUES) = 1