PL/SQL - Constants Package

Occasionally, there is a need in PL/SQL to have a package of constants. In one such project I had, there were some drop down values that we stored in the database and we allowed the users to change the text. We had to write some logic based on the drop down value, but couldn't count on the text remaining the same. So I wrote a constants package:

create or replace package CONSTANTS
as
TYPE constants is table of NUMBER(38) INDEX BY VARCHAR2(100);
enum constants;
enum_size = 4;
function getContstant(in_val varchar2) return number;
end;

create or replace package body CONSTANTS
as
PROCEDURE load_data IS
BEGIN
  enum('CONST1') := 50;
  enum('CONST2') := 51;
  enum('CONST3') := 52;
  enum('CONST4') := 53;
end load_data;

function getConstant(in_val varchar2) return number
is
begin
  if enum.COUNT <> enum_size THEN
    load_data();
  end if;
  return enum(upper(in_val));
  EXCEPTION WHEN OTHERS THEN RETURN -1;
end getConstant;
end;

-- usage
select * from some_table where constant_id = CONSTANTS.getConstant('const1');

You may ask and rightfully so, why not just create a package with a bunch of variables that represent that value? Good question. The key to the package is the load_data function. It can be modified to support any implementation of the way you save your constants. In the example above, I hard coded them in the package, but I could have just as easily read them from the database to avoid my constants table from getting a lot of database hits. Hopefully, you'll be able to use the above to your advantage. (Note: The following has only been compiled and tested on Oracle 10g.)