Tuesday, August 01, 2006

Prime number generator for PL/SQL

Well, it's been a little while since I blogged about PL/SQL!

I have been heads-down, coding and documenting, documenting and coding -- working furiously to meet deadlines that would allow my unit testing tool, code-named Qute (the Quest Unit Test Engine), to go production in October. Wow...what a brain drain.

One of the features offered by Qute is the ability to select test data values from pre-defined groups. And I thought that, what the heck, I would provide a set of prime numbers between 1 and 100, as one of those groups.

To do that, I built a little prime number generator package. It is definitely not anything fancy and I include it below. The generator tries to divide into a given number every integer between 2 and one less than that number. In none of the divide evenly, I've got a prime.

Hey, what can I say? I've got a Bachelors Degree with High Distinction in Mathematics, Summa Cum Laude, from the University of Rochester, 1980. This kind of brute force algorithm comes to me naturally.

Anyway, I also added the ability to generate text that contains the prime number. Here is an example of using the program to generate code that I needed to insert into my Qute install script:

BEGIN
prime_numbers.show_primes (
prefix_in => 'intval_insert ( ''$prime - a prime number'', '''
, suffix_in => ''', is_expression_in => qu_config.c_no);'
);
END;


The code (sorry about the formatting; sometimes I hate HTML - but I have no time to fix this. Use the Toad or SQL Navigator formatter to make it look nice).

Hope you find it useful!

CREATE OR REPLACE PACKAGE prime_numbers
IS
TYPE primes_aat IS TABLE OF VARCHAR2 ( 32767 )
INDEX BY PLS_INTEGER;

FUNCTION is_prime (
number_in IN INTEGER
)
RETURN BOOLEAN;

PROCEDURE show_primes (
start_in IN INTEGER DEFAULT 1
, end_in IN INTEGER DEFAULT 100
, prefix_in IN VARCHAR2 DEFAULT NULL
, suffix_in IN VARCHAR2 DEFAULT NULL
, placedholder_in IN VARCHAR2 DEFAULT '$prime'
);

FUNCTION primes (
start_in IN INTEGER DEFAULT 1
, end_in IN INTEGER DEFAULT 100
, prefix_in IN VARCHAR2 DEFAULT NULL
, suffix_in IN VARCHAR2 DEFAULT NULL
, placedholder_in IN VARCHAR2 DEFAULT '$prime'
)
RETURN primes_aat;
END prime_numbers;
/

CREATE OR REPLACE PACKAGE BODY prime_numbers
IS
FUNCTION is_prime (
number_in IN INTEGER
)
RETURN BOOLEAN
IS
c_limit INTEGER := ABS ( number_in );
retval BOOLEAN DEFAULT TRUE;
divisor INTEGER := 2;
BEGIN
WHILE ( divisor < c_limit AND retval )
LOOP
retval := MOD ( number_in, divisor ) <> 0;
divisor := divisor + 1;
END LOOP;

RETURN retval;
END is_prime;

FUNCTION primes (
start_in IN INTEGER DEFAULT 1
, end_in IN INTEGER DEFAULT 100
, prefix_in IN VARCHAR2 DEFAULT NULL
, suffix_in IN VARCHAR2 DEFAULT NULL
, placedholder_in IN VARCHAR2 DEFAULT '$prime'
)
RETURN primes_aat
IS
retval primes_aat;
BEGIN
FOR indx IN start_in .. end_in
LOOP
IF is_prime ( indx )
THEN
retval ( retval.COUNT + 1 ) :=
REPLACE ( prefix_in, placedholder_in, TO_CHAR ( indx ))
|| TO_CHAR ( indx )
|| REPLACE ( suffix_in, placedholder_in, TO_CHAR ( indx ));
END IF;
END LOOP;

RETURN retval;
END primes;

PROCEDURE show_primes (
start_in IN INTEGER DEFAULT 1
, end_in IN INTEGER DEFAULT 100
, prefix_in IN VARCHAR2 DEFAULT NULL
, suffix_in IN VARCHAR2 DEFAULT NULL
, placedholder_in IN VARCHAR2 DEFAULT '$prime'
)
IS
l_primes primes_aat
:= primes ( start_in, end_in, prefix_in, suffix_in, placedholder_in );
BEGIN
FOR indx IN 1 .. l_primes.COUNT
LOOP
DBMS_OUTPUT.put_line ( l_primes ( indx ));
END LOOP;
END show_primes;
END prime_numbers;
/

4 comments:

Mark Williams said...

Hi Steven,

Funny you should post about this. For the Sep/Oct 2006 ODP.NET column in Oracle Magazine I cover debugging PL/SQL using the Oracle Developer Tools for Visual Studio .NET. My choice of PL/SQL code to debug? A function that determines if a number is likely prime.

Is your "is_prime" function as posted here complete?

Thanks,

Mark

Mark Williams said...

Hi Steven,

I see that the "is_prime" function code has been "unmangled".

Thanks!

- Mark

DaPi said...

Hi Steven,

Probably not worth doing for numbers up to 100, but should you go bigger you should limit your loop to TRUNC(SQRT(ABS(number_in))).

Cheers - DaPi

robvl said...

1 is not a prime ;-)