An underhand compilation

Oracle cheats. Or, more generously, it sometimes plays by different rules. Normally a DDL will do an implicit commit of any outstanding transaction. Actually it does a couple of implicit commits, one before it tries the DDL and a second if it succeeds.


Furthermore, an 'ALTER PACKAGE .. COMPILE' is a DDL statement. And it does an implicit commit.


However, if the compilation occurs implicitly, because you are trying to execute a package that it is invalid, then your outstanding transaction ISN'T committed. That is what you want as your application can't cope with having partial transactions committed just because it needs to compile a package.


The question really is, if the commit isn't actually necessary, is it being performed by the ALTER PACKAGE simply to be consistent ?


Demo script follows:





XE> DROP TABLE plch_data PURGE;


XE> CREATE TABLE plch_data
  2    (id NUMBER, col_a NUMBER, col_b NUMBER);


XE> CREATE OR REPLACE PACKAGE plch_pkg
  2  IS
  3     FUNCTION ret_count RETURN NUMBER;
  4  END plch_pkg;
  5  /


XE> CREATE OR REPLACE PACKAGE BODY plch_pkg
  2  IS
  3   FUNCTION ret_count RETURN NUMBER IS
  4     v_num NUMBER;
  5   BEGIN
  6     SELECT COUNT(id) INTO v_num
  7     FROM plch_data;
  8     --
  9     RETURN v_num;
 10   END ret_count;
 11  END plch_pkg;
 12  /


XE> SELECT object_name, object_type, status
  2  FROM user_objects
  3  WHERE object_name = 'PLCH_PKG';


OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
PLCH_PKG   PACKAGE             VALID
PLCH_PKG   PACKAGE BODY        VALID


XE> ALTER TABLE plch_data DROP COLUMN id;
XE> ALTER TABLE plch_data RENAME COLUMN col_a TO id;


XE> SELECT object_name, object_type, status
  2  FROM user_objects
  3  WHERE object_name = 'PLCH_PKG';


OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
PLCH_PKG   PACKAGE             VALID
PLCH_PKG   PACKAGE BODY        INVALID


XE> INSERT INTO plch_data (id) VALUES (10);


XE> EXEC dbms_output.PUT_LINE( plch_pkg.ret_count);
1


XE> ROLLBACK;


XE> EXEC dbms_output.PUT_LINE( plch_pkg.ret_count);
0




XE> REM =================================================================
XE> ALTER TABLE plch_data DROP COLUMN id;
XE> ALTER TABLE plch_data RENAME COLUMN col_b TO id;


XE> SELECT object_name, object_type, status
  2  FROM user_objects
  3  WHERE object_name = 'PLCH_PKG';


OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
PLCH_PKG   PACKAGE             VALID
PLCH_PKG   PACKAGE BODY        INVALID


XE> INSERT INTO plch_data (id) VALUES (10);


XE> ALTER PACKAGE PLCH_PKG COMPILE BODY;
XE> SELECT object_name, object_type, status
  2  FROM user_objects
  3  WHERE object_name = 'PLCH_PKG';


OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
PLCH_PKG   PACKAGE             VALID
PLCH_PKG   PACKAGE BODY        VALID


XE> EXEC dbms_output.PUT_LINE( plch_pkg.ret_count);
1


XE> ROLLBACK;


XE> EXEC dbms_output.PUT_LINE( plch_pkg.ret_count);
1