Ora-00604 Error Occurred At Recursive Sql Level 1 Ora-06502
Contents |
E-mail to friend ORA-00604/ORA-06502 on create package, validates after recompile [message #421442] Tue, 08 September 2009 05:50 IvanGeentjens Messages: 1Registered: September 2009 Junior Member We have a problem on ora-604: error occurred at recursive sql level %s our production environment. When installing a new release, all the packages that were
Ora-00604 Error Occurred At Recursive Sql Level 1 Ora-01882 Timezone Region Not Found
created become INVALID, with the following error : ERROR at line 1: ORA-00604: error occurred at recursive SQL ora-00604 error occurred at recursive sql level 3 level 1 ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 11 However, when recompiling the package, it becomes VALID . The execution of the code in the packages became up ora 00604 solution to 10 times slower after this install. We run Oracle 9.2.0.7.0 on an AIX 5.2 Any ideas ? Report message to a moderator Re: ORA-00604/ORA-06502 on create package, validates after recompile [message #421446 is a reply to message #421442] Tue, 08 September 2009 06:06 JRowbottom Messages: 5933Registered: June 2006 Location: Sunny North Yorkshire, ho... Senior Member Ok, there's almost certainly
Ora-00604 Error Occurred At Recursive Sql Level 1 Ora-00942 Table Or View Does Not Exist
some confusion here. 1) How are you compiling the new packages? 2) After compilation, when you look in USER_ERRORS, is there an error for every single new package, or just for one package? 3) Do just the new packages run slowly, or do all packages run slowly? Is it just the first time that you call a package that it is slow, or is every call to every object in the package slower? 4) Are there any trace files generated? 5) Do you have any home grown auditing code that might be firing when you change code? The problem with the error you're getting is that it is a runtime error, not a compilation error - implying that either the error isn't happening when you say it is, or that you've got some code you're not telling us about that runs as part of your install or on compilation of objects on the DB. Report message to a moderator Re: ORA-00604/ORA-06502 on create package, validates after recompile [message #428968 is a reply to message #421442] Sat, 31 October 2009 04:02 jsphatt Messages:
01, 2013 - 7:13 pm UTC Category: Database � Version: 9.0.1.0.0 Whilst you are here, check out some content from the AskTom team: Where is my tracefile Latest Followup You Asked Hi, I have ora 00604 oracle sql developer the following problem: SQL> select * from t; select * from t * ERROR ora-16000 at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04092: cannot COMMIT in a trigger ORA-06512: at line 12
Ora-01003: No Statement Parsed
ORA-00942: table or view does not exist And table `t' does not exist. But this happens no matter what user I am, no matter what I do but only if there are errors (assuming that http://www.orafaq.com/forum/t/149820/ it would normally give only the last error). I was looking for the trigger that might do commit but there is no single one in any schemas in our database. What does this ORA-00604 mean? Does it have something to do with recursive SQL queries? Is this some kind of internal problem? How can I get rid of it? Thanx in advance, Piotr and we said... someone created an https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:4018285967344 "after servererror" trigger and is committing in it. consider: ops$tkyte@ORA9I.WORLD> drop user a cascade; User dropped. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> grant create session to a identified by a; Grant succeeded. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> @connect a/a ops$tkyte@ORA9I.WORLD> set termout off a@ORA9I.WORLD> REM GET afiedt.buf NOLIST a@ORA9I.WORLD> set termout on a@ORA9I.WORLD> select * from t; select * from t * ERROR at line 1: ORA-00942: table or view does not exist a@ORA9I.WORLD> a@ORA9I.WORLD> @connect / a@ORA9I.WORLD> set termout off ops$tkyte@ORA9I.WORLD> REM GET afiedt.buf NOLIST ops$tkyte@ORA9I.WORLD> set termout on ops$tkyte@ORA9I.WORLD> drop table log; Table dropped. ops$tkyte@ORA9I.WORLD> create table log( msg varchar2(255) ); Table created. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> create or replace trigger error_trigger 2 after servererror on database 3 begin 4 insert into log values ( 'hi' ); 5 commit; 6 end; 7 / Trigger created. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> @connect a/a ops$tkyte@ORA9I.WORLD> set termout off a@ORA9I.WORLD> REM GET afiedt.buf NOLIST a@ORA9I.WORLD> set termout on a@ORA9I.WORLD> select * from t; select * from t * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04092: cannot COMMIT in a trigger ORA-06512: at line 3 ORA-00942: table or view does not exist a@ORA9I.WORLD> a@ORA9I.WORLD> @connect / a@ORA9I.WORLD> set termout off ops$tkyte@ORA9I.WORLD> REM GET afiedt.buf NOLIST ops$tkyte@ORA9I.WORLD> set termout on ops$tkyte@ORA9I.WORLD> select owner, trigger_name 2 from
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You https://community.oracle.com/thread/2412190?start=15&tstart=0 can not post a blank message. Please type your message and try again. http://osamamustafa.blogspot.com/2012/07/oracle-discussion-forums-oracle.html More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This discussion is archived 1 2 Previous Next 22 Replies Latest reply on Jul 9, 2012 5:57 AM by 914302 Go to original post This content has been marked as final. Show 22 replies 15. Re: ORA-00604: error occurred error occurred at recursive SQL level 1 914302 Jul 8, 2012 12:39 PM (in response to Osama_Mustafa) Dear Osama I got the same error SQL> SET SERVEROUT ON SIZE 1000000 SQL> create user test identified by test; create user test identified by test * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-06502: PL/SQL: numeric or value error: character string buffer too error occurred at small ORA-06512: at line 7 Thanks. Like Show 0 Likes(0) Actions 16. Re: ORA-00604: error occurred at recursive SQL level 1 Osama_Mustafa Jul 8, 2012 12:48 PM (in response to 914302) Check metalink How to Determine the Cause of ORA-6502 Errors [ID 139548.1] Osama ... Like Show 0 Likes(0) Actions 17. Re: ORA-00604: error occurred at recursive SQL level 1 Solomon Yakobson Jul 8, 2012 12:51 PM (in response to 914302) One more thing. To 100% make sure error is trigger related, on Test database issue: alter system set "_system_trig_enabled"=FALSE;And then try creating/altering user. For example: SQL> create or replace 2 trigger ORA_06502 3 after alter 4 on database 5 declare 6 v_str1 varchar2(1); 7 v_str2 varchar2(3); 8 begin 9 v_str2 := 'ABC'; 10 v_str1 := v_str2; 11 end; 12 / Trigger created. SQL> alter user u1 identified by u1 2 / alter user u1 identified by u1 * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 6 SQL> alter system set "_system_trig_e
u1 identified by u1 2 / alter user u1 identified by u1 * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 6 Solution : SQL> alter system set "_system_trig_enabled"=FALSE; System altered. Enjoy Osama Mustafa Posted by Osama Mustafa at 4:12:00 PM Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest Labels: ORA-00604, oracle, Oracle Blog, Oracle Blog ., oracle blogs, Osama, Osama blog, Osama mustafa, Osama mustafa blog, osama oracle, recursive SQL, Sql Error, SQL level 1 1 comment: AnonymousJanuary 8, 2014 at 1:21 AMThanks, Osama! I was trying to log in as a schema user in order to create a fixed user database link and got these two errors. Running your Alter system command did the trick!Regards,JasonReplyDeleteAdd commentLoad more... Newer Post Older Post Home Subscribe to: Post Comments (Atom) Oracle ACE About Me Osama Mustafa Osama Mustafa is a database specialist, Oracle ACE Director , Certified Oracle Professional (10g, 11g), Certified Ethical hacker (Penetration testing), and Sun System Administrator.Osama currently works as an Oracle Instructor in the Middle East. He also works on troubleshooting and the implementation of database projects.He spends his free time on Oracle OTN forums and publishes many articles, including Oracle database articles, in his bloghttp://osamamustafa.blogspot.com. View my complete profile Soical Network Linked In My Twitter My Book oracle penetration testing Blogroll Kevin Closson Kellyn PotVin-Gorman Gokhan Atil Blog HeliFromFinland Blog Archive ► 2016 (27) ► October (6) ► September (2) ► August (4) ► June (1) ► May (3) ► April (5) ► March (1) ► February (1) ► January (4) ► 2015 (71) ► December (3) ► November (3) ► September (4) ► August (2) ► July (6) ► June (4) ► May (8) ► April (8) ► March (21) ► February (6) ► January (6) ► 2014 (77) ► December (5) ► November (4) ► October (6) ► September (3) ► August (8) ► July (7) ► June (7) ► May (4) ► April (9) ► March (5) ► February (4) ► January (15) ► 2013 (184) ► December (5) ► November (8) ► October (10) ► September (8) ► August (5) ► July (12) ► June (24) ► May (18) ► April (22) ► March (23) ► February (26)