About Pre Process Functions and Post Process Functions
You can define PL/SQL functions for Publisher to run when a connection to a JDBC data source is created (preprocess function) or closed (postprocess function).
These two fields enable the administrator to set a user's context attributes before a connection is made to a database and then to dismiss the attributes after the connection is broken by the extraction engine.
The system variable :xdo_user_name can be used as a bind variable to pass the login username to the PL/SQL function calls. Setting the login user context in this way enables you to secure data at the data source level (rather than at the SQL query level).
For example, assuming the following sample function:
FUNCTION set_per_process_username (username_in IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
SETUSERCONTEXT(username_in);
return TRUE;
END set_per_process_username
To call this function every time a connection is made to the database, enter the following in the Pre Process Function field: set_per_process_username(:xdo_user_name)
Another sample usage might be to insert a row to the LOGTAB table every time a user connects or disconnects:
CREATE OR REPLACE FUNCTION BIP_LOG (user_name_in IN VARCHAR2, smode IN VARCHAR2)
RETURN BOOLEAN AS
BEGIN
INSERT INTO LOGTAB VALUES(user_name_in, sysdate,smode);
RETURN true;
END BIP_LOG;
In the Pre Process Function field enter: BIP_LOG(:xdo_user_name)
As a new connection is made to the database, it is logged in the LOGTAB table. The SMODE value specifies the activity as an entry or an exit. Calling this function as a Post Process Function as well returns results such as those shown in the table below.
