9 Replies Latest reply: Apr 12, 2013 10:35 PM by sb92075 RSS

    error occurred at recursive SQL level 1

    650632
      Hi


      I have oracle 11g database and when I am trying to run a package, procedure I am getting same error.


      SQL> set serveroutput on
      SQL> @c:/oraclestudy/matrixmul1.sql
      create or replace procedure procmatrixmul1 is
      *
      ERROR at line 1:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-06550: line 5, column 15:
      PL/SQL: ORA-00942: table or view does not exist
      ORA-06550: line 5, column 3:
      PL/SQL: SQL Statement ignored


      what is this regarding any help would be appreciated.
        • 1. Re: error occurred at recursive SQL level 1
          Boneist
          PL/SQL: ORA-00942: table or view does not exist

          Do all the tables/views in your package exist, and if there are references to tables/views in other schemas, are there direct grants to the user running the packaged procedure for the tables/views (as opposed to grants to a role that the user has been granted)?
          • 2. Re: error occurred at recursive SQL level 1
            650632
            hi thanks

            As all tables and views are exits as we find them and they are also not referanced to the another schema and there are direct grants to the user running the packaged procedure.
            • 3. Re: error occurred at recursive SQL level 1
              649102
              So the procedure only references objects within it's own schema? Can you post the code for matrixmul1.sql as well as the results of select * from user_tables?
              • 4. Re: error occurred at recursive SQL level 1
                Billy~Verreynne
                A recursive SQL is a system (internally generated) SQL. The error message details for 11g says:

                00604, 00000, "error occurred at recursive SQL level %s"
                // *Cause:  An error occurred while processing a recursive SQL statement
                // (a statement applying to internal dictionary tables).
                // *Action: If the situation described in the next error on the stack
                // can be corrected, do so; otherwise contact Oracle Support.
                The cause is very likely due to a problem with invalid Oracle packages. missing data dictionary views, or something along those lines. The problem is often caused by an incorrect or incomplete Oracle database creation or upgrade.

                What "system" code/tables/interfaces does your script use? E.g. streams, AQ, DBMS_PIPE, XDB, etc.
                • 5. Re: error occurred at recursive SQL level 1
                  650632
                  hi

                  when running any procedure it is giving same error


                  SQL> @c:/oraclestudy/procsubexcpt.sql
                  create or replace procedure procsubexcpt(vdeptid number) IS
                  *
                  ERROR at line 1:
                  ORA-00604: error occurred at recursive SQL level 1
                  ORA-06550: line 5, column 15:
                  PL/SQL: ORA-00942: table or view does not exist
                  ORA-06550: line 5, column 3:
                  PL/SQL: SQL Statement ignored


                  *********code for procedure*******************

                  create or replace procedure procsubexcpt(vdeptid number) IS
                       empcount number;
                       result number;
                       no_emp_value exception;
                  begin
                       BEGIN
                            Select count(*)
                       into empcount
                       From emp
                            Where deptid = vdeptid;     
                       
                            If empcount = 0 Then
                                 Raise no_emp_value;
                            End If;

                       exception
                            when no_emp_value then
                                 dbms_output.put_line( 'Empcount is Zero. subblock result User defined exception' );     
                            
                                 result := 1000/empcount;     

                                 dbms_output.put_line( 'result = ' || result );
                       end;

                       
                  exception
                       when no_emp_value then
                            dbms_output.put_line( 'Empcount is Zero. main block result User defined exception' );     
                            dbms_output.put_line( 'sql code = ' || sqlcode);
                            dbms_output.put_line( 'sql error message = ' || substr(sqlerrm,1,250));
                       when zero_divide Then
                            dbms_output.put_line( 'Empcount is Zero. system defined exception' );               
                            dbms_output.put_line( 'sql code = ' || sqlcode);
                       --     dbms_output.put_line( 'sql error message = ' || substr(sqlerrm,1,250));
                       when too_many_rows Then
                            dbms_output.put_line( 'Dept ' || vdeptid || ' has more than 1 employees.' );               
                       when others then
                            dbms_output.put_line( 'others');
                  end procsubexcpt;
                  /


                  *********select * from user_tables it is giving me 12 rows back ***************


                  and in there there are objects ,tables are present that we are using

                  thanks
                  • 6. Re: error occurred at recursive SQL level 1
                    696330
                    I have oracle 8g database and when I am trying to run a package, procedure I am getting same error.


                    ERROR:
                    gt: error occurred at recursive SQL level 1
                    ORA-00376: file 17 cannot be read at this time
                    ORA-01110: data file 17: '/export/home1/OraHome1/dbs/rbs03'

                    what is this regarding any help would be appreciated.
                    • 7. Re: error occurred at recursive SQL level 1
                      94799
                      That is not the same error.

                      The first line indicates that the error occurred during a recursive SQL (one implicitly executed by Oracle).

                      The other errors indicate the underlying cause, in your case one of your datafiles is either corrupt, missing or inaccessible. You may wish to ask about that issue on the DBA forum.
                      • 8. Re: error occurred at recursive SQL level 1
                        1001554
                        can you please advice;
                        I am trying to connect my oracle Database to ASP page and i am getting error:

                        ORA-00604: Error occurred at recursive SQL level 1
                        ORA-01843:not a valid month
                        ORA-06512: at line 2
                        • 9. Re: error occurred at recursive SQL level 1
                          sb92075
                          sric wrote:
                          can you please advice;
                          I am trying to connect my oracle Database to ASP page and i am getting error:

                          ORA-00604: Error occurred at recursive SQL level 1
                          ORA-01843:not a valid month
                          ORA-06512: at line 2
                          fix your error

                          start your own thread


                          How do I ask a question on the forums?
                          SQL and PL/SQL FAQ