## Question and Answer

## You Asked

Hi,

Can I get Cumulative Normal Distribution and Normal Distribution Density using PL/SQL?

Thanks

Ankit.

Can I get Cumulative Normal Distribution and Normal Distribution Density using PL/SQL?

Thanks

Ankit.

## and Tom said...

http://docs.oracle.com/cd/B10501_01/server.920/a96540/functions29a.htm#82888

for example, check out the sql reference guide - all functions are documented, you might well find dozens more that you can use.

for example, check out the sql reference guide - all functions are documented, you might well find dozens more that you can use.

## Rating

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

# Comments

I think you have to write your own PL/SQL function.

Here are examples in C++ and VB

http://www.sitmo.com/doc/Calculating_the_Cumulative_Normal_Distribution

Here are examples in C++ and VB

http://www.sitmo.com/doc/Calculating_the_Cumulative_Normal_Distribution

If this is the function:

do NOT use plsql, just inline that right in the SQL statement itself. Use a view to hide it if you need.

double N(const double x) { const double b1 = 0.319381530; const double b2 = -0.356563782; const double b3 = 1.781477937; const double b4 = -1.821255978; const double b5 = 1.330274429; const double p = 0.2316419; const double c = 0.39894228; if(x >= 0.0) { double t = 1.0 / ( 1.0 + p * x ); return (1.0 - c * exp( -x * x / 2.0 ) * t * ( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 )); } else { double t = 1.0 / ( 1.0 - p * x ); return ( c * exp( -x * x / 2.0 ) * t * ( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 )); } }

do NOT use plsql, just inline that right in the SQL statement itself. Use a view to hide it if you need.

1. I'd love to know why the OP needed that. While Normal Distribution is very popular, most realistic statistical applications would use Student's T distribution. This is so common that Oracle contains a built in T Test function: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions157.htm#i1279931

2. Find a website with this table ( http://lilt.ilstu.edu/dasacke/eco138/ZTable.htm ), load the table into Oracle, and use it as much as you want.

3. Another interesting way to calculate the cumulative normal distribution in Oracle would be to use Oracle's dbms_random.normal to generate an approximation of the normal distribution and calculate the cumulative distribution on the result. Of course it is not very accurate:

select n,cume_dist ( ) over (order by n) cume_dist from

(select dbms_random.normal n from dual connect by level<=100);

2. Find a website with this table ( http://lilt.ilstu.edu/dasacke/eco138/ZTable.htm ), load the table into Oracle, and use it as much as you want.

3. Another interesting way to calculate the cumulative normal distribution in Oracle would be to use Oracle's dbms_random.normal to generate an approximation of the normal distribution and calculate the cumulative distribution on the result. Of course it is not very accurate:

select n,cume_dist ( ) over (order by n) cume_dist from

(select dbms_random.normal n from dual connect by level<=100);