How to Calculate pi

You can use the following series to calculate pi:

4/1 - 4/3 + 4/5 - 4/7 + 4/9 - 4/11 + ...

I decided to try this out using PL/SQL. The example I created is shown below. It works OK but the series converges very slowly so you have to work out several million terms just to get pi accurate to 6 decimal places:

SQL> set timing on
SQL> declare
  2  pi          number  := 0;
  3  numerator   number  := 4;
  4  denominator number  := 1;
  5  dp          number  := 0;
  6  pi1         number;
  7  pi2         number;
  8  counter     number  := 0;
  9  finished    boolean := false;
 10  begin
 11  while not finished
 12  loop
 13   pi          := pi + (numerator / denominator);
 14   pi1         := round (pi,dp);
 15   denominator := denominator + 2;
 16   pi          := pi - (numerator / denominator);
 17   pi2         := round (pi,dp);
 18   denominator := denominator + 2;
 19   counter     := counter + 1;
 20   if pi1 = pi2 then
 21    dbms_output.put_line ('Counter = '||counter);
 22    if dp = 1 then
 23     dbms_output.put_line
 24     ('Accurate to 1 decimal place:');
 25    else
 26     dbms_output.put_line
 27     ('Accurate to '||dp||' decimal places:');
 28    end if;
 29    dbms_output.put_line ('Pi = '||pi1);
 30    dbms_output.put_line ('**********');
 31    dp := dp + 1;
 32    if dp > 6 then
 33     finished := true;
 34    end if;
 35   end if;
 36  end loop;
 37  end;
 38  /
Counter = 2
Accurate to 0 decimal places:
Pi = 3
**********
Counter = 60
Accurate to 1 decimal place:
Pi = 3.1
**********
Counter = 148
Accurate to 2 decimal places:
Pi = 3.14
**********
Counter = 5397
Accurate to 3 decimal places:
Pi = 3.142
**********
Counter = 11723
Accurate to 4 decimal places:
Pi = 3.1416
**********
Counter = 213092
Accurate to 5 decimal places:
Pi = 3.14159
**********
Counter = 3255425
Accurate to 6 decimal places:
Pi = 3.141593
**********

PL/SQL procedure successfully completed.

Elapsed: 00:00:34.53
SQL>

How to Provide User Input to PL/SQL

Here are some simple examples tested on Oracle 11.2. You can use a variable beginning with one ampersand:

 

SQL> !cat accept1.sql

begin

dbms_output.put_line('Hello &your_name');

end;

/

 

If you do this, you are asked to input the data again when you rerun the script:

 

SQL> set echo on

SQL> set serveroutput on

SQL> @accept1

SQL> begin

  2  dbms_output.put_line('Hello &your_name');

  3  end;

  4  /

Enter value for your_name: Andrew

old   2: dbms_output.put_line('Hello &your_name');

new   2: dbms_output.put_line('Hello Andrew');

Hello Andrew

 

PL/SQL procedure successfully completed.

 

SQL> @accept1

SQL> begin

  2  dbms_output.put_line('Hello &your_name');

  3  end;

  4  /

Enter value for your_name: Brian

old   2: dbms_output.put_line('Hello &your_name');

new   2: dbms_output.put_line('Hello Brian');

Hello Brian

 

PL/SQL procedure successfully completed.

 

SQL>

 

You can use a variable beginning with two ampersands:

 

SQL> !cat accept2.sql

begin

dbms_output.put_line('Hello &&first_name');

end;

/

 

If you do this, PL/SQL remembers the value of the variable between one execution and the next:

 

SQL> @accept2

SQL> begin

  2  dbms_output.put_line('Hello &&first_name');

  3  end;

  4  /

Enter value for first_name: Colin

old   2: dbms_output.put_line('Hello &&first_name');

new   2: dbms_output.put_line('Hello Colin');

Hello Colin

 

PL/SQL procedure successfully completed.

 

SQL> @accept2

SQL> begin

  2  dbms_output.put_line('Hello &&first_name');

  3  end;

  4  /

old   2: dbms_output.put_line('Hello &&first_name');

new   2: dbms_output.put_line('Hello Colin');

Hello Colin

 

PL/SQL procedure successfully completed.

 

SQL>

 

Later, if you want to provide a new value, you can UNDEFINE the variable between executions:

 

SQL> undefine first_name

SQL> @accept2

SQL> begin

  2  dbms_output.put_line('Hello &&first_name');

  3  end;

  4  /

Enter value for first_name: David

old   2: dbms_output.put_line('Hello &&first_name');

new   2: dbms_output.put_line('Hello David');

Hello David

 

PL/SQL procedure successfully completed.

 

SQL> undefine first_name

SQL> @accept2

SQL> begin

  2  dbms_output.put_line('Hello &&first_name');

  3  end;

  4  /

Enter value for first_name: Elvis

old   2: dbms_output.put_line('Hello &&first_name');

new   2: dbms_output.put_line('Hello Elvis');

Hello Elvis

 

PL/SQL procedure successfully completed.

 

SQL>

 

