Factorial function in oracle?

SELECT ROWNUM, EXP(SUM(LN(ROWNUM)) OVER (ORDER BY ROWNUM)) FACTORIAL FROM USER_OBJECTS WHERE ROWNUM < 10

more here

Don Burleson dives into factorials:

A Zillion Ways to Do Factorials

He mentions that the exp(sum(ln(rownum))) technique doesn't work in all cases.

Ah, I see he also mentions that his information is drawn from this book:

SQL Design Patterns by Vadim Tropashko

Probably the best way is just to create your own function. I found one at:

Club Oracle

create or replace function factorial_calc(value integer)
  return  integer
  as
  minus1 pls_integer;
  product pls_integer;
  begin
     dbms_output.enable(999999);
     minus1 := value - 1;
     if ( minus1 > 0 )
     then
        product := value*factorial_calc(minus1);
        return product;
     end if;
     return value;
end factorial_calc;

Caution! Not tested. They also show a way to do it in SQL.

Check out this query. I found the query over internet.

select count(*) FACTORIAL from ( select level b from (select level n from dual connect by level <= &x ) YourTable connect by nocycle n != prior n )where b = &x

Replace &x with factorial value.

But this query seems to be very slow. Only to show another way of doing this.