The problem:

An organization maintains 2 structure databases, one for 2D records and a second for 3D structures used in pharmacophore searches and modeling studies. The 2D database is updated directly as users create or acquire new compounds and correct existing records. The 3D database is updated in manually, at regular intervals. An administrator creates a dump of 2D records, runs an external program such as Corina (see http://www.molecular-networks.com/products/corina) and loads the resulting 3D structures into the 3D database.

The vision:

Each time a new record is added to the 2D database, an automated process is started that runs the 3D coordinate generation program and loads the results into the 3D database with no user intervention.

Additional requirements:

The 3D process must not delay the 2D registration.
The invocation of the 3D program must not introduce any security holes in the system.
Our 3D strategy includes the generation of up to 5 conformations (for ring compounds), so the tool that reads 3D files must read multiple records from a single file.

Strategy:

Since our databases were Oracle, we used an Oracle-specific approach.
An extproc listener invokes Corina with a defined entry point:
Parameters: input SD file; output SD file; processing options
Extproc set up as C language shared object library
A trigger (2DTABLE_TRIGGER) on the 2D structure table starts an asynchronous process using the DMBS_JOB package (http://tinyurl.com/3t8zarf ) when it detects a new or updated structure.
We looked at DBMS_SCHEDULER package, which is, in many ways, a successor to DBMS_JOB, but found that it includes an automatic commit, making it unusable in a table trigger.
We use DBMS_JOB to launch a top-level stored procedure ('RUNCORINAPROC') to manage the 3D generation process.
The top-level stored procedure calls a stored function ('CORINAFUNC') to write out a 2D SD file and run Corina, producing a 3D SD file.
A second stored procedure ('READFULLSDFILE') is then called to read the 3D SD file into the 3D structure table using UTL_FILE.
Errors encountered are recorded in a log table.

Result:

Each time a record is added to a 2D structure table, a set of corresponding 3D records appears in a 3D structure table.