sys_context ('USERENV', 'CURRENT_SQL') is giving null value..
654605Jul 18 2009 — edited Jul 20 2009Hi,
I have one system level trigger which captures all invalid sql statment with user and session detail.My problem is that this trigger is working fine but it is not capturing sql statement.That column is always null.
the oracle version is Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production and Fine-grained access control is TRUE..The trigger code is attached below.Could somebody help me in this..I am firing some simple wrong sql statement like selects from abc and i am expecting that this trigger will capture that.
CREATE OR REPLACE TRIGGER trg_server_error
AFTER SERVERERROR
ON schema
DECLARE
session_id VARCHAR2(30);
sql_statement VARCHAR2(4000);
ip_addr VARCHAR2(30);
hostname VARCHAR2(30);
auth_type VARCHAR2(30);
os_user VARCHAR2(30);
BEGIN
SELECT sys_context ('USERENV', 'SESSIONID')
INTO session_id
FROM dual;
SELECT sys_context ('USERENV', 'CURRENT_SQL')
INTO sql_statement
FROM dual;
SELECT sys_context ('USERENV', 'IP_ADDRESS')
INTO ip_addr
FROM dual;
SELECT sys_context ('USERENV', 'OS_USER')
INTO os_user
FROM dual;
SELECT sys_context ('USERENV', 'HOST')
INTO hostname
FROM dual;
SELECT sys_context ('USERENV', 'AUTHENTICATION_TYPE')
INTO auth_type
FROM dual;
INSERT INTO error_info VALUES
*(user, os_user,sysdate, session_id,ip_addr,hostname,auth_type, sql_statement);*
END;