CREATE FUNCTION FN_MYAPP_WS_LOGGER
return VARCHAR2
as PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

/* needs grant select on v$session (or any dictionary), but not via role */
INSERT INTO MYAPP_WS_LOGGER
SELECT * FROM V$SESSION where audsid = sys_context('USERENV','SESSIONID');
commit;

RETURN 'Y';

END FN_MYAPP_WS_LOGGER;

1 Comment

• researchadmin said 5 years ago Reply

  Extended logging, in view:
  
  and FN_MYAPP_WS_LOGGER2((SELECT VA.SQL_TEXT FROM V$SESSION V, V$SQLAREA VA WHERE V.SQL_ID = VA.SQL_ID AND audsid = sys_context('USERENV','SESSIONID'))) = 'Y'
  
  and function would be:
  
  CREATE FUNCTION FN_MYAPP_WS_LOGGER2(p_currsqlid varchar2)
  return VARCHAR2
  as PRAGMA AUTONOMOUS_TRANSACTION;
  
  BEGIN
  
  INSERT INTO MYAPP_WS_LOGGER2
  SELECT p_currsqlid, v.* FROM V$SESSION v where audsid = sys_context('USERENV','SESSIONID');
  commit;
  
  RETURN 'Y';
  
  END FN_MYAPP_WS_LOGGER2;
  
  

You must log on to comment.