# Factorial function in oracle?

 0 Do we have any function which calulates Factorial value. Factorial definition: n* n-1 n-2 ...... * 1 For example: 5! = 5*4*3*2*1 I didn't find any function for this. I am planning to create a PL/SQL function for this. Any ideas are welcome. more ▼ asked Dec 17, 2009 at 09:08 PM in Default OraLearner 1.3k ● 82 ● 88 ● 92 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

3 answers: sort voted first
 0 SELECT ROWNUM, EXP(SUM(LN(ROWNUM)) OVER (ORDER BY ROWNUM)) FACTORIAL FROM USER_OBJECTS WHERE ROWNUM < 10 more here more ▼ answered Dec 17, 2009 at 09:35 PM Tariq Rahiman 665 ● 2 ● 4 ● 7 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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. more ▼ answered Dec 17, 2009 at 10:48 PM KillerDBA 1.5k ● 9 ● 11 ● 14 This question reads like a college level course homework assignment :) Dec 17, 2009 at 11:37 PM HillbillyToad You know, I think you're right. It's probably because of the factorials. College programming (I go back before "Computer Science" degrees) homework always involved some unrealistic problem that involved some sort of math that we've never even seen since college... like factorials! Dec 18, 2009 at 02:08 AM KillerDBA Not exactly Kind of college work. But I needed to do this for my project requriement. I know that we can do this by definig PL/SQL procedure. But I thought of finding some better ways to do this. So I found few good ones here. Thanks to everyone. Dec 18, 2009 at 05:10 PM OraLearner add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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. more ▼ answered Dec 18, 2009 at 08:30 PM BI DWH BALA 606 ● 54 ● 62 ● 66 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
Your answer

### Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x442
x60

asked: Dec 17, 2009 at 09:08 PM

Seen: 5508 times

Last Updated: Dec 17, 2009 at 09:08 PM

### Related Questions

Copyright 2018 Redgate Software. Privacy Policy