Creating Oracle COM Automation Triggers to Create, Update, or Delete Active Directory Accounts in Realtime.
The Oracle Enterprise Database 11g includes a feature called COM Automation, which allows a table trigger to call an external application using its COM plugin. This is useful if you need to execute a command each time a record in the database is created, updated, or deleted. An example may be to insert a record into an Excel spreadsheet (not very useful), or synchronizing Active Directory with the database in real time (very useful). Each action type requires its own trigger, so in this tutorial I will cover the Insert trigger and how to install the COM automation plugin.
Installing Oracle 11.2
Oracle can be difficult to install and is rather picky about system resources. Make sure your system is strong enough to support this database engine.
- > 10G extra hard drive space. A 30G drive should work well.
- Windows XP or higher (XP worked the best for all of my tests)
- > 1G free virtual memory (1024M does well)
- Java JDK 1.5+ (click the JDK download button, install both JDK and JRE)
- VMs are OK
The Oracle 11g release 2 download includes 2 zip files. The first file is 1.6GB and the second one is 600MB, approximately. Download both files and unzip them to the same folder.
Execute setup.exe from the unzipped files. You will see a command prompt window which initializes the setup. Any error messages regarding resources will show up there, such as not enough virtual memory.
Step 1: Input your information for updates, or uncheck the checkbox and press Next.
Step 2: Select “Install database software only”. We’ll configure a database later. If you are comfortable with Oracle you can install the database now.
Step 3: Select “Single instance database installation”
Step 4: Press Next for default language of English (unless another language is desired).
Step 5: Press Next for Enterprise Edition.
Step 6: Choose your Oracle Base and Software installation locations (usually [driveletter]:\app\[username]\…)
Step 7: Prerequisite Checks. Click Next.
Step 8: Summary. Click Next.
Step 9: Install Product.
Creating a Database
Click Start > All Programs > Oracle – OraDb11g_home1 (or whatever name it gave you) > Configuration and Migration Tools > Database Configuration Assistant
Click Next for the Welcome screen.
Step 1: Click Next to Create a Database.
Step 2: Click Next for a General Purpose or Transaction Processing database.
Step 3: Choose a global database name and SID.
Step 4: Choose Configure Enterprise Manager checked. Press Next.
Step 5: Configure your desired passwords (choose a strong password). For this demonstration, we will use the same password for all accounts. The account we will log in with is “system”.
Step 6: Choose the storage mechanism. File System is the default, press Next.
Step 7: Choose the database file locations. The default is to use the template. Choose Next.
Step 8: Recovery Configuration. Press Next for the default options.
Step 9: Database Content. Press Next.
Step 10: Choose memory and various other advanced options. Defaults are fine, press Next.
Step 11: Security Settings. Press Next.
Step 12: Automatic Maintenance Tasks. Press Next.
Step 13: Database Storage. Press Next.
Step 14: Creation Options. Press Next.
Test the Database Connection with SQL Developer
Click Start > All Programs > Oracle… > Application Development > SQL Developer
SQL Developer may ask for the location of the Java executable. Install the Java SDK (linked in the requirements) and point SQL Developer to “C:\Program Files\Java\jdk1.6.0_23\bin\java.exe” (Java version must be 1.5+)
Right-click Connections and choose New Connection
Input the connection information.
Click the Test button to check the connection. You should see “Status : Success”
Creating the Table for the Trigger
In SQL Developer, right click Tables and choose New Table
Create your table based on your own design.
Installing the Oracle COM Automation Module
Open the command prompt (Start > Run > cmd)
Note: Don’t use quotes in these commands.
Navigate to your install directory’s com\java folder. In my case it’s “E:\app\demoa\product\11.2.0\dbhome_1\com\java”
You can find a readme.txt file in the com directory for more installation instructions.
In the command prompt, type “sqlplus“. Enter “system” as the user account and the password you configured in the database installation screens.
You are now using SQLPlus.
Type “@grant.sql SYSTEM” to grant COM execution to the system user account. To grant COM execution permissions for other accounts, type in their accounts in the same way (“@grant JOHNDOE”). Notice that the user account is capitalized.
Type “exit” to return to the command prompt.
Type “loadjava -force -resolve -user system oracom.jar“. This will install the COM Automation Java classes to Oracle.
Finalizing the command prompt installation steps:
This last piece is not documented in the readme.txt file, but is still necessary. It installs several functions to be used in COM Automation triggers.
Type “cd..” to navigate to the com directory.
Type “sqlplus” and log back into SQL*Plus.
Close the Command Prompt and return to SQL Developer.
Creating a COM Automation Trigger
The trigger below is specific to an application called UMRA, however the setup is similar to any other application. I’ve included COM setup, variable initialization, and method execution.
Within SQL Developer connect to your database.
Press File > New to open a new SQL input window.
Select SQL File in the New Gallery window. Press OK.
Specify a filename and directory location to save the file, then press OK.
Paste the code below, or use your own version for your COM Automation program. Modify the necessary table names and methods.
Note: you may need to paste the code in Wordpad to remove some formatting characters.
When the code is pasted, press the “Execute Statement” button.
create or replace TRIGGER UMRA_STUDENTS_INSERT AFTER INSERT ON UMRA_STUDENTS REFERENCING NEW AS newRow FOR EACH ROW DECLARE /* Variables to be used */ retval binary_integer := -1; conretval binary_integer := -1; execretval binary_integer := -1; umra binary_integer := -1; BEGIN /* Creates the COM object link */ retval := ORDCOM.CreateObject('UMRACom.UMRA',0,'',umra); /* If the connection fails, output an error. */ /* This example is disabled for you */ IF retval <> 0 THEN /* INSERT INTO UMRA_ERRORS (EXECTIME,ERRORMESSAGE) VALUES(CURRENT_TIMESTAMP,'Could not create the UMRA object. Error:'||retval); */ return; END IF; /* Use these for SETARG I2 - 2 byte integer I4 - 4 byte integer R4 - IEEE 4 byte real R8 - IEEE 8 byte real SCODE - error code CY - currency DISPATCH - dispatch pointer BSTR - String BOOL - boolean DATE - date */ /* Set some arguments for the Connect method */ ORDCOM.INITARG(); ORDCOM.SETARG('mycomputername','BSTR'); ORDCOM.SETARG('56814','I4'); /* Invoke the Connect method for UMRA */ /* Invoke(comvariable, methodname, argumentcount, returnvariable) */ retval := ORDCOM.Invoke(umra,'Connect',2,conretval); /* If the method execution fails, then log an error */ /* This method is disabled for you */ IF conretval <> 0 THEN /* INSERT INTO UMRA_ERRORS (EXECTIME,ERRORMESSAGE) VALUES(CURRENT_TIMESTAMP,'Could not connect to UMRA. Error:'||conretval); */ return; END IF; /* Everything's OK, continue */ /* You might like to log the information into another Oracle table? */ /* This is disabled for you */ /* INSERT INTO UMRA_TEST (SOMEVALUE) VALUES(:newRow.FirstName); */ /* Set variables to execute the COM object's function. */ /* Below, we will execute a UMRA script with 2 variables set. */ /* The variables for UMRA are invoked with a different method and must be */ /* set prior to executing the script. */ /*FirstName*/ ORDCOM.INITARG(); ORDCOM.SETARG('%FirstName%','BSTR'); ORDCOM.SETARG(:newRow.FirstName,'BSTR'); retval := ORDCOM.INVOKE(umra,'SetVariableText',2,execretval); /*LastName*/ ORDCOM.INITARG(); ORDCOM.SETARG('%LastName%','BSTR'); ORDCOM.SETARG(:newRow.LastName,'BSTR'); retval := ORDCOM.INVOKE(umra,'SetVariableText',2,execretval); /*Execute UMRA*/ ORDCOM.INITARG(); ORDCOM.SETARG('OracleTest','BSTR'); retval := ORDCOM.INVOKE(umra,'ExecuteProjectScript',1,execretval); retval := ORDCOM.DestroyObject(umra); END UMRA_STUDENTS_INSERT;
If the above trigger is good then you will see it listed in the Triggers folder in SQL Developer. If it cannot compile then it will have a small x on the icon.