This sure sounds like sms did not grant execute directly to the trigger
owner? Or it's gone? But I can't believe it is that easy.

-----Original Message-----
From: YIF
[mailto:oracledba-ezmlmshield-x78212674.[Email address protected]
Sent: Wednesday, September 07, 2005 7:23 AM
To: LazyDBA Discussion
Subject: TRIGGER IS NOT WORKING




Hi all
i have trigger that it should sms me when someone logs in as system or
sys
user, but it's giving me an error everytime i try to compile it.

here is my trigger

CREATE OR REPLACE TRIGGER LOGON_NOTIFIER
LOGON ON DATABASE
Declare
P_Mobile VARCHAR2(15) := '9656656210'; -- YIF
V_OS VARCHAR2(1000);
P_Machine Varchar2(20) := 'DB_SUPPORT'; -- YIF
P_Message varchar2(70);
Begin

IF user IN ( 'SYSTEM' , 'SYS' ) OR
SYS_CONTEXT('USERENV', 'ISDBA') = 'TRUE' THEN

P_Message := 'Logon :' || chr(10) ||
user ||'@'|| sys_context('USERENV', 'DB_NAME')
|| chr(10) ||
sys_context('USERENV', 'OS_USER') ||
chr(10) ||
sys_context('USERENV', 'IP_ADDRESS') ||
chr(10) ||
sys_context('USERENV', 'HOST') ;

insert into db_history
( ID , VALUE , INSERT_DATE , SMS )
values
( 'LOGON_NOTIFIER TRIGGER' , P_Message, SYSDATE , NULL
);

V_OS := DO_OS( 'NET SEND ' || P_Machine ||' '||
P_Message );
end if;
--
iF user IN ( 'SYSTEM' , 'SYS' ) OR
SYS_CONTEXT('USERENV', 'ISDBA') = 'TRUE' THEN
SMS.SMSTABLE_SEND( P_Mobile , P_Message, 'AR' );
END IF;

Exception
when Others then
Null;
End;
/


THIS IS THE ERROR
PLS-00201: identifier 'SMS.SMSTABLE_SEND' MUST BE DECLARED

SMS.SMSTABLE_SEND IS THE SCHEMA WE USE FOR SMS AND THE SMSTABLE_SEND IS
THE TABLE WE INSERT PHONE NUMBERS INTO.
REGARDS



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html



Oracle LazyDBA home page