JDBC Triggers
- by Tim Dexter
Received a question from a customer last week, they were using the new rollup patch on top of 10.1.3.4.1. What are these boxes for?
Don't you know? Surely? Well, they are for ... that new functionality, you know it's in the user docs, that thingmabobby doodah.
OK, I dont know either, I can have a guess but let me check first. Serveral IM sessions, emails and a dig through the readme for the new
patch and I had my answer. Its not in the official documentation, yet. Leslie is on the case.
The two fields were designed to allow an Admin to set a users context attributes before a connection is made to a database and for
un-setting the attributes after the connection is broken by the extraction engine. We got a sample from the Enterprise Manager team on
how they will be using it with their VPD connections.
FUNCTION bip_to_em_user (user_name_in IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
SETEMUSERCONTEXT(user_name_in, MGMT_USER.OP_SET_IDENTIFIER);
return TRUE;
END bip_to_em_user;
And used in the jdbc data source definition like this (pre-process function):
sysman.mgmt_bip.bip_to_em_user(:xdo_user_name)
You, of course can call any function that is going to return a boolean value, another example might be.
FUNCTION set_per_process_username (username_in IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
SETUSERCONTEXT(username_in);
return TRUE;
END set_per_process_username
Just use your own function/package to set some user context. Very grateful for the mail from Leslie on the EM team's usage but I had
to try it out. Rather than set up a VPD, I opted for a simpler test. Can I log the comings and goings of users and their queries using
the same pre-process text box. Reaching back into the depths of my developer brain to remember some pl/sql, it was not that deep and I
came up with:
CREATE OR REPLACE FUNCTION BIPTEST (user_name_in IN VARCHAR2, smode IN VARCHAR2) RETURN BOOLEAN AS
BEGIN
INSERT INTO LOGTAB VALUES(user_name_in, sysdate,smode);
RETURN true;
END BIPTEST;
To call it in the pre-fetch trigger.
BIPTEST(:xdo_user_name)
Not going to set the pl/sql world alight I know, but you get the idea. As a new connection is made to the database its logged in the
LOGTAB table. The SMODE value just sets if its an entry or an exit. I used the pre- and post- boxes.
NAME
UPDATE_DATE
S_FLAG
oracle
14-MAY-10 09.51.34.000000000 AM
Start
oracle
14-MAY-10 10.23.57.000000000 AM
Finish
administrator
14-MAY-10 09.51.38.000000000 AM
Start
administrator
14-MAY-10 09.51.38.000000000 AM
Finish
oracle
14-MAY-10 09.51.42.000000000 AM
Start
oracle
14-MAY-10 09.51.42.000000000 AM
Finish
It works very well, I had some fun trying to find a nasty query for the extraction engine so that the timestamps from in to out actually
had a difference. That engine is fast!
The only derived value you can pass from BIP is :xdo_user_name. None of the other server values are available.
Connection pools are not currently supported but planned for a future release.
Now you know what those fields are for and look for some official documentation, rather than my ramblings, coming soon!
