Oracle - How handling function error and return null instead

Question

How handling error in functon and return null instead any errors?

Simple example:

create or replace Function MY_FUNC
( 
     p_par IN number
)
RETURN varchar2
IS
BEGIN
     return (Select name from my_table where id = p_par);
END;
    
    
    
    
create or replace Function MY_FUNC
( 
    p_year IN number,
    p_month IN number,
    p_day IN number
)
RETURN varchar2
IS
  v_return varchar2(100);
BEGIN
     Select to_char(p_day)||''||substr(to_char(TO_DATE(p_year || '-' || p_month || '-' || p_day, 'YYYY-MM-DD'),'DY'),0,1) into v_return from dual;
     return v_return;
END;

Select MY_FUNC(2021,6,30) from dual; --OK
Select MY_FUNC(2021,6,31) from dual; --Need catch error

Return null if don't have record in table, or return null on ORA-01839: date not valid for month specified for anything.

Where does the date come from? p_par is a number, I assume id is a number, name is presumably a varchar2 — Justin Cave
– Justin Cave, Commented Jun 1, 2021 at 7:09
@JustinCave In above example function return varchar2, in other function I get error (date not valid for month) . — user_odoo
– user_odoo, Commented Jun 1, 2021 at 7:11

Justin Cave · Accepted Answer · 2021-06-01 07:35:25Z

My guess is that you want something like this where you catch the no_data_found exception

create or replace Function MY_FUNC
 ( 
   p_par IN my_table.id%type
 )
 RETURN my_table.name%type
IS
  l_name my_table.name%type;
BEGIN
  begin
    select name
      into l_name
      from my_table
     where id = p_par;
  exception
    when no_data_found
    then
      l_name := null;
  end;

  return l_name;
END;

I'm not sure where an "ORA-01839: date not valid for month" error could be coming from since there are no obvious dates in your code. You can, however, catch additional exceptions for other statements in your code in just the same way that I'm catching the no_data_found exception here.

create or replace Function MY_FUNC
( 
    p_year IN number,
    p_month IN number,
    p_day IN number
)
RETURN varchar2
IS
  v_return varchar2(100);
 
  invalid_date exception;
  pragma exception_init( invalid_date, -01839 );
BEGIN
  begin
   Select to_char(p_day) || '' ||
          substr(to_char(TO_DATE(p_year || '-' || p_month || '-' || p_day, 
                                 'YYYY-MM-DD'),
                         'DY'),
                  0,
                  1) 
      into v_return 
      from dual;
  exception
    when invalid_date
    then
      v_return := null;
  end; 

  return v_return;
END;

In this specific case, though, you can simplify your logic (assuming you're on 12.2 or later)

create or replace Function MY_FUNC
( 
    p_year IN number,
    p_month IN number,
    p_day IN number
)
RETURN varchar2
IS
  v_return varchar2(100);
  v_dt     date;  
BEGIN
  v_dt := to_date( p_year || '-' || p_month || '-' || p_day default null on conversion error,
                   'YYYY-MM-DD' );
  if( v_dt is null )
  then
    v_return := null;
  else
    v_return := to_char(p_day) || 
                  substr( to_char(v_dt, 'DY' ),
                          0,
                          1 );
  end if;

  return v_return;
END;

Hello, I'm update my question. Other example where I get error ORA-01839

Alex Fischer · Accepted Answer · 2021-06-01 15:39:45Z

0

You could use the EXCEPTION key word.

The NO_DATA_FOUND is the exception type. If you don'nt know the type you can use the OTHERS key word.

Edit (Thanks to Justin)
Of course you have to store it in a variable.
To return the result.

create or replace Function MY_FUNC
   ( 
     p_par IN number
   )
   RETURN varchar2
IS
    l_result varchar2;
BEGIN
     Select name into l_result from my_table where id = p_par;
     return l_result;
EXCEPTION
     WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;

edited Jun 1, 2021 at 15:39

answered Jun 1, 2021 at 7:11

Alex Fischer

2511 gold badge4 silver badges14 bronze badges

1 Comment

Justin Cave Over a year ago

You can't return the result of a select statement like that. You'd need to select the value into a local variable and return that.

Collectives™ on Stack Overflow

Oracle - How handling function error and return null instead

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related