Monday, November 21, 2011

One Oracle 12g Pre-announce of bug fix in dbms_debug_jdwp

4 years ago, I filed a bug of dbms_debug_jdwp in 10gR2, after 2 years of pingpong playing, Oracle finally acknowledged the reproducibility of submitted testcase, but only promised to fix it in Oracle 12g.

Here the test code:

alter system set plsql_optimize_level = 1;

CREATE OR REPLACE PACKAGE ksun_nocopy_called
IS
   type t_rec is record(
    a                    number := 100
   ,b                    number
   );
  PROCEDURE called(
    o_rec     OUT NOCOPY t_rec
  );
END ksun_nocopy_called;
/

CREATE OR REPLACE PACKAGE BODY ksun_nocopy_called
IS
  PROCEDURE called(
    o_rec     OUT NOCOPY t_rec
  )
  IS
  BEGIN
    null;
  END called;
END ksun_nocopy_called;
/

CREATE OR REPLACE PACKAGE ksun_nocopy_caller
IS
  PROCEDURE caller;
END ksun_nocopy_caller;
/

CREATE OR REPLACE PACKAGE BODY ksun_nocopy_caller
IS
  b_rec_old                   ksun_nocopy_called.t_rec;
  PROCEDURE caller
  IS
  BEGIN
    ksun_nocopy_called.called(b_rec_old);
  END caller;
END ksun_nocopy_caller;
/


To run the testcase, start JDeveloper (Version 10.1.3.2.0), set a breakpoint at the line in ksun_nocopy_called.called:
    null;
   
Open a Sqlplus window, run the following code:

exec dbms_debug_jdwp.connect_tcp('pc-123', 4000);
exec ksun_nocopy_caller.caller
exec dbms_debug_jdwp.disconnect;

Then get the following message:

*********START PLSQL RUNTIME DUMP************
***Got internal error Exception caught in pl/sql run-time while running PLSQL***
***Got ORA-6544 while running PLSQL***
PACKAGE BODY K.KSUN_NOCOPY_CALLER:


Due to my impatience and their reluctance, two alternative approaches were worked out.

One is to substitute the package body variable with a procedure local variable as:

CREATE OR REPLACE PACKAGE BODY ksun_nocopy_caller
IS
  b_rec_old                   ksun_nocopy_called.t_rec;
  PROCEDURE caller
  IS
    l_rec_copy                ksun_nocopy_called.t_rec;
  BEGIN
    l_rec_copy := b_rec_old;
    ksun_nocopy_called.called(l_rec_copy);
    b_rec_old := l_rec_copy;
  END caller;
END ksun_nocopy_caller;

Another is to eliminate initial assignment in record declaration as:

   type t_rec is record(
    a                    number
   ,b                    number
   );
  
Probably Oracle was either encouraged by the simplicity of testcase, or inspired by the intuitive workarounds, they finally recognized the bug and projected to fix it in Oracle 12g.

By the way, DEBUG parameters are not updated in Oracle 12c.

Addendum (2015.10.19):  Oracle 12c document said:
   The PLSQL_DEBUG parameter is deprecated. It is retained for backward compatibility only (see PLSQL_DEBUG)

 it is replaced by:
   PLSQL_OPTIMIZE_LEVEL = 1

However 12c DBMS_TRACE document wrote:
   You can enable a program unit by compiling it debug.
       alter session set plsql_debug=true;

(see Database PL/SQL Packages and Types Reference)