Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Sudarshan.

Asked: February 02, 2017 - 2:29 pm UTC

Answered by: Connor McDonald - Last updated: November 02, 2020 - 2:43 am UTC

Category: Developer - Version: 11g

Viewed 1000+ times

You Asked

Can you please give me an example for recursive function in plsql

Which takes i_date as an input variable

using this i_date it should perform operations like it should fetch data from table into cursor

From this cursor I should insert data into a file .This file should be generated seperately for date that is passed in as input

Let us take that my input date is 01012016 the function should recursively perform until it reaches 31122016 (for every last day of a month i.e total 12 times)

and we said...

A recursive function is simply one that calls itself.

SQL> create or replace
  2  function factorial (
  3    n positive
  4  ) return positive
  5  is
  6  begin
  7    if n = 1 then
  8      return n;
  9    else
 10    return n * factorial(n-1);
 11    end if;
 12  end;
 13  /

Function created.

SQL> set serverout on
SQL> begin
  2    for i in 1..5 loop
  3      dbms_output.put_line(i || '! = ' || factorial(i));
  4    end loop;
  5  end;
  6  /
1! = 1
2! = 2
3! = 6
4! = 24
5! = 120

PL/SQL procedure successfully completed.

But your question does not necessarily suggest recursion, it sounds more to me like you just want to process something in a loop.

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.

and you rated our response

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Review


It seems not so accurate

October 24, 2020 - 2:51 am UTC

Reviewer: Shyamal from India

I have one doubt here..! I feel you have just called the function using for loop, But how can we say It is recursive ? The function should call its own function again (Function calls itself) is said to be recursive right ??? I am confused.

Connor McDonald


October 27, 2020 - 1:40 am UTC

To *test* the function, I called it in a loop.

But look *inside* the function code. It calls itself.

do not do this

October 30, 2020 - 7:03 pm UTC

Reviewer: marc from NY

Do not call the function on the very 1st line, a few years ago i accidently did not realize i placed the function in the 1st line and the DB server crashed. If i have any code before it, oracle caught the issue, like null; and oracle did not crash.

    factorial(n - 1);

    IF n = 1 THEN
        RETURN n;
        RETURN n * factorial(n - 1);
    END IF;

Connor McDonald


November 02, 2020 - 2:43 am UTC

Yes, that's got endless loop and a lot of memory consumption written all over it :-)

More to Explore


More on PL/SQL routine DBMS_OUTPUT here