How to find what data caused Oracle function failure?

Question

sorry if this not the right place.

I am doing a SQL SELECT statement, invoking a function. It's a large data dump - about 10,000 records. I am calling a function to preform some calculations, but its failing. One ore more of those records has bad data that is causing the function to crash.

Is there any way to see exactly what data caused the crash readily Or should I create some code to run the function by hand for each of 10,000 records? I could create code that generates the input data fairly straightforwardly, then run the function like this SELECT MY_FUNCT(1,1,1) FROM DUAL; but I am wondering if there is a better way.

For reference I am running the SQL query like this.

SELECT 
MY_FUNCT(A.FOO, A.BAR) 
FROM TABLE A
WHERE ....;

Answer 1

As others have said, you just need to handle the error and not raise it all the way. A neat way of doing this would be to create a wrapper function for your function that sometimes fails, you can declare this function within your select query using a with pl/sql clause:

Let's say this is your function that sometimes fails

create or replace function my_funct (inputnumber number)
return varchar2
is
  sString varchar2(200);
begin
  if inputnumber = 42 then 
    raise_application_error(-20001,'UH OH!'); 
  end if;
  sString := 'Input: '||inputnumber;
  
  return sString;
end my_funct;
/

We can define a function that takes the same inputs, and just calls this function, then we just need to add some error handling (obviously never just rely on dbms_output to capture errors, this is just to make it obvious):

function my_funct_handle (inputnumber number)
return varchar2
is
begin 
  return my_funct (inputnumber => inputnumber); 
exception when others then  
  dbms_output.put_line(sqlerrm||' at '||inputnumber);
return 'ERROR'; 
end;

And we can then just stick that in our query using with function

with 
  function my_funct_handler (inputnumber number)
  return varchar2
  is
  begin 
    return my_funct (inputnumber => inputnumber); 
  exception when others then  
    dbms_output.put_line(sqlerrm||' at '||inputnumber);
    return 'ERROR'; 
  end;
select my_funct_handler (id), string_col
from   as_table;
/

I get both the dbms_output text to describe the error and the ID but I could also filter on the results of that function to only show me the erroring rows:

with 
  function my_funct_handle (inputnumber number)
  return varchar2
  is
  begin 
    return my_funct (inputnumber => inputnumber); 
  exception when others then  
    dbms_output.put_line(sqlerrm||' at '||inputnumber);
    return 'ERROR'; 
  end;
select my_funct_handle (id), string_col
from   as_table
where  my_funct_handle (id) = 'ERROR';
/
MY_FUNCT_HANDLE(ID)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
STRI
----
ERROR
blah


ORA-20001: UH OH! at 42
ORA-20001: UH OH! at 42

(I get two errors shown in the dbms_output output as the function can be called multiple times - once as part of the select list and once as part of the where clause.)

Answer 2

one better approach is to create a error handler Package/Procedure which will write it into a table and call it from the function, this way all the errors will be captured in a Oracle table.

--- untested -- You can create other columns to capture the function name, date, and other details in the error table.

PROCEDURE SP_ERROR_INS_ERR_COMMON (n_ERR_CODE NUMBER,  c_ERR_SOURCE VARCHAR2,  n_ERR_LINE NUMBER,     c_ERR_DESC VARCHAR2, C_USER_COMMENT VARCHAR2 ) IS
    
    n_Log_Id NUMBER; 
    

    PRAGMA AUTONOMOUS_TRANSACTION;

    BEGIN
    
    n_Log_Id := 0;

    INSERT INTO ERROR_LOG_COMMON
        (ERROR_CODE, ERROR_SOURCE, ERROR_LINE, ERROR_DESCRIPTION, USER_COMMENT)
    VALUES
        (n_ERR_CODE,  c_ERR_SOURCE,  n_ERR_LINE, c_ERR_DESC, C_USER_COMMENT
        );

    COMMIT;

    raise_application_error( -20001,SUBSTR(SQLERRM, 1, 200));

 END SP_ERROR_INS_ERR_COMMON;

In your function you can call the error

  EXCEPTION  
 WHEN OTHERS
     THEN
     vn_errcode :=  SQLCODE;
        vc_errmsg := SUBSTR (SQLERRM, 1, 4000);
        sp_error_ins_err_common(vn_ErrCode,
        'SP_RP_DIM_COMPARE', NULL, vc_ErrMsg, substr('batch_id ' || g_BATCH_ID || 
         l_str_err_msg,1,4000) );
        RAISE;

Answer 3

One option is to handle the exception properly, e.g.

create or replace function my_funct(par_foo in number, par_bar in number)
  return number 
is
  retval number;
begin
  select sal
    into retval
    from emp
    where ename = par_foo
      and deptno = par_bar;

  return par_foo/par_bar;
exception                             --> this
  when no_data_found then
    return null;
end;

---

If you want, you can even log those errors. How? Make the function autonomous transaction (so that it could write into the log table; you'll have to commit that insert). Store all relevant information (including SQLERRM). Once your code finishes, check what's written in the log file and then decide what to do.

---

Or, you could even continue current task by enclosing that select into its own begin-exception-end block within a loop, e.g.

begin
  for cur_r in (select ... from ...) loop
    begin
      -- your current SELECT 
      SELECT 
        MY_FUNCT(A.FOO, A.BAR) 
      FROM TABLE A
      WHERE ....;
    exception
      when others then
        dbms_output.put_line(cur_r.some_value ||': '|| sqlerrm);
    end;
  end loop;
end;