1 2 Previous Next 22 Replies Latest reply on Jul 9, 2012 5:57 AM by 914302 Go to original post
      • 15. Re: ORA-00604: error occurred at recursive SQL level 1
        914302
        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 small
        ORA-06512: at line 7


        Thanks.
        • 16. Re: ORA-00604: error occurred at recursive SQL level 1
          Osama_Mustafa
          Check metalink
          How to Determine the Cause of ORA-6502 Errors [ID 139548.1]


          Osama ...
          • 17. Re: ORA-00604: error occurred at recursive SQL level 1
            Solomon Yakobson
            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_enabled"=FALSE;
            
            System altered.
            
            SQL> alter user u1 identified by u1
              2  /
            
            User altered.
            
            SQL> 
            SY.
            • 18. Re: ORA-00604: error occurred at recursive SQL level 1
              914302
              Dear SY

              I really thankful you worked on it and resolved this issue.

              After setting Parameter

              SQL> alter system set "_system_trig_enabled"=FALSE scope=spfile;

              I able to create users and

              SQL> create user test identified by test;

              User created.

              able to change password as well.

              SQL> alter user test identified by test01;

              User altered.

              Thanks a lot.
              • 19. Re: ORA-00604: error occurred at recursive SQL level 1
                Solomon Yakobson
                911299 wrote:

                I really thankful you worked on it and resolved this issue.
                No, it didn't. It simply masked the issue. And it only masked it in DDL triggers. It might happen in stored procedure or in plain insert. when you will be trying to fit a string that in Test database will occupy more bytes than it is defined in column. Issue:
                select  *
                  from  nls_database_parameters
                  where parameter like '%SET%'
                /
                on Prod & Test and compare results. I am almost positive they are different. Then ask yourself a question: how can I rely on testing my code in Test database if it has setting different from my Prod?

                SY.
                • 20. Re: ORA-00604: error occurred at recursive SQL level 1
                  914302
                  Dear Sy

                  O/P of both servers are same,

                  Prod

                  SQL> select * from nls_database_parameters where parameter like '%SET%'
                  2 /

                  PARAMETER VALUE
                  ------------------------------ ----------------------------------------
                  NLS_CHARACTERSET AR8MSWIN1256
                  NLS_NCHAR_CHARACTERSET AL16UTF16


                  Test

                  SQL> select * from nls_database_parameters where parameter like '%SET%'
                  2 /

                  PARAMETER VALUE
                  ------------------------------ ----------------------------------------
                  NLS_CHARACTERSET AR8MSWIN1256
                  NLS_NCHAR_CHARACTERSET AL16UTF16

                  Can you explain about hidden parameter you set to false.

                  Thanks.

                  Edited by: 911299 on Jul 8, 2012 6:30 AM
                  • 21. Re: ORA-00604: error occurred at recursive SQL level 1
                    Solomon Yakobson
                    911299 wrote:

                    O/P of both servers are same,
                    OK. You rulled that out. Now on both db issue:
                    column name format a30
                    column value format a30
                    select  name,
                            value
                      from  v$parameter
                      where name = 'nls_length_semantics'
                    /
                    Post results.
                    Can you explain about hidden parameter you set to false.
                    If this is set to false then system triggers will no longer fire. So since ALTER USER works when system triggers are disabled proves that issue is with one of your system triggers on Test database.

                    SY.
                    • 22. Re: ORA-00604: error occurred at recursive SQL level 1
                      914302
                      Hi

                      column name format a30
                      column value format a30
                      select name,
                      value
                      from v$parameter
                      where name = 'nls_length_semantics'
                      /

                      PROD :-

                      SQL> select name,value from v$parameter where name = 'nls_length_semantics'
                      2 /

                      NAME
                      --------------------------------------------------------------------------------
                      VALUE
                      --------------------------------------------------------------------------------
                      nls_length_semantics
                      BYTE

                      Test :-

                      SQL> select name,value from v$parameter where name = 'nls_length_semantics'
                      2 /

                      NAME VALUE
                      ------------------------------ ------------------------------
                      nls_length_semantics BYTE

                      Thanks for explanation .
                      1 2 Previous Next