Ora-00604 Error Occurred At Recursive Sql Level 1 Ora-20001
Contents |
Tom Kyte � Last updated: April 02, 2009 - 6:22 pm UTC Category: Database � Version: 9.2.0.6 Whilst you are here, check out some content from the AskTom team: Is a year a leap year? Latest Followup You Asked ora 00604 oracle sql developer Hi Tom, Could you tell me how to suppress "ORA-00604: error occurred at recursive SQL ora-604 error occurred at recursive sql level %s level 1" when I raise application error using "RAISE_APPLICATION_ERROR". I am using it in after logon trigger and restricting few users. I ora-00604 error occurred at recursive sql level 1 ora-01882 timezone region not found am displaying my error message. But I am getting the below errors also with my application error message. Is it possible to suppress the below error messages and want to display only the application error message.
Ora-00604 Error Occurred At Recursive Sql Level 3
******************************************************** 1)ORA-00604: error occurred at recursive SQL level 1 2)ORA-06512: at line 53 ******************************************************** Thanks, SC. and we said... you cannot, it works the way it works. Applications are responsible for displaying to end users what the application thinks is appropriate. This is something your application would do - eg: put up a message box that says "login failed, sorry" SQLPlus displays the entire call stack because the developers of sqlplus felt that was ora 00604 solution most appropriate. You can have your applications display whatever you feel is appropriate. Reviews Write a Review Thanks July 26, 2006 - 3:16 pm UTC Reviewer: Sreekanth from Herndon, VA USA Thanks for the reply. It was useful to convince other people that it's not possible. One more question July 26, 2006 - 3:28 pm UTC Reviewer: S Tom, Thank you for your response. I have an other question. Could you pls tell me if it is possible to use dbms_output and display a message to the user in after logon trigger. The user can be logged in from sqlplus or any other tool. Followup July 26, 2006 - 4:14 pm UTC but sqlplus is pretty much the only tool that would get it - and sqlplus wouldn't even get it because it doesn't realize it would be there to be gotten. that and the logon trigger doesn't really work in the "same space", the dbms-output stuff isn't going into a buffer we have access to. April 07, 2008 - 10:59 pm UTC Reviewer: Susan from New Zealand if you try instead of raise application error and try to kill the session it still gives the error. The error appears within a trace file, is there no way of suppressing the errors? Followup April 09, 2008 - 1:57
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have ora-00604 error occurred at recursive sql level 1 ora-00942 table or view does not exist Meta Discuss the workings and policies of this site About Us Learn
Ora-16000
more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us
Ora-00604 Ora-04031
Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:68817379083269 database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Database logon trigger up vote 5 down vote favorite 1 To block a particular user and IP http://dba.stackexchange.com/questions/10444/database-logon-trigger combination on an Oracle Database , I created the following trigger, and compiled without errors. Create or replace trigger you_may_not_login after logon on database begin if sys_context('USERENV','SESSION_USER')='xx' AND sys_context('USERENV','IP_ADDRESS')='10.0.30.219' then raise_application_error(-20001,'Denied! You are not allowed to logon the database'); end if; end; / When I login using the 'xx' user, the alert log shows the following error but access IS granted to the database, the trigger somehow fails from blocking the login of the user. Errors in file /oraarch/core/udump/wfsbi_ora_9338.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-20001: Sorry, you are not allowed here! ORA-06512: at line 9 Where am I going wrong?/ oracle trigger access-control share|improve this question asked Jan 10 '12 at 13:29 user996853 3113 4 Is another trigger coming into play here? Your trigger is defined to give the message Denied! You are not allowed to logon the database, but the error displayed is Sorry, you are not allowed here!. Also, the line number seems off. –Leigh Riffel Jan 10 '12 at 14:45 add a comme
First, we create a table to record those logins: CREATE TABLE HELPDESK.FAILED_LOGINS ( USERNAME VARCHAR2 (30 BYTE), OSUSER VARCHAR2 (30 BYTE), TERMINAL VARCHAR2 (30 BYTE), IP http://lefterhs.blogspot.com/2012/03/logon-trigger-to-forbid-unauthorized.html VARCHAR2 (64 BYTE), MODULE VARCHAR2 (48 BYTE), TIME DATE ); CREATE INDEX HELPDESK.FAILED_LOGINS_IDX ON HELPDESK.FAILED_LOGINS (TIME); The following trigger will check some database users and will allow only specific os users to login. CREATE OR REPLACE TRIGGER SYS.AUTH_LOGIN AFTER LOGON ON DATABASE DECLARE BEGIN IF DBMS_STANDARD.LOGIN_USER IN ('APPS', 'APPLSYS', 'AP', 'AR', 'AX', 'GL', 'INV', 'ONT', error occurred 'PO', 'HR', 'XXFSM') AND SYS_CONTEXT ('USERENV', 'OS_USER') NOT IN ('oracle', 'oracol') THEN INSERT INTO HELPDESK.FAILED_LOGINS (USERNAME, OSUSER, TERMINAL, IP, MODULE, TIME) VALUES (DBMS_STANDARD.LOGIN_USER, SYS_CONTEXT ('USERENV', 'OS_USER'), SYS_CONTEXT ('USERENV', 'TERMINAL'), SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), SYS_CONTEXT ('USERENV', 'MODULE'), SYSDATE); COMMIT; RAISE_APPLICATION_ERROR (-20001, 'Unauthorized login'); END IF; END; / We try to error occurred at login as APPS and we are not allowed: SQL> CONN APPS@COLDB_RP1 Enter password: **** ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-20001: Unauthorized login ORA-06512: at line 31 The attempt is recorded: SQL> SELECT * FROM HELPDESK.FAILED_LOGINS; USERNAMEOSUSERTERMINALIPMODULELTIME APPSetsekourasETSEKOUR10.100.25.60sqlplusw.exe29-MAR-12 If I add my os username in the trigger, I am allowed access: SQL> CONN APPS@COLDB_RP1 Enter password: **** Connected. Posted by Lefteris Tsekouras Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest Labels: Administration, Real Case No comments: Post a Comment Newer Post Older Post Home Subscribe to: Post Comments (Atom) My profile MBA International Athens University of Economics & Business Department of Electronic & Computer Engineering Technical University of Crete Labels Administration (41) Tuning (28) Real Case (27) Grid Control (16) Oracle Wait Interface (14) Segments (13) Tablespaces (11) RAC (8) e-BS (6) Disk I/O (4) Archive Logs (3) RMAN (3) Siebel (3) Alert.log (2) Auditing (2) Temporary (2) BI Publisher (1) Forms (1) Linux (1) SGA (1) Popular Posts Clone a database using RMAN DUPLICATE command In this scenario, we want to create a database copy [ AYXILIARY ] using RMAN DUPLICATE command in a different server with the same OS. In t... Archived log generation volume and count report V$AR