4 Replies Latest reply: Feb 15, 2013 2:56 PM by chuckers RSS

    Could someone with an 11.2 instance try something?

    chuckers
      We're on an 11.1.0.7 instance, and the following use of DBMS_UTILITY.FORMAT_ERROR_BACKTRACE gives us the wrong line number if the FOR ROW IN CSR loop is used. I was wondering if this was different in 11.2, since we're headed to 11.2.0.3 within the next 3 months or so:
      create or replace
      PACKAGE  TEST_BTRACE AS
      
        procedure MAIN(p_loop_type varchar2);
                       
      end;
      /
      create or replace
      PACKAGE BODY           "TEST_BTRACE" AS
      
      procedure p0200;
      procedure p0300;
      
      procedure MAIN(p_loop_type varchar2)  is
      
        cursor l_dual_csr is 
          select dummy from dual;
      
        type l_dual_tabtype is table of l_dual_csr%rowtype;
        l_dual_tab l_dual_tabtype;
      
      begin
      
        if p_loop_type = 'FOR_ROW_IN_CSR' then
        
          for l_row in (select * from dual)
          loop
            p0200();
          end loop;
          
        else
          open l_dual_csr;
          loop
            fetch l_dual_csr bulk collect into l_dual_tab limit 1000;
            
            for j in 1 .. l_dual_tab.count
            loop
              p0200();
            end loop;
            
            exit when l_dual_csr%NOTFOUND;
          end loop;
          close l_dual_csr;
        end if;
      
      exception
         WHEN others then
           dbms_output.put_line(dbms_utility.format_error_backtrace);
      end;
      -----------------------------------------------------------------------------
      procedure p0200 is
      begin   
        p0300; 
      End p0200;
      -----------------------------------------------------------------------------
      procedure p0300 is
        l_str varchar2(1);
      begin
        l_str := '12';
      end p0300;
      -----------------------------------------------------------------------------
      
      END TEST_BTRACE;
      /
      The first call to MAIN() only gives a line number within the FOR ROW IN CSR loop, while the second gives me the line stack:
      BEGIN
        TEST_BTRACE.MAIN('FOR_ROW_IN_CSR');
        TEST_BTRACE.MAIN('OPEN_FETCH_CLOSE');
      END;
      which gives
      ORA-06512: at "SCOTT.TEST_BTRACE", line 21
      
      ORA-06512: at "SCOTT.TEST_BTRACE", line 52
      ORA-06512: at "SCOTT.TEST_BTRACE", line 46
      ORA-06512: at "SCOTT.TEST_BTRACE", line 31
      What do you 11.2'ers get?
      Thanks
      --=Chuck
        • 1. Re: Could someone with an 11.2 instance try something?
          rp0428
          Vanilla 11.2.0.1 gives this:
          >
          ORA-06512: at "SCOTT.TEST_BTRACE", line 51
          ORA-06512: at "SCOTT.TEST_BTRACE", line 45
          ORA-06512: at "SCOTT.TEST_BTRACE", line 20

          ORA-06512: at "SCOTT.TEST_BTRACE", line 51
          ORA-06512: at "SCOTT.TEST_BTRACE", line 45
          ORA-06512: at "SCOTT.TEST_BTRACE", line 30
          >
          Is that what you were expecting?

          Vanilla 10.2.0.1 gives:
          >
          ORA-06512: at "SCOTT.TEST_BTRACE", line 51
          ORA-06512: at "SCOTT.TEST_BTRACE", line 45
          ORA-06512: at "SCOTT.TEST_BTRACE", line 18

          ORA-06512: at "SCOTT.TEST_BTRACE", line 51
          ORA-06512: at "SCOTT.TEST_BTRACE", line 45
          ORA-06512: at "SCOTT.TEST_BTRACE", line 30
          • 2. Re: Could someone with an 11.2 instance try something?
            EdStevens
            chuckers wrote:
            .... I was wondering if this was different in 11.2, since we're headed to 11.2.0.3 within the next 3 months or so:
            My first thought is that if you are "headed to 11.2.0.3 within the next 3 months or so", why don't you already have a 11.2.0.3 test system?
            My second thought is why you don't have a private test system that you can do anything you want with? The web is full of blogs telling you how to create your own system on your own PC, using VMworkstation or Virtual Box. I'm increasingly of the opinion that everyone who considers themselves a DBA should have their own private test lab on their own personal PC. There is simply no excuse not to.
            • 3. Re: Could someone with an 11.2 instance try something?
              chuckers
              You're getting the expected output in 10.2 also? Hmmm, then I'm definitely confused as to what's going on here. I was able to replicate the documented behavior above across all 3 of our 11.1.0.7.0 instances, where the line number of the error is not displayed using a FOR ROW IN CSR loop.

              --=Chuck
              • 4. Re: Could someone with an 11.2 instance try something?
                chuckers
                I replicated our experience in 10.2.0.3, meaning, WITH the flawed output to DBMS_OUTPUT.
                --=cf