Create ActiveX servers for PL/SQL packages

A general rule of thumb for tuning database applications is "80 percent application, 20 percent database."

This means that, on average, developers can tune 20 percent of performance problems by modifying database parameters, and solve 80 percent of these problems by tuning the application design and logic.

However, a developer's first impulse is to follow the manuals' examples and start coding database logic directly into their code. This practice results in a high-maintenance cost; one change to database logic or switching to a different database driver can require many hours of hunting through code to fix. It also becomes difficult to analyze performance from outside a source code debugger.

A better approach is to treat the database server as a real server—one that provides a set of business logic services completely removed from the code logic. This allows testing and maintenance of these services outside of the code before attaching code. An application should never know that there is such a thing as an EMP table, only that there is some kind of interface that you can access to find out "how many employees there are" and "give that particular employee a raise." In 32-bit Windows, the main way of providing a generic interface to most programming languages is through ActiveX or COM servers. These are DLLs or executables that provide an interface and services to many different programming environments. Coding ActiveX components is often a daunting task, and one that may require constant maintenance as database interfaces change.

For Visual Basic 6.0 programmers, the Oracle Objects for OLE Code Wizard for Visual Basic makes this task much easier. With the wizard, you can create an ActiveX component to wrap up database code for your developers--without writing much Visual Basic code.

If Visual Basic was installed before you installed an Oracle database, then you'll find the wizard in the Add-Ins menu option. If you installed Visual Basic later, you need to register the Code Wizard DLL's to enable this menu feature. Run these commands from the ORACLE_HOME/bin directory:

regsvr32.exe oo4ocodewiz.dll

regsvr32.exe odbtreeview.ocx

regsvr32.exe oo4oaddin.dll

To run the wizard, select Add-Ins from the main menu, then OO4O Code Wizard For Stored Procedures. The first time you run the wizard, it will ask you to log into a database. Once you're logged in, you can select Oracle DB, and then a database package from a list of packages. The wizard will create OO4O code around the functions and procedures in the package.

For example, say you want to create an ActiveX DLL server component for your database package. Listing A shows how to code the database package to handle your business services.

Next, you go into Visual Basic 6.0 and create an ActiveX DLL. By creating it as an ActiveX DLL, it will automatically create the ActiveX interface, build the DLL, and register it in the Windows operating system. (Anyone who wants to work with my database packages can simply select it from their favorite OLE Browser.)

With the project open, you invoke the wizard and log in, and the wizard would generate this Visual Basic code for your package automatically. View this code in Listing B.

You can remove the original blank Class1.cls file, set the new EMPSVC.cls to MultiUse=Yes, and set the project name to the type of library name you want, EmpSvcLib, and build the DLL. If you start a new Visual Basic project, you can see EmpSvcLib.EMPSVC in the list of exposed objects. If you reference it and declare a variable with it, you can call GET_COUNT and GIVE_RAISE from the variable. If you want to access it from C++, you can grab the IDL from the OLE Viewer that comes with Visual Studio, add it to your project, and let Visual Studio create Proxy code to access the database code.

Listing C shows the IDL for the above code from the OLE Viewer.

If you decide you want to change database logic to prevent unreasonable requests, such as requesting a negative salary or placing a limit on extremely large raises, simply modify the PL/SQL package--as long as the function and procedure signatures don't change. If they do, you can reinvoke the wizard to generate an updated version.

If you don't want to use OO4O, it's fairly straightforward to generate similar Visual Basic code for ADO, OLE DB, or other database architectures by querying function and procedure definitions from the USER_ARGUMENTS table.

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.