TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!

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

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