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.

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!