Hello Team,

I need your help :Scenario is I need to create a function which will convert input string into date format but if string can not be able to convert into date format then a user defined exception should be raised in function which should be handled in main program so that further processing can be done.Below is the sample code .

Please let me know if in oracle a user defined unhanded exception can propagate to calling program and how to handle that in main program?



--Step1 :
create or replace FUNCTION to_dt( in_txt IN VARCHAR2 -- to be converted
)
RETURN DATE
AS
date_exp exception;
BEGIN
RETURN TO_DATE (in_txt, 'YYYYMMDD');
EXCEPTION -- If TO_DATE caused an error, then I want to rasie user defined Exception date_exp which I want to handle to main program
WHEN OTHERS
THEN
raise date_exp;
RETURN NULL;

commit;
END to_dt
;

--Step2
declare
date_exp exception;
v_date date;
begin
--select to_dt('20150101') into v_date from dual;
--dbms_output.put_line(v_date); --01-JAN-15--Happy Path

select to_dt('20153131') into v_date from dual;--I am expecting it should goto exception handler and print 'Invalid date' but I am getting below errors:
/*Error report -
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SCOTT.TO_DT", line 11
ORA-01843: not a valid month
ORA-06512: at line 8
06510. 00000 - "PL/SQL: unhandled user-defined exception"
*Cause: A user-defined exception was raised by PL/SQL code, but
not handled.
*Action: Fix the problem causing the exception or write an exception
handler for this condition. Or you may need to contact your
application administrator or DBA.*/

dbms_output.put_line(v_date);
exception
when date_exp then
dbms_output.put_line('Invalid date');
end;

Thanks,