Without a function, you are going to have an enormous list of expressions etc within your SQL. It depends how far you want to go (and its unlikely you'll ever get to a 100% solution, as you would with a function).

You could start with:

case when incoming_date like '__/__/____'

which just checks for the slashes.

Then you head into the digits

case when incoming_date like '__/__/____'
and substr(incoming_date,1,2) between '01' and '12'
and substr(incoming_date,4,2) between '01' and '31'
and substr(incoming_date,7,4) between '1900' and '3000'

but you're still miles off a true valid check (because April 31st is not a valid date).

Then you could check the month/day mappings individually...but now you've got leap years to look out. Then its leap years based on 100 year and 400 year boundaries...

Sure you can do ALL of that in SQL...and you're update statement will be 200 lines long....

... or you can use a function.

"I was thinking something like using case statement in update and check for converting date using to_date and handle exception in the case block ."

There's a name for that ... it's called a function :-)

If you are on 12c, you can have that function defined *inside* the update statement itself

SQL> drop table t purge;

Table dropped.

SQL> create table t ( x varchar2(20), d date , err varchar2(20));

Table created.

SQL>
SQL> insert into t (x) values ('qwe');

1 row created.

SQL> insert into t (x) values ('01/01/2000');

1 row created.

SQL>
SQL> update  /*+ with_plsql */
  2  (
  3  with
  4    function date_checker(p_str varchar2) return date is
  5      dte date;
  6    begin
  7      dte := to_date(p_str,'dd/mm/yyyy');
  8      return dte;
  9    exception
 10      when others then return null;
 11    end;
 12    function date_error_msg(p_str varchar2) return varchar2 is
 13      dte date;
 14    begin
 15      dte := to_date(p_str,'dd/mm/yyyy');
 16      return 'OK';
 17    exception
 18      when others then return 'Fail';
 19    end;
 20  select x,d,err, date_checker(x) valid_date, date_error_msg(x) err_text
 21  from t
 22  )
 23  set d = valid_date, err = err_text;
 24  /

2 rows updated.

SQL>
SQL> select * from t;

X                    D          ERR
-------------------- ---------- --------------------
qwe                             Fail
01/01/2000           2000-01-01 OK

SQL>

but otherwise, you need that function defined separately in the database.