JDBC Triggers


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?


processtrigger.gif


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!


Comments:

Just to let you know... your website looks extremely peculiar in Firefox on a Mac

Posted by Jan Blakey on July 25, 2010 at 09:46 PM MDT #

Excellent job.

Posted by Rhode Island Excavation on September 17, 2010 at 06:09 PM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today