X

An Oracle blog about BI Publisher

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!


Join the discussion

Comments ( 2 )
  • Jan Blakey Monday, July 26, 2010
    Just to let you know... your website looks extremely peculiar in Firefox on a Mac
  • Rhode Island Excavation Saturday, September 18, 2010
    Excellent job.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.