exception when others then
  if sqlcode = <код ошибки> then
    -- your stuff here
  end if;
end;

declare
  str varchar2( 10 );
  e_value_error exception;
  pragma exception_init(e_value_error, - 6502 ); 
begin
  select '12345678910' into str from dual;
exception when e_value_error then
  dbms_output.put_line('Случилось непоправимое ...');
end;