Sophis Risque: how to declare a user for SQL insertion of deals
Jun
14
Written by:
6/14/2013 11:58 AM
If you need to run SQL scripts in order to update some deals stored into the Oracle table of deals, HISTOMVTS, several things have to be done in order to permit the update done by the Oracle triggers at the same time. The steps to respect are the following ones:
- either deactivate the trigger..., not really possible on a production database when users are connected,
- or permit to the Oracle function getuserid to run without error, but returning null, by calling into the same Oracle session:
select seqlog.nextval from dual;
- or, knowing on which computer the script will run, inserting a record into the Sophis audit table which logs the connections, SECURITY_LOG, using for example this script (using MANAGER for the user ident uident):
01.declare
02.temp number := 0;
03.begin
04.select getnextloginid() into temp from dual;
05.insert into security_log (ident, uident, eventdate, workstation, category, event, parent, infos) values (temp, 1, SYSDATE,sys_context('USERENV', 'HOST') , 1, 101, temp, 0);
06.end;
07./
08.
09.select getuserid() from dual;
In this last case, the triggers setted on the HISTOMVTS table will use the getuserid declared on Sophis schema, and will insert the ident used for insertion into the field uident of SECURITY_LOG.