Sending feed back to sqlplus user
- From: "Spears, Brian" <BSpears@xxxxxxxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Aug 2006 08:59:58 -0400
Hi,
I have been trying to send message to logged on user using the database
logon trigger. I can send them a message when signing them
off by raising exception in the trigger but I need to send in any ugly
format a message back to the sqlplus user from inside this trigger.
(Not all code include for sizing)
CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
BEGIN
DBMS_OUTPUT.ENABLE(100000);
SELECT program, machine, username INTO v_prog, v_mach, v_user
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND rownum = 1 -- Parallel processes will have the same AUDSID's
AND upper(username) in ('WORKBRAIN') ;
IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%'OR -- Toad
UPPER(v_prog) LIKE '%SQLPLUS%' -- SQLPLUS
AND upper(v_mach) LIKE 'LIMITED\%'
THEN
DBMS_OUTPUT.PUT_LINE('yup output from trigger');
RAISE e_badlogin;
END IF;
EXCEPTION
WHEN e_badlogin THEN
-- DBMS_OUTPUT.PUT_LINE('yup output from trigger');
RAISE_APPLICATION_ERROR(-20000, 'This access is in violation of LTS
compliance, audit, and IT Security Policies.!'||' User: '||v
_user|| ' Program: '||v_prog);
END;
============
Problem is, I can't from the trigger send back any message without killing
the session. Dbms_output doesnot seem to work from inside this trigger
(Should it?) .
Can someone point me in the right direction here? There must be some way to
send message to SQLplus user a message without killing the session.
Brian
Other related posts:
