Re: Sending feed back to sqlplus user

Brian,
the problem is that in SQL*Plus, you cannot get the results from a dbms_output.put_line, unless you have issued 'set serveroutput on...'. and you cannot do this unless you're connected. So your 'after logon' trigger will fire before you can 'set serverout....', hence no way to get the message immediately after the logon. In SQL*Plus, you can do the following:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> begin
  2 dbms_output.enable(10000);
  3 dbms_output.put_line('test before');   4 end;
  5 /

PL/SQL procedure successfully completed.

SQL> set serverout on size 10000
SQL> begin null; end;
  2 /
test before

PL/SQL procedure successfully completed.

so 'set server...' does not clear the buffer if already set before, so for sql*plus,
you can change your glogin.sql to insert set serveroutpout on size .....
begin
null;
end;
/

and it should do the trick. (modify your trigger to comment the RAISE statements)

rgds
On 8/25/06, Spears, Brian <BSpears_at_limitedbrands.com> wrote:
>
>
> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l