Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sys_context ('USERENV', 'CURRENT_SQL') is giving null value..

654605Jul 18 2009 — edited Jul 20 2009
Hi,
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;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 17 2009
Added on Jul 18 2009
7 comments
4,306 views