Home » SQL & PL/SQL » SQL & PL/SQL » COM Object or Command Line Triggers (10g Express, Windows)
COM Object or Command Line Triggers [message #398205] Wed, 15 April 2009 12:23 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #398559 is a reply to message #398205] Thu, 16 April 2009 13:04 Go to previous messageGo to next message
Buckwheat469
Messages: 4
Registered: April 2009
Junior Member
Surprise surprise. The Express edition doesn't have the COM feature, so now I have to get the full edition for testing. I'll check back later.
Re: COM Object or Command Line Triggers [message #398576 is a reply to message #398205] Thu, 16 April 2009 15:18 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Just wondering: how exactly do you think your COM object will be executed on Oracle server (assuming that server may reside on Linux/Unix-HP/AIX/etc)?
Re: COM Object or Command Line Triggers [message #398801 is a reply to message #398576] Fri, 17 April 2009 11:59 Go to previous message
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.
Previous Topic: Mail csv file as an attachment In Oracle
Next Topic: Procedure error [merged]
Goto Forum:
  


Current Time: Sat Nov 02 12:18:42 CDT 2024