April 8th,2010 by Allan Bogh

This is a placeholder for later documentation. The trigger used is below. You will need to install the Oracle Automation extension from the Oracle Universal Installer. More information is to come...

create or replace TRIGGER UMRA_STUDENTS_INSERT
AFTER INSERT ON UMRA_STUDENTS
REFERENCING NEW AS newRow
FOR EACH ROW
DECLARE 
    retval binary_integer := -1;
    conretval binary_integer := -1;
    execretval binary_integer := -1;
    umra binary_integer := -1;
BEGIN
    retval := ORDCOM.CreateObject('UMRACom.UMRA',0,'',umra);

    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
    */
    ORDCOM.INITARG();
    ORDCOM.SETARG('xp','BSTR');
    ORDCOM.SETARG('56814','I4');

    retval := ORDCOM.Invoke(umra,'Connect',2,conretval);

    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*/
    INSERT INTO UMRA_TEST (SOMEVALUE) VALUES(:newRow.FirstName);
    
    /*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;