Profile Raise Application Error
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: On DBMS_ROWID, Parsing, and Sizing Latest raise application error oracle example Followup You Asked Hi Tom, I am trying to create an AFTER LOGON
Difference Between Raise And Raise_application_error In Oracle
trigger to restrict logon form some terminals. Thats why I have created the following trigger. CREATE OR REPLACE TRIGGER afterlogon raise_application_error(-20001 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 oracle raise 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
Pragma Exception_init And Raise Application Error
/ 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
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 raise_application_error parameters Visual Basic Web Design and Development Windows < Back CHOOSE A DISCUSSION GROUP raise application error in oracle triggers Research Directory TOPICS Database Hardware Networking SAP Security Web Design MEMBERS Paul_Pedant DACREE MarkDeVries Inside-CRM Inside-ERP MacProTX VoIP_News I_am_the_dragon maxwellarnold
Raise_application_error Number Range
Michael Meyers-Jouan TerryCurran Chris_Day Andrew.S.Baker Ramnath.Awate JoeTorre Craig Borysowich Locutus Dennis Stevenson DukeGanote Richard iudithm mircea_luca Clinton Jones bracke Nikki Klein AbhaiTripathi Adrian_Grigoriu Iqbalyk blrvenkat numbersguyPA COMPANIES Wave Direct Avaya Pivotal CRM SendGrid https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3236035522926 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 http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/user-of-raise-application-error-in-trigger-943297 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
is. It is an abomination of hard-coding and poor practice. If you didn't know that, I'm sorry I was the one who told you. I've written and http://www.oraclenerd.com/2010/10/never-use-raiseapplicationerror-again.html used extensively an ultra-simple framework to eliminate RAISE_APPLICATION_ERROR from my code forever.Here's an example (assume 11gR2) of something we all know we can do:begin dbms_output.put_line(1/0); http://blog.whitehorses.nl/2010/04/15/handling-ora-errors-in-apex/ end;This will throw an unhandled ORA-01476 exception. We could write some meaningful handling of that with this:begin dbms_output.put_line(1/0);exception when zero_divide then dbms_output.put_line('zero divide exception caught');end;This raise application coding is elegant because Oracle has conveniently predefined an exception named ZERO_DIVIDE and a corresponding pragma for us. Unfortunately, Oracle has only 22 predefined exceptions. What happens when I do this:declare d date;begin d := to_date('2010-09-30', 'YYYY-MM-DD'); -- works dbms_output.put_line(d); d := to_date('12345-09-30', 'YYYY-MM-DD'); -- fails dbms_output.put_line(d);end;This will throw raise application error an unhandled ORA-01861 exception. My option to handle this is less than meaningful because this is not a predefined exception:declare d date;begin d := to_date('2010-09-30', 'YYYY-MM-DD'); -- works dbms_output.put_line(d); d := to_date('12345-09-30', 'YYYY-MM-DD'); -- fails dbms_output.put_line(d);exception when others then case sqlcode when -1861 then dbms_output.put_line('literal does not match exception caught'); else raise; end case;end;This leads me to the inevitable desire to create my own named exception and pragma, so I could have code that looks like this instead:declare d date;begin d := to_date('2010-09-30', 'YYYY-MM-DD'); -- works dbms_output.put_line(d); d := to_date('12345-09-30', 'YYYY-MM-DD'); -- fails dbms_output.put_line(d);exception when error.ora_literal_string_mismatch then dbms_output.put_line('literal does not match exception caught');end;Understanding this, creating my own ERROR package with a friendly named exception and pragma for ORA-01861 leads me to the pattern of how to handle my own application exceptions, namely defining an exception and pragma. But how does this get RAISE_APPLICATION_ERROR out of my li
comments Tweet One of the first things I noticed when making an APEX application, there was a lack of error handling. In particular, the error handling raised from an database procedure. For clarity, here a part of a screen as you will see after a unique constraint violation, the ORA-0001 error. I think the most horrible part is that this error is displayed on a new page! Errors like these must be displayed on the same page where the error occured, on top of the page, in the #NOTIFICATION_MESSAGE# area, the same way as page processing validation is displayed. Here a preview of the final result. This of course can be achieved by adding an extra page process validation. Page process validation is to be conducted before the page procesess. Major disadvantage of this solution is that all business rules which are implemented in the database, must be defined as a page process validation on each APEX page. Moreover, if we adding a business rule on the database, we must not forget to add an extra process validation on a ore more APEX pages. Make sure that all errors are handled by one procedure (a good habit anyway). something like this: exception when others then omp_log.error(sqlcode,sqlerrm); end ins; In this procedure, we test of this error has occurred in an APEX environment. If so, we DON'T raise this as a application error. Moreover, we remember through a package variable that an error has occurred which had not been raised. if v('APP_SESSION') is null then pv_set_unraised_error_exists := false; raise_application_error(sqlcode,sqlerrm); else -- context is APEX pv_set_unraised_error_exists := true; -- save the omp_nos.ins(sqlerrm); end if; We also save the error occurred in a temporary table. We must do that with an autonomous transaction so that the errors are actually recorded in the temporary table. We can not keep these errors in a package pl/sql table because the web, and therfore APEX is stateless and everytime we (re-)load an APEX page, we have got an new oracle session. create global temporary table omp_notifications ( session_id number not null enable ,text varchar2(4000 byte) not null enable ) on c