I'd like to take advance of the migration process to fix this a little bit. Can you give advice on a best practice for this?
What do you mean? Best practice for storing customer-specific parametrization? Even if you have those parameters as constants in a database package the solution wouldn't be very flexible, and code like
if customer_name = 'SOME_CUSTOMER' then do_something; elsif customer_name = 'SOME_OTHER_CUSTOMER' then do_something_else; end if;
really gives me the creeps. IMHO the most flexible way would be to store those parametervalues in a table; if you give them non-customer specific names (like +do_something_on_something+ instead +weird_customer_x_logic_when_something+) you have an even more general way to customize your application.
Thanks. I was hoping there would be something better than both approaches (package constants or parameter table) bundled within the database.
Of course the
if customer name = 'COMPANY_A' then use this logic ...
gives the creeps to anyone.
Instead of this everything should be
if logic_to_do_this = 'A' then ...
There are two minor problems with the table approach:
Single row and one column for each parameter? (thus you can control the parameter datatype, but need to ddl everytime you add a parameter.
or a parameter table with parameter name/value pairs? (here you'd have to go with varchar for everything, but you could have a set of pre-established conversion masks)
I prefer the second (you only need to establish masks for number and date parameters), but I'm a bit late, as the app has been working with a single row parameter table from the beginning, and they even didn't wrap it with a getter function.
In fact, in an old forms application I developed where customization was paramount, I remember I used two tables master / detail: the master table for the parameter "context" definition and the detail table for the values and dates of entry in force (the app worked 24x7 and users even needed to program changes in preferences in advance). A getter function gets you the value currently in force.
I'd go for name/value pairs; you could store the datatype along with a numeric/date/varchar2 value; like:
create table params ( param_name varchar2(255), data_type varchar2(50), param_value varchar2(4000) );
and write a functions to get/set values which depending on data_type does a conversion to the correct data type. Or you add a column for each data type, and write a function which depending on the data_type returns the column; like
create table params ( param_name varchar2(255), data_type varchar2(50), char_value varchar2(4000), num_value number, date_value date );
Message was edited by: Christian Erlinger On a second thought the second method might be suboptimal as you need a decode everywhere you want to query all parameters. cheers