Skip to Main Content

PL/SQL to execute exe file with parameters from DB

PhelitDec 28 2011 — edited Dec 30 2011
Hi all,

I have a bit of a problem.

I am in need to write all Logon, Logoff and Logon fail data onto the Windows Application Log. To do this I already tried various solutions but with my configuration (Oracle EE 10.2.0.4 and Windows Server 2003 R2 X64) the only possible solution (apparently) is the following.

I create a Table with the info I need:

--------------------------------------

CREATE TABLE logonaudittable
(
event VARCHAR2(15),
sid NUMBER,
serial# NUMBER,
orario DATE,
username VARCHAR2(30),
osuserid VARCHAR2(30),
machinename VARCHAR2(64)
)
/
--------------------------------------

I create a trigger for Logon Data, one for Logoff and one for Logon Fail:

------------------------------------------------

LOGON trigger.

CREATE OR REPLACE TRIGGER logonauditing AFTER LOGON ON database
DECLARE
machinename VARCHAR2(64);
osuserid VARCHAR2(30);
v_sid NUMBER(10);
v_serial NUMBER(10);

CURSOR c1 IS
SELECT sid, serial#, osuser, machine
FROM v$session WHERE audsid = userenv('sessionid');
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename;

INSERT INTO logonaudittable VALUES ( 'LOGON', v_sid, v_serial, sysdate,
user, osuserid, machinename );

CLOSE c1;
END;
/

-----------------------------------------------

LOGOFF trigger

CREATE OR REPLACE TRIGGER logoffauditing
BEFORE LOGOFF ON database
DECLARE
machinename VARCHAR2(64);
osuserid VARCHAR2(30);
v_sid NUMBER(10);
v_serial NUMBER(10);

CURSOR c1 IS
SELECT sid, serial#, osuser, machine
FROM v$session WHERE audsid = userenv('sessionid');
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename;

INSERT INTO logonaudittable VALUES ( 'LOGOFF', v_sid, v_serial, sysdate,
user, osuserid, machinename );

CLOSE c1;
END;
/

-------------------------------------------------------

LOGON FAIL trigger

CREATE OR REPLACE TRIGGER logonfailauditing
AFTER SERVERERROR ON database
DECLARE
machinename VARCHAR2(64);
osuserid VARCHAR2(30);
v_sid NUMBER(10);
v_serial NUMBER(10);

CURSOR c1 IS
SELECT sid, serial#, osuser, machine
FROM v$session WHERE audsid = userenv('sessionid');
BEGIN
IF (IS_SERVERERROR(1017)) THEN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename;
INSERT INTO logonaudittable VALUES ( 'FAILLOGON', v_sid, v_serial, sysdate,
user, osuserid, machinename );
CLOSE c1;
END IF;
END;
/

-------------------------------------------

The I create a trigger that starts every time something is written on the table.
This trigger needs to start an EXE file that writes the latest data written on the table into the Windows Application Log.

So I need a trigger that executes my WRITELOG.EXE file adding the parameters. For example:

Execute WRITELOG.EXE event sid orario username

So my program will write a Log in Windows with this data:

Event: LOGON
Sid: 2938473
Orario: 12/12/2011 11:45:32
Username: Scott

And so on.

I already have the program that does this, I need to execute the program with the right data directly from trigger or from a procedure executed by my trigger.

Hope I've been clear and REALLY hope someone can help me!!!

Thank you all!
Comments
Post Details
Added on Dec 28 2011
8 comments
1,993 views