COM Object or Command Line Triggers [message #398205] |
Wed, 15 April 2009 12:23 |
Buckwheat469
Messages: 4 Registered: April 2009
|
Junior Member |
|
|
I'm researching a method to integrate Oracle 10g with a COM object for a program that I run to make system updates realtime based on database updates, but I am having trouble finding the information I need. I've done this in T-SQL using one of Microsoft's extensions, but cannot find the same information in Oracle.
My situation is I have a program that contains a COM object, also a command-line interface. I would like an Oracle trigger to execute the COM Object or command line when a new record is inserted, updated, or deleted (3 triggers would be created respectively for each task).
I've researched how to create a trigger, so that part's not the problem, but how would I create an interface for the COM Object in this trigger?
I also found information on Oracle's COM Automation and have a sample script developed, but again I don't know how to integrate it in the trigger.
Non-working sample:
CREATE OR REPLACE TRIGGER TriggerTest_after_insert
AFTER INSERT
ON TriggerTest
FOR EACH ROW
declare
HRESULT BINARY_INTEGER := 0;
applicationToken BINARY_INTEGER := -1;
error_src VARCHAR2(255);
error_description VARCHAR2(255);
error_helpfile VARCHAR2(255);
error_helpID BINARY_INTEGER;
begin
HRESULT :=ORDCOM.CreateObject('UMRAcom.UMRA', 0, '', applicationToken);
IF (HRESULT!=0) THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
-- applicationToken is initialized.
-- 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
-- Invoke some methods
-- The following is equivalent to the pseudocode:
-- ResultHandle := ApplicationHandle.SomeMethodname
ORDCOM.InitArg();
ORDCOM.SetArg('localhost','BSTR');
ORDCOM.SetArg(56814,'I4');
HRESULT:=OrdCom.Invoke(applicationToken,'Connect',2,ResultHandle);
IF (HRESULT!=0) THEN
dbms_output.put_line(HRESULT);
IF (ResultHandle!=0) THEN
dbms_output.put_line(HRESULT);
-- Destroy the object when finished
HRESULT:=ORDCOM.DestroyObject(applicationToken);
IF (HRESULT!=0) THEN
dbms_output.put_line(HRESULT);
END
[Updated on: Wed, 15 April 2009 12:30] Report message to a moderator
|
|
|
Re: COM Object or Command Line Triggers [message #398452 is a reply to message #398205] |
Thu, 16 April 2009 05:34 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Non-working is not an oracle error.
Get your COM integration working from a procedure first - integrating it into a trigger will be the easy part of the job.
What error do you get when you run the code?
|
|
|
Re: COM Object or Command Line Triggers [message #398536 is a reply to message #398452] |
Thu, 16 April 2009 10:29 |
Buckwheat469
Messages: 4 Registered: April 2009
|
Junior Member |
|
|
Thanks for the reply. By the words "Non-working sample" I meant that here's the code that I think may work but doesn't yet, possibly due to other requirements or syntax that I don't know about. I included it to give as much information as possible first, because I've seen forums where the problem can't be solved due to lack of information.
To answer your question about what errors I get, well Oracle's web interface won't display an error because it can't find the "/apex/wwv_flow.show" webpage. This is a basic default setup in which I never modified file locations, so this must be an installation bug in the Oracle installer. It doesn't pertain to the issue though.
I will use your suggestion and try the code in a procedure. I will respond with more information when I've exhausted that route or had success.
Regards.
|
|
|
|
|
Re: COM Object or Command Line Triggers [message #398801 is a reply to message #398576] |
Fri, 17 April 2009 11:59 |
Buckwheat469
Messages: 4 Registered: April 2009
|
Junior Member |
|
|
In this situation it is more than likely a Windows server, but I also provided the note that the application has a command-line interface, which would also work for the job. In my own Linux system I could use Wine with this, but of course many customers won't do that in production.
For the purposes of this discussion it should be assumed that this is a Windows-based Oracle server with either the COM object installed as described, or the command-line program available in the PATH variable (to make it easier to write).
For an update: I attempted to install Oracle Standard on a VM and found that it easily ran out of memory during the installation (256M). This is very surprising since I can run some other database systems without this problem (albeit slow, but it doesn't freeze the system like Oracle did). Now I'm working on cleaning up the HD and giving it 512M of memory, but my god Oracle is a hassle to deal with. I needed Oracle Standard or Enterprise for the COM integration (ORDCOM component), since Express didn't have that component built in.
Regards.
|
|
|