You can accept the variable beforehand in SQL*Plus as follows:

 

SQL> !cat accept3.sql

accept christian_name prompt "Who are you? "

begin

  dbms_output.put_line('Hello &christian_name');

end;

/ 

SQL> @accept3

SQL> accept christian_name prompt "Who are you? "

Who are you? Finbar

SQL> begin

  2    dbms_output.put_line('Hello &christian_name');

  3  end;

  4  /

old   2:   dbms_output.put_line('Hello &christian_name');

new   2:   dbms_output.put_line('Hello Finbar');

Hello Finbar

 

PL/SQL procedure successfully completed.

 

SQL>

 

If you don’t want Oracle to display the old and new values of the variable, you can stop this happening by running set verify off beforehand:

 

SQL> set verify off

SQL> @accept3

SQL> accept christian_name prompt "Who are you? "

Who are you? Gordon

SQL> begin

  2    dbms_output.put_line('Hello &christian_name');

  3  end;

  4  /

Hello Gordon

 

PL/SQL procedure successfully completed.

 

SQL> 

SERVEROUTPUT

Set serveroutput off/on can be used in SQL*Plus to control the output from the PL/SQL dbms_output.put_line statement. When you first login the default is for serveroutput to be off:

SQL> show serveroutput

serveroutput OFF

SQL>

If serveroutput is set to off, you will not get any output from dbms_output.put_line:

SQL> begin

  2  dbms_output.put_line('12345678901234567890');

  3  end;

  4  /

 

PL/SQL procedure successfully completed.

 

SQL>

Setting it to on changes this:

SQL> set serveroutput on

SQL> show serveroutput

serveroutput ON size 2000 format WORD_WRAPPED

SQL> begin

  2  dbms_output.put_line('12345678901234567890');

  3  end;

  4  /

12345678901234567890

 

PL/SQL procedure successfully completed.

 

SQL>

There is also a size associated with serveroutput. This specifies the volume of output allowed. In Oracle 9 this can have any value between 2000 and 1000000:

SQL> set serveroutput on size 1999

SP2-0547: size option 1999 out of range (2000 through 1000000)

SQL> set serveroutput on size 1000001

SP2-0547: size option 1000001 out of range (2000 through 1000000)

SQL>

I will set it to 2000 for the example below although, as you can see above, this appears to be the default. When you reach the limit set by the size parameter, Oracle displays an error and stops:

SQL> set serveroutput on size 2000

SQL> declare

  2  a number;

  3  begin

  4  a := 1;

  5  while a < 100

  6  loop

  7  dbms_output.put_line(a||' Andrew Reid, International DBA');

  8  a := a + 1;

  9  end loop;

10  end;

11  /

1 Andrew Reid, International DBA

2 Andrew Reid, International DBA

3 Andrew Reid, International DBA

4 Andrew Reid, International DBA

5 Andrew Reid, International DBA

6 Andrew Reid, International DBA

7 Andrew Reid, International DBA

8 Andrew Reid, International DBA

9 Andrew Reid, International DBA

10 Andrew Reid, International DBA

11 Andrew Reid, International DBA

12 Andrew Reid, International DBA

13 Andrew Reid, International DBA

14 Andrew Reid, International DBA

15 Andrew Reid, International DBA

16 Andrew Reid, International DBA

17 Andrew Reid, International DBA

18 Andrew Reid, International DBA

19 Andrew Reid, International DBA

20 Andrew Reid, International DBA

21 Andrew Reid, International DBA

22 Andrew Reid, International DBA

23 Andrew Reid, International DBA

24 Andrew Reid, International DBA

25 Andrew Reid, International DBA

26 Andrew Reid, International DBA

27 Andrew Reid, International DBA

28 Andrew Reid, International DBA

29 Andrew Reid, International DBA

30 Andrew Reid, International DBA

31 Andrew Reid, International DBA

32 Andrew Reid, International DBA

33 Andrew Reid, International DBA

34 Andrew Reid, International DBA

35 Andrew Reid, International DBA

36 Andrew Reid, International DBA

37 Andrew Reid, International DBA

38 Andrew Reid, International DBA

39 Andrew Reid, International DBA

40 Andrew Reid, International DBA

41 Andrew Reid, International DBA

42 Andrew Reid, International DBA

43 Andrew Reid, International DBA

44 Andrew Reid, International DBA

45 Andrew Reid, International DBA

46 Andrew Reid, International DBA

47 Andrew Reid, International DBA

48 Andrew Reid, International DBA

49 Andrew Reid, International DBA

50 Andrew Reid, International DBA

51 Andrew Reid, International DBA

52 Andrew Reid, International DBA

53 Andrew Reid, International DBA

54 Andrew Reid, International DBA

declare

*

ERROR at line 1:

ORA-20000: ORU-10027: buffer overflow, limit of 2000

bytes

ORA-06512: at "SYS.DBMS_OUTPUT", line 35

ORA-06512: at "SYS.DBMS_OUTPUT", line 198

ORA-06512: at "SYS.DBMS_OUTPUT", line 139

ORA-06512: at line 7

 

SQL>