8 Replies Latest reply: Jul 24, 2014 5:26 PM by rp0428 RSS

    ORA-00604: error occurred at recursive SQL level 1

    Chaw

      hi everyone,

       

      i executed the script below and it worked.

       

       

       

      BEGIN

         FOR cur_rec IN (SELECT object_name, object_type

                           FROM user_objects

                          WHERE object_type IN

                                   ('TABLE',

                                    'VIEW',

                                    'PACKAGE',

                                    'PROCEDURE',

                                    'FUNCTION',

                                    'SEQUENCE'

                                   ))

         LOOP

            BEGIN

               IF cur_rec.object_type = 'TABLE'

               THEN

                  EXECUTE IMMEDIATE    'DROP '

                                    || cur_rec.object_type

                                    || ' "'

                                    || cur_rec.object_name

                                    || '" CASCADE CONSTRAINTS';

               ELSE

                  EXECUTE IMMEDIATE    'DROP '

                                    || cur_rec.object_type

                                    || ' "'

                                    || cur_rec.object_name

                                    || '"';

               END IF;

            EXCEPTION

               WHEN OTHERS

               THEN

                  DBMS_OUTPUT.put_line (   'FAILED: DROP '

                                        || cur_rec.object_type

                                        || ' "'

                                        || cur_rec.object_name

                                        || '"'

                                       );

            END;

         END LOOP;

      END;

      /

       

      but the problem is, after this, i cant grant, create or drop etc.. in my database even im using a sysdba user..

      i am getting an error

      ORA-00604: error occurred at recursive SQL level 1

      ORA-00942: table or view does not exist

       

      please help.. thanks

        • 1. Re: ORA-00604: error occurred at recursive SQL level 1
          Dan Jankowski

          Who did you run the procedure as? I wonder if you've dropped some SYS or SYSTEM objects.

          • 2. Re: ORA-00604: error occurred at recursive SQL level 1
            Martin Preiss

            that's a quite dangerous piece of code - and may result in lots of errors if you try to delete parent tables before the child tables etc. Using internal users (as sys, system etc. could make things much more problematic).

             

            But if you want to write code that tries to access USER_%-views I would recommend you take a look at the concepts of definer and invoker rights: Managing Security for Definer's Rights and Invoker's Rights

            • 3. Re: ORA-00604: error occurred at recursive SQL level 1
              GregV

              Hi,

               

              Is there more detail in the alert.log file, or has a trace file been created?

              • 4. Re: ORA-00604: error occurred at recursive SQL level 1
                BPeaslandDBA

                but the problem is, after this, i cant grant, create or drop etc.. in my database even im using a sysdba user..

                i am getting an error

                ORA-00604: error occurred at recursive SQL level 1

                ORA-00942: table or view does not exist

                 

                 

                Yikes! This was run as SYSDBA? That would drop Data Dictionary objects that you don't want to be dropped. No wonder the ORA-942 error was raised.

                 

                To know which statement is causing the ORA-604 error, start a SQL trace in the session. Then run the code again. In the trace file, search for the string "err=942".

                 

                Cheers,
                Brian

                • 5. Re: ORA-00604: error occurred at recursive SQL level 1
                  Chaw

                  guys thanks, i think my data dictionary got corrupted. but i am wondering why it did because i used a different user in executing the script. i decided to create a new instance instead

                  • 6. Re: ORA-00604: error occurred at recursive SQL level 1
                    rp0428
                    i think my data dictionary got corrupted. but i am wondering why it did because i used a different user in executing the script. i decided to create a new instance instead

                    That's it? Why are you just ignoring the questions you were ask? People are trying to help you; ignoring their questions is just rude.

                     

                    What PROBLEM are you trying to solve?

                     

                    Why do you think a script like that is the way to solve iti?

                     

                    What user did you run the script as?

                     

                    Do you realize that there are more types of objects than what your script is trying to drop?

                    ('TABLE',

                                                 'VIEW',

                                                  'PACKAGE',

                                                  'PROCEDURE',

                                                  'FUNCTION',

                                                  'SEQUENCE'

                                                 ))

                    What do you expect to happen if the user owns object types other than the ones you are dropping? For example materialized views, synonyms, MV logs on those tables you dropped?

                     

                    What do you expect to happen to objects that other users own that depend on the objects you are dropping? Other users might have grants on those objects on have code that references those objects.

                     

                    Whatever problem you have a script like that is NOT the way to solve it.

                     

                    But we can't help you until you answer the questions you have been ask and tell us what you are really trying to do.

                    • 7. Re: ORA-00604: error occurred at recursive SQL level 1
                      Chaw

                      i said i used a different user in running that script. that's the answer to their question if i used SYSDBA. thanks for your input.

                      • 8. Re: ORA-00604: error occurred at recursive SQL level 1
                        rp0428

                        And so now you just choose to ignore the questions that I ask?

                        What do you expect to happen if the user owns object types other than the ones you are dropping? For example materialized views, synonyms, MV logs on those tables you dropped?

                         

                        What do you expect to happen to objects that other users own that depend on the objects you are dropping? Other users might have grants on those objects on have code that references those objects.

                        People can't help you if you don't provide the info they need to try to reproduce your problem.