Thanks for the question, Sudarshan.
Asked: February 01, 2017 - 1:15 pm UTC
Last updated: February 02, 2017 - 4:54 pm UTC
Version: 11g
Viewed 10K+ times! This question is 
You Asked
How to call a recursive function in pl\sql
function xyz(i_birthdate varchar2) return number as
y varchar2(25);
x number;
o_ret_date varchar2(25);
I have written code to generate files which takes i_birthdate as input(it includes some cursors also)
begin
loop
y:=DATE_NXT(i_birthdate,o_ret_date);
if(y!='31122016') then
x:=xyz(y);
end if;
EXIT WHEN y='31122016';
end loop;
end;
return 0;
exception
when others then
utl_file.put_line(f_dbg,'sqlcode:'||sqlcode);
utl_file.put_line(f_dbg,'sqlerrm:'||sqlerrm);
utl_file.put_line(f_dbg,dbms_utility.format_error_backtrace);
utl_file.put_line(f_dbg,'Complete rollback');
rollback;
return -2;
end xyz ;
function DATE_NXT(i_birthdate in varchar2,o_nxt_date out varchar2) return varchar2
as
V_DATE1 varchar2(25):=i_busidate;
begin
V_DATE1:=LAST_DAY(to_date(i_busidate,'ddmmyyyy')+1);
V_DATE1 := TO_CHAR(TO_DATE(V_DATE1,'DD-MON-YY', 'NLS_DATE_LANGUAGE = English'),'DDMMYYYY');
DBMS_OUTPUT.PUT_LINE(V_DATE1);
return V_DATE1;
EXCEPTION
when OTHERS then
DBMS_OUTPUT.PUT_LINE(SQLCODE);
end DATE_NXT;
Suppose If gave input as
30092016 then it should execute function for
30092016
31102016
30112016
and should exit from the function
i know error is occuring at this point x:=xyz(y);[it is calling for 30092016,31102016,30112016 and generating files but when it called for 30092016 it didnot reach the end of the function so the function is getting traced back an causing error)
Please help
and Chris said...
I'm baffled. Why are you writing a recursive function? Why not just:
create or replace function xyz (
dt date
) return date as
y date := dt;
begin
loop
y := last_day(y) + 1;
exit when y >= date'2016-12-31';
end loop;
return y;
end;
/
?
And why are you logging to a file? If you want to log exceptions, put them in a database table. Ideally using a utility like logger:
https://github.com/OraOpenSource/Logger The re-raise after.
In fact, why have the "when others" handlers at all? Remove them! In particular you're hiding all the exceptions for DATE_NXT, so if this fails you won't know!
Addenda: Here's another example of a recursion. When you posted a duplicate question so Chris and I keep looping around to each other's work, which simply chews up our time.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment