Raise Application Error In Trigger Oracle
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: Looking at Edition-Based Redefinition, Part 3 oracle after logon trigger Latest Followup You Asked Hi Tom, I am trying to create an AFTER
Ora-00604 Error Occurred At Recursive Sql Level 1 Ora-20001
LOGON trigger to restrict logon form some terminals. Thats why I have created the following trigger. CREATE OR REPLACE TRIGGER oracle logon trigger for specific user afterlogon AFTER LOGON on 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 raise application error syntax is not working in After 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
Ora-20000
/ Trigger created. ops$tkyte@ORA9I.WORLD> drop user a cascade; User 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
Sveta Smirnova-Oracle on Sep 18, 2009 Recently I got this question twice. Although SIGNAL was implemented in version 6.0 (which is partially mysql-trunk now) this version is ora-06512 not stable yet, so users still need to use workaround.Here it is. Create 2 procedures as following: DROP PROCEDURE IF EXISTS raise_application_error;DROP PROCEDURE IF EXISTS get_last_custom_error;DROP TABLE IF EXISTS RAISE_ERROR;DELIMITER $$CREATE PROCEDURE raise_application_error(IN CODE INTEGER, IN MESSAGE VARCHAR(255)) SQL SECURITY INVOKER DETERMINISTICBEGIN CREATE TEMPORARY TABLE IF NOT EXISTS RAISE_ERROR(F1 INT NOT NULL); SELECT CODE, MESSAGE INTO @error_code, @error_message; INSERT INTO RAISE_ERROR VALUES(NULL);END;$$CREATE PROCEDURE get_last_custom_error() https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3236035522926 SQL SECURITY INVOKER DETERMINISTICBEGIN SELECT @error_code, @error_message;END;$$DELIMITER ; You can use them as:CALL raise_application_error(1234, 'Custom message');CALL get_last_custom_error(); Example: table which stores only odd numbers.DROP TABLE IF EXISTS ex1;DROP TRIGGER IF EXISTS ex1_bi;DROP TRIGGER IF EXISTS ex1_bu;CREATE TABLE ex1(only_odd_numbers INT UNSIGNED);DELIMITER $$CREATE TRIGGER ex1_bi BEFORE INSERT ON ex1 FOR EACH ROWBEGIN IF NEW.only_odd_numbers%2 != 0 THEN CALL raise_application_error(3001, 'Not odd number!'); END IF;END$$CREATE TRIGGER ex1_bu https://blogs.oracle.com/svetasmirnova/entry/how_to_raise_error_in BEFORE UPDATE ON ex1 FOR EACH ROWBEGIN IF NEW.only_odd_numbers%2 != 0 THEN CALL raise_application_error(3001, 'Not odd number!'); END IF;END$$DELIMITER ; Usage: mysql> INSERT INTO ex1 VALUES(2);Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO ex1 VALUES(3);ERROR 1048 (23000): Column 'F1' cannot be nullmysql> CALL get_last_custom_error();+-------------+-----------------+| @error_code | @error_message |+-------------+-----------------+| 3001 | Not odd number! | +-------------+-----------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> SELECT \* FROM ex1;+------------------+| only_odd_numbers |+------------------+| 2 | +------------------+1 row in set (0.00 sec)
Category: MySQL Tags: mysql Permanent link to this entry « Why there is not bug... | Main | Presentation from... » Comments: Got the answer thanks. Posted by Outsource software development on September 23, 2009 at 03:54 AM MSD # This solution may not be as pretty, but I think it is easier to use. 1. Create a function to throw an error: CREATE FUNCTION imxfn_RaiseError(_msg VARCHAR(256)) RETURNS INT DETERMINISTIC BEGIN DECLARE a TINYINT; -- Force an error to be raised by assigning a string to an -- integer variable. The string will appear on the client. SET a = _msg; RETURN 0; END 2. Call function witFunctionsRegular 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. http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/user-of-raise-application-error-in-trigger-943297 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.
Technology and Trends Enterprise Architecture and EAI ERP Hardware IT Management and Strategy Java Knowledge Management Linux Networking Oracle PeopleSoft Project and Portfolio Management SAP SCM Security Siebel Storage UNIX Visual Basic Web Design and Development Windows < Back CHOOSE A DISCUSSION GROUP Research Directory TOPICS Database Hardware Networking SAP Security Web Design MEMBERS Paul_Pedant DACREE MarkDeVries Inside-CRM Inside-ERP I_am_the_dragon VoIP_News MacProTX maxwellarnold Michael Meyers-Jouan TerryCurran Chris_Day Andrew.S.Baker Ramnath.Awate JoeTorre Craig Borysowich DukeGanote Dennis Stevenson mircea_luca Richard Locutus iudithm Clinton Jones bracke Iqbalyk AbhaiTripathi Adrian_Grigoriu Nikki Klein blrvenkat numbersguyPA COMPANIES Sophos Adaptive Planning Imation Dell Software View All Topics View All Members View All Companies Toolbox for IT Topics Oracle Groups Ask a New Question Oracle Development A forum where peers share technical expertise, solve problems, and discuss issues related to Oracle Development. Home | Invite Peers | More Oracle Groups Your account is ready. You're now being signed in. Solve problems - It's Free Create your account in seconds E-mail address is taken If this is your account,sign in here Email address Username Between 5 and 30 characters. No spaces please The Profile Name is already in use Password Notify me of new activity in this group: Real Time Daily Never Keep me informed of the latest: White Papers Newsletter Jobs By clicking "Join Now", you agree to Toolbox for Technology terms of use, and have read and understand our privacy policy. User of raise application error in trigger beena_kumari asked Mar 3, 2006 | Replies (10) Hi All, Can anyone pl. tell me why I am getting such error message and how to take of this error.Pl. note that I want to user raise_application_error here. 1 create or replace trigger emp_t1 after insert or update of sal on emp_bak for each row 2 declare 3 x number; 4 begin 5 if inserting then 6 if :new.sal > 50000 then 7 raise_application_error(20002,'more than 50K while inserting'); 8 end if; 9 elsif updating then 10 dbms_output.put_line('updating.......'); 11 if:new.sal > :old.sal + 50000 then 12 raise_application_error(20003,'more than 50K+old value while updating'); 13 end if; 14 end if; 15* end; SQL> / Trigger created. SQL> update emp_bak set sal=70000 where empno=7934; update emp_bak set sal=70000 where empno=7934 * ERROR at line 1: ORA-21000: error number argument to raise_application_error of 20003 is out of range ORA-06512: at "SCOTT.EMP_T1", line 11 ORA-04088: er