QUICK FACTS
A contract consultant for Dupont Pharmaceuticals creates a solution using Oracle’s external procedure feature so that he is able to call a C function from the IMSL C Numerical Library (CNL) directly from PL/SQL. This allows for complicated analysis of large data sets to be done almost instantly calling the third party IMSL C Numerical Library directly from Oracle.

THE PROBLEM
When it comes to database design and statistical programming tools, there is one name at the front of Stephen J. Cottrell's Rolodex -- Visual Numerics, Inc. As principal of a Villanova, Pennsylvania-based technical consulting practice that bears his name, Cottrell has over 13 years of experience with pharmaceutical and chemical industry firms, including five years at the former Sterling Winthrop, Inc., and the last three with DuPont Pharmaceuticals Company, a wholly owned independent subsidiary of DuPont.

As a contract consultant to the Leads Discovery group at DuPont Pharmaceuticals, Cottrell's responsibilities include technical support for a large-volume Oracle database that houses biological results from high-throughput screening. In addition to managing the Leads Discovery research database, he assists DuPont Pharmaceuticals scientists by developing statistical applications for analyzing research data.

According to Cottrell, the sheer volume of data generated by DuPont Pharmaceuticals scientists and researchers presents some unique analysis challenges. "Because of the high volume of data associated with high-throughput screening, the only way desktop statistical applications work is to have all the background processing done directly in Oracle using PL/SQL, the Oracle programming language. Although executing the analysis programming in PL/SQL is efficient and the Oracle statistical toolset provides solid functionality, I needed more robust statistical analysis routines to meet the demanding needs of my clients."

THE SOLUTION
Cottrell's innovative solution was to utilize Oracle's little-known external procedure feature, which gives programmers the ability to call a C function directly from PL/SQL, essentially creating a new stored procedure within the Oracle programming environment. "Theoretically, this feature makes it possible to perform even the most complex numerical calculations entirely within the Oracle environment," Cottrell said. "Recognizing the potential of this feature, I requested that DuPont management purchase a Unix server license of the IMSL C Numerical Library (CNL) from Visual Numerics."

First released in 1990, CNL is a comprehensive set of more than 370 mathematical and statistical analysis functions that C/C++ programmers can embed directly into their numerical analysis applications. Many of CNL's functions are based upon corresponding routines from Visual Numerics' highly regarded IMSL Fortran Numerical Library, which was first released in 1970. Since that time, programmers in industries ranging from engineering test & analysis to space physics to business & finance and the earth sciences have used IMSL routines to build their applications.

The benefits of using CNL are many; foremost among them is an acceleration of application development and the corresponding savings in time and expense. Organizations that develop their own algorithms will invest as much money in building a single algorithm as it costs to buy and deploy the entire CNL.

Although no Oracle documentation was found that addressed the possibility of calling a third-party library from Oracle, Cottrell managed to do just that. The Oracle interface application, developed using Microsoft tools such as Access 97 and Visual Basic, accepts user input to identify the Oracle data of interest and to specify the relevant analysis parameters. This information is relayed to Oracle, which passes the information to the appropriate IMSL function via a PL/SQL stored procedure. The IMSL function passes the return arguments to the PL/SQL procedure, which then returns the results to the screen.

"Even for complicated analyses of large data sets, the entire process is completed almost instantly," Cottrell said. "The discovery of this method opens up an unlimited set of opportunities for developing leading-edge analysis tools."

RETURN ON INVESTMENT
Before applying the IMSL tools to his work at DuPont, Cottrell needed to export data from Oracle into a Unix operating system file, transfer the file to the Windows NT server, and then load the file into the appropriate statistical software. "Obviously, being able to call the IMSL functions directly into Oracle is a significant productivity enhancement," he said.

Cottrell, who first used the IMSL tools as a graduate student at the University of Delaware in 1985, decided to go with CNL because it was the only statistical library he found that is written in C, which is required by the Oracle external procedure feature; and it is available for Solaris, which is the platform for the Oracle server.

Like all good consultants, Cottrell sees the benefits of the IMSL/Oracle combination through the eyes of his client. "The greatest strength of the IMSL software is that, with the Oracle external procedure feature, it lets DuPont researchers perform advanced statistical analysis without their having to spend time locating and manipulating their data. With the IMSL/Oracle combination, important statistical information is only a button click away."