Oracle PL/SQL: Storing Application Settings With Oracle

You're always being told not to hard-code values into your code (and they're right BTW). This blog post gives instructions on creating a table/ package combination in order to allow settings to be saved/ loaded from within PL/SQL.

The first step is to create a table to store the values;

create table XXC_SYSTEMSETTINGS
(
  setting_section VARCHAR2(80) not null,
  setting_name    VARCHAR2(80) not null,
  setting_value   VARCHAR2(240) not null,
  setting_type    VARCHAR2(8) not null
);
alter table XXC_SYSTEMSETTINGS
  add constraint XXC_SYSTEMSETTINGS_PK primary key (SETTING_SECTION, SETTING_NAME)
  using index;

Now we've created a table an a primary key index so we can quickly search and find values. Now the next step we need to do is think about what values we actually want to be able to store.

The settings will need to be placed into the table so that they can be picked up by the package below. This is intended to be a very simple package allowing developers to create their own settings in the table but, by not having a non-developer user interface, preventing anyone else from having a go.

From my experience most settings are either numbers or text (and of course with text you can store anything else) so based on that I'm going to create a package for retrieving numbers and text - it would be easy to add another (for example a date) or to add a customisation to refine text to be something specific like a Zip Code. The package body therefore is;

CREATE OR REPLACE PACKAGE XXC_SYSTEMSETTING IS
  function getNumber(p_Section in varchar2,
                     p_Name    in varchar2,
                     p_Default in number) return number;
  function getText(p_Section in varchar2,
                   p_Name    in varchar2,
                   p_Default in varchar2) return varchar2;
  function textSettingExists(p_Section in varchar2, p_Name in varchar2)
    return boolean;
  function numberSettingExists(p_Section in varchar2, p_Name in varchar2)
    return boolean;
end XXC_SYSTEMSETTING;

Nothing earth-shattering, I've added the two "Exists" functions as it's often useful to know if the value already exists. The package body will be;

CREATE OR REPLACE PACKAGE BODY XXC_SYSTEMSETTING is
  cursor c_Sys(p_Section varchar2, p_Name varchar2, p_Type varchar2) is
    select sp.setting_value
      from XXC_SYSTEMSETTINGS sp
     where upper(sp.setting_section) = upper(p_Section)
       and upper(sp.setting_name) = upper(p_Name)
       and upper(sp.setting_type) = upper(p_Type);

  function TextExists(p_Section in varchar2, p_Name in varchar2)
    return boolean as
  begin
    return(getText(p_Section, p_Name, '@@@@') <> '@@@@');
  end TextExists;

  function NumberExists(p_Section in varchar2, p_Name in varchar2)
    return boolean as
  begin
    return(getNumber(p_Section, p_Name, -905040) <> -905040);
  end NumberExists;

  function get(p_Section in varchar2,
               p_Name    in varchar2,
               p_Type    in varchar2,
               p_Default in varchar2) return varchar2 as
    pragma autonomous_transaction;
 
    v_Result varchar2(255);
  begin
    open c_Sys(p_Section, p_Name, p_Type);
    fetch c_Sys
      into v_Result;
    close c_Sys;
 
    if v_Result is null then
      v_Result := p_Default;
    end if;
 
    return v_Result;
  end get;

  function getNumber(p_Section in varchar2,
                     p_Name    in varchar2,
                     p_Default in number) return number as
 
    v_TextResult varchar2(255);
    v_Result     number;
  begin
    v_TextResult := get(p_Section, p_Name, 'NUMBER', to_char(p_Default));
    begin
      v_Result := to_number(v_TextResult);
    exception
      when others then
        v_Result := p_Default;
    end;
    return v_Result;
  end getNumber;

  function getText(p_Section in varchar2,
                   p_Name    in varchar2,
                   p_Default in varchar2) return varchar2 as
  begin
    return get(p_Section, p_Name, 'TEXT', p_Default);
  end getText;
end XXC_SYSTEMSETTING;

The getNumber function gives an example of how it works when we have specific formatting you want to apply to a setting.

Hopefully this will help you, it's actually saved me a lot of time over the years!