Packages: pkg_db_type & pkg_db_const

Like most developers, when coding PL/SQL I find myself requiring the use of the same data types and constants. To prevent excessive repetition of the same declarations and values I have centralised key type definitions and constants into two packages:

pkg_db_type

This package contains type definitions for common database types that I find myself requiring throughout application code. The type definitions are database level types and are not associated with any application. The package is specifically designed so that it has no dependencies on other objects, such as being anchored to table column types.

CREATE OR REPLACE PACKAGE pkg_db_type
AS

--------------------------------------------------------------------------------
--  Package       : pkg_db_type
--  Author        : Mark Hoxey
--  Creation Date : 21/09/2009
--
--  Overview      : Common database types
-----------------------------------------------------------------
-- Source Control: $Id$
-----------------------------------------------------------------
--  Modification History :
--
--  Initials  Date        Description
--  --------  ----------  ---------------------------------------
--  M Hoxey   21/09/2009  Initial version
--  M Hoxey   04/02/2010  Added context specific data types
-----------------------------------------------------------------

-- The size of an Oracle database object name
SUBTYPE t_object_name   IS VARCHAR2(30 BYTE);

-- The maximum size of a VARCHAR2 string
SUBTYPE t_big_string    IS VARCHAR2(32767 BYTE);

-- The maximum size of a VARCHAR2 string for
-- the database engine
SUBTYPE t_max_db_string IS VARCHAR2(4000 BYTE);

-- The data type of SQLERRM
SUBTYPE t_sqlerrm       IS VARCHAR2(512 BYTE);

-- The data type of an error message for RAISE_APPLICATION_ERROR
SUBTYPE t_err_msg       IS VARCHAR2(2000 BYTE);

-- The data types of Oracle module, action and client_info
-- strings
SUBTYPE t_action_name   IS VARCHAR2(32 BYTE);
SUBTYPE t_module_name   IS VARCHAR2(48 BYTE);
SUBTYPE t_client_info   IS VARCHAR2(64 BYTE);

-- Data types for context variables
SUBTYPE t_context_namespace IS VARCHAR2(30 BYTE);
SUBTYPE t_context_attribute IS VARCHAR2(30 BYTE);
SUBTYPE t_context_value     IS VARCHAR2(256 BYTE); 

END pkg_db_type;
/

pkg_db_const

Following on from the previous package, this one contains commonly used, *application agnostic* constants. Again, this package has no dependencies on any other object and has no package body.

CREATE OR REPLACE PACKAGE pkg_db_const
AS

-----------------------------------------------------------------
--  Package       : pkg_db_const
--  Author        : Mark Hoxey
--  Creation Date : 24/09/2009
--
--  Overview      : Common database associated constants
-----------------------------------------------------------------
-- Source Control: $Id$
-----------------------------------------------------------------
--  Modification History :
--
--  Initials  Date        Description
--  --------  ----------  ---------------------------------------
--  M Hoxey   24/09/2009  Initial version
--  M Hoxey   04/02/2010  Added context specific constants
-----------------------------------------------------------------

-- The size of an Oracle database object name
c_object_name_len          CONSTANT NUMBER(2) := 30;

-- The maximum size of a VARCHAR2 string
c_max_plsql_string_len     CONSTANT NUMBER(6) := 32767;

-- The maximum size of a VARCHAR2 string for
-- the database engine
c_max_db_string_len        CONSTANT NUMBER(4) := 4000;

-- The size of SQLERRM
c_max_sqlerrm_len          CONSTANT NUMBER(3) := 512;

-- The size of an error message for RAISE_APPLICATION_ERROR
c_max_err_msg_len          CONSTANT NUMBER(4) := 2000;

-- The size of Oracle module, action and client_info
-- strings
c_action_name_len          CONSTANT NUMBER(2) := 32;
c_module_name_len          CONSTANT NUMBER(2) := 48;
c_client_info_len          CONSTANT NUMBER(2) := 64;

-- The size of a dbms_output line
--   Oracle 9i and earlier is 255
--   Oracle 10g and later is 32767
c_max_dbms_output_line_len CONSTANT NUMBER(5) := 32767;

-- The size of various context details
c_context_namespace_len    CONSTANT NUMBER(2) := 30;
c_context_attribute_len    CONSTANT NUMBER(2) := 30;
c_context_value_len        CONSTANT NUMBER(3) := 256;  

END pkg_db_const;
/

The drawback to this centralisation of types and constants is that any code using them will have a dependency on the packages. Within Oracle this has impact. If you’ve ever faced the following error then you will appreciate this:

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "<>" has been invalidated
ORA-04065: not executed, altered or dropped package "<>"
ORA-06508: PL/SQL: could not find program unit being called: "<>"
ORA-06512: at line 1

Oracle has made improvements in its dependency checking to reduce the invalidation of dependent code in recent versions of the database. It used to be that simply recompiling the referenced package without change would result in a dependent package being invalidated.

Let’s see what changes we’re able to make to our packages that won’t invalidate dependent packages. We start with a simple package that references both pkg_db_types and pkg_db_const packages and then make changes these packages to see when our dependent package is invalidated. Our test package is:

CREATE OR REPLACE PACKAGE pkg_test
AS
   FUNCTION get_value
   RETURN pkg_db_type.t_big_string;
END pkg_test;
/

CREATE OR REPLACE PACKAGE BODY pkg_test
AS
   c_const CONSTANT NUMBER(6) := pkg_db_const.c_object_name_len;

   FUNCTION get_value
   RETURN pkg_db_type.t_big_string
   AS
   BEGIN
      RETURN TO_CHAR(c_const);
   END get_value;
END pkg_test;
/

Now we’ll make the following changes to pkg_db_type:

• Compile it without change
• Add a new type
• Remove a non-referenced type
• Alter a non-referenced type
• Alter a referenced type

We can test whether a change has invalidated our dependent package by running:

SELECT object_name
,      object_type
,      status
FROM   user_objects
WHERE  object_name = 'PKG_TEST'
/

Rather than list all the changes made to pkg_db_type, I’ll simply list the outcome of the test. Note, these results were obtained under Oracle Database version 11.2.0.2.0.

Our starting status is:

OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
PKG_TEST        PACKAGE             VALID
PKG_TEST        PACKAGE BODY        VALID

After adding a new type to pkg_db_type, we get:

OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
PKG_TEST        PACKAGE             VALID
PKG_TEST        PACKAGE BODY        VALID

Removing a non-referenced type, we get:

OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
PKG_TEST        PACKAGE             VALID
PKG_TEST        PACKAGE BODY        VALID

Altering a non-referenced type also results in:

OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
PKG_TEST        PACKAGE             VALID
PKG_TEST        PACKAGE BODY        VALID

It’s only when we alter a referenced type do we get:

OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
PKG_TEST        PACKAGE             INVALID
PKG_TEST        PACKAGE BODY        INVALID

This is quite pleasing. We can do many operations to our package and not invalidate our dependent code.

What about the constants though? Let’s perform the same type of test but this time make changes to pkg_db_const:

• Compile it without change
• Add a new constant after our referenced constant
• Add a new constant before our referenced constant
• Remove a non-referenced constant after our referenced constant
• Remove a non-referenced constant before our referenced constant
• Alter a non-referenced constant after our referenced constant
• Alter a non-referenced constant before our referenced constant
• Alter a referenced constant
• Alter the order of constants that includes our referenced one

The scenarios that invalidated our test package were:

• Add a new constant before our referenced constant
• Remove a non-referenced constant before our referenced constant
• Alter a referenced constant
• Alter the order of constants that includes our referenced one

Strangely enough, the position for adding a new constant impacted whether or not the test package was invalidated. Further testing using a local variable within pkg_test, as opposed to a package level constant, showed the same effect.

So, it would seem that we can make pretty much any change to the types package without impacting dependent packages so long as we don’t alter the specific types we’re using. The constants are a bit more complex. While we could say that if we want to add new constants we should be okay if we add them to the end of the package I would probably play safe and steer clear of any changes if there were concerns about impacting dependent code in a production system whilst the system was up.

The centralising of type definitions and constants in packages as shown here is a debatable strategy. On one hand it means we don’t have to repeat the definitions throughout different pieces of code. On the other hand we may be creating a situation where changes to these packages are difficult to make. The balance that I’ve tried to strike with these particular packages is that they are application agnostic and changes to them should be infrequent. Of course, in an environment with many developers this does rely on all members understanding this. If the packages end up being dumping grounds for any odd type or constant then problems will arise.

---

Download scripts shown in this post.

Advertisement