Raise Application Error In Oracle Triggers
Contents |
Tom Kyte � Last updated: May 06, 2013 - 7:01 pm UTC Category: Developer � Version: 9.0.1 Whilst you are here, check out some content from the AskTom team: Table Functions, Part 1: Introduction and Exploration Latest Followup You
Oracle After Logon Trigger
Asked Hi Tom, I am trying to create an AFTER LOGON trigger to ora-00604 error occurred at recursive sql level 1 ora-20001 restrict logon form some terminals. Thats why I have created the following trigger. CREATE OR REPLACE TRIGGER afterlogon AFTER LOGON on
Oracle Logon Trigger For Specific User
DATABASE BEGIN IF sys_context('userenv','Terminal') = 'HALOGENTEACH' THEN RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to logon from this terminal'); END IF; END; / But it does not work. Actually Raise_application_trigger is not working in After raise application error syntax logon trigger. Is there any wrong in this code? What is the problem. Have a nice time, Tarek and we said... Oh it works -- just not with people that have DBA. ops$tkyte@ORA9I.WORLD> CREATE OR REPLACE TRIGGER afterlogon 2 AFTER LOGON on DATABASE 3 BEGIN 4 RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to logon from this terminal'); 5 END; 6 / Trigger created. ops$tkyte@ORA9I.WORLD> drop user a cascade; User ora-20000 dropped. ops$tkyte@ORA9I.WORLD> grant connect to a identified by a; Grant succeeded. ops$tkyte@ORA9I.WORLD> connect a/a ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-20001: You are not allowed to logon from this terminal ORA-06512: at line 2 Warning: You are no longer connected to ORACLE. ops$tkyte@ORA9I.WORLD> connect / Connected. ops$tkyte@ORA9I.WORLD> That is so that when you make a horrible mistake and have a logon trigger that always fails, you have someone who can drop it! It works for all be DBA accounts (and sysdba/sysoper). And, since you don't need to use DBA in the real world -- you should just setup your own "MY_DBA" role and use that -- the solution is easy. Only grant DBA to those users that really should have this super power. Updated December 1, 2005 - a couple of people have pointed out it just takes the administer Database Trigger privilege to be immune from a logon trigger failing. Plus, it seems that the trigger owner -- even if it's a regular "Create Session, Resource" guy -- never sees the exception in his own "after logon on schema" trigger (unless there's a magic parameter lurking that I have yet to stumble on). Bryn Llewellyn who "owns" plsql says in addition
Kyte � Last updated: August 28, 2013 - 7:31 pm UTC Category: Database � Version: 8.1.7 Whilst you are here, check out some content from the AskTom team: MERGE vs
Ora-06512
UPDATE/INSERT revisited Latest Followup You Asked Tom 1. I have a procedure, where in I drop partitions every day and add partitions. My question is about the exception section. Do I have to give specific exception conditions to handle the erros which are possible while dropping and adding partitions, or should I leave it by just giving the when others then exception, or https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3236035522926 should I leave it alone by not giving the exception section at all. what are the possible erros which can occur while trying to create a partition and trying to drop a partition. 2. The use of pragma exception init is very clear, but Why do you have to use a raise application error. Can I just use a regular exception, and https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1155066278457 raise it when I require to. I dont see the specific use of raise application error. what is the advantage of raise application error over a regular exception which you declare in the exception section and use it. (the only advantage seems to be that you can assign a number(20001 to 20999) to the error.) correct me if Iam wrong. and we said... 1) A when others is almost always a BUG unless it is immediately followed by a RAISE. The point of an exception block is to catch exceptional conditions you are EXPECTING, handle them gracefully and continue. For example, lets say you have a procedure that will either INSERT a new record or UPDATE an existing one depending on whether or not it exists. You could code: begin insert into t ( columns.... ) values ( values ..... ); exception when dup_val_on_index then -- record already exists, lets update it update t set .... = .... where ....; end; Now, if that was coded: begin insert into t ( columns.... ) values ( values ..... ); exception when dup_val_on_index then -- record already ex
FunctionsRegular Expressions FunctionsStatistical FunctionsLinear Regression FunctionsPL SQL Data TypesPL SQL StatementsPL SQL OperatorsPL SQL ProgrammingCursorCollectionsFunction Procedure PackagesTriggerSQL PLUS Session EnvironmentSystem Tables Data DictionarySystem PackagesObject OrientedXMLLarge ObjectsTransactionUser PrivilegeRaise application error in a trigger http://www.java2s.com/Tutorial/Oracle/0560__Trigger/Raiseapplicationerrorinatriggerincaseofinvalidnewvalue.htm in case of invalid new value : NEW OLD«Trigger«Oracle PL/SQL TutorialOracle PL/SQL TutorialTriggerNEW OLDSQL> SQL> create table t ( x int ); Table created. SQL> SQL> create trigger t_trigger before insert on t for each row 2 begin 3 for x in ( select * 4 from dual raise application 5 where :new.x > 10) 6 loop 7 raise_application_error( -20001, 'check failed' ); 8 end loop; 9 end; 10 / Trigger created. SQL> show errors No errors. SQL> SQL> insert into t select 1 from all_users; 15 rows created. SQL> SQL> set autotrace traceonly statistics SQL> SQL> insert into raise application error t select 1 from all_users; 15 rows created. Statistics ---------------------------------------------------------- 16 recursive calls 15 db block gets 73 consistent gets 0 physical reads 0 redo size 924 bytes sent via SQL*Net to client 947 bytes received via SQL*Net from client 6 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 15 rows processed SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped. SQL> SQL> SQL> 28.3.NEW OLD28.3.1.Old and new value28.3.2.Reference new value with :NEW in a before insert or update trigger28.3.3.Refernece an old value in :OLD after update trigger28.3.4.:old and :new Pseudo-records, Example 128.3.5.:old and :new Pseudo-records, Example 228.3.6.Raise application error in a trigger in case of invalid new value28.3.7.REFERENCING OLD AS old NEW AS new28.3.8.REFERENCING OLD AS old_values NEW AS new_values28.3.9.Output new and old value in a before update triggerjava2s.com |Email:info at java2s.com|© Demo Source and Support. All rights reserved.