Software Development

Sometimes a variables table is better than hard coding into a package


When writing PL/SQL packages, there are times where it's better to store a variable in a table instead of coding it into the package. It's most useful for a variable that you might want to change fairly often. If you put such a variable directly in the package (either the spec or body), the only way to modify it is to edit that package and recompile. The recompile can become problematic; if your application is still running then a user's session might have that package locked and it prevents compilation. It happens to me all the time when maintaining somebody else's application.

Now I store certain variables in a table instead. All it takes to change the variable (and thus the application's behavior) is a SQL update and commit to that table.

The variable table is pretty straightforward. I use two fields: a name and a value:

  CREATE TABLE "VARIABLES"

( "NAME" VARCHAR2(60 BYTE) NOT NULL,

"VALUE" VARCHAR2(255 BYTE),

CONSTRAINT "VARIABLES_PK" PRIMARY KEY ("NAME")

Now all you need is a function to return the values from that table:

create or replace

function get_variable (p_name variables.name%type) return variables.value%type is

cursor cur_variables is

select value

from variables

where name = p_name;

v_value variables.value%type;

begin

for v_rec in cur_variables loop

v_value := v_rec.value;

end loop;

return v_value;

end;

In your PL/SQL package, you might use a variable like this:

v_server_name variables.value%type := get_variable('server_name');

This really simple little trick can make things so much easier for you. I store pretty much everything I think may change often in such an external table.

19 comments
Igwekala
Igwekala

Sounds cool. But where speed is critical performance might suffer.

nuno-d-cardoso
nuno-d-cardoso

True, but for that problem, put it on the package begin clause. It only queries once per session. BR ndc

bscalzo
bscalzo

A long time ago (before sequences), this was how sequences were implemented. And even after sequences debuted, this was stiil the chief method by which one could guarantee sequential numbers with no gaps. HOWEVER - there is a major performance problem with this approoach that one must be cognisceint of and willing to accept. You now have a lock bottleneck if the value updates frequently. In a high usage system (lots of sessions calling this code), it could become the cheif limiting factor.

JohnnySacks
JohnnySacks

I'm not sure if there's any faster way to do this in an OLTP high throughput environment, especially when using the following: Update and retrieve values using only functions to avoid any SQL statement parsing. Use the PRAGMA AUTONOMOUS_TRANSACTION statement in the function to avoid any locking. Typically these values don't need to be transactional unless you're using it for sequences which cannot have any gaps but then a function called GET_NEXT_ID can be used to increment and return the value transactionally. Add ORGANIZATION INDEX to create the values table as an index organized table on the primary key. Add CACHE to the table creation to keep the table in the buffer cache.

Jaqui
Jaqui

most likely would become. Not every developer could create a database table and query set that are optimal for high traffic situations, by using the database table method it is most likely "will become the cheif bottleneck" Storing it into the application output file and in ram in an array would kill that bottleneck.

khasawneh4ever
khasawneh4ever

could you please provide a small example over that? Mohammed Khasawneh thanking you in advance

Justin James
Justin James

That is generally a good practice in *any* code, not just an Oracle package, *provided* that the way it is done does not significantly impact the performance. For example, reading a config file (or table) every time you reference a value, instead of just once at the begin of execution, is wasteful. J.Ja

DanLM
DanLM

Why wouldn't you do this???? Who doesn't do this? Examples of when I would want to do a Benny Hill slap on someone's head for hard coding values in their code. Tax percentages. Company district names. Percentages used in calculations. Oi, people.... It's going to change, trust me. Put it in a bloody table. Want to know what is a real Benny Hill slap offense is? When the system is designed with tables for this specific purpose. Even has gui front ends so that the users can change the values themselves... And people still hard code values. Dan

JohnnySacks
JohnnySacks

You name it, EMail notification recipient list, database connection params, file locations... If any change, set up the project, edit code, recompile, test (or not), deploy. Add entry on todo list to refactor in a configuration file at minimum (and add some error logging/handling to the empty try catch blocks at the same time)

Tony Hopkinson
Tony Hopkinson

Database connections string, urls, IPs, goddamn f'ing registry keys....

DanLM
DanLM

There is a primary example that you KNOW will always change... Always, always, always... And bloody people still hard code that. That just irritiates the sin out of me. Dan

beugley
beugley

I'm with Dan. I get frustrated when fellow developers don't have the foresight to see that this technique saves rework! I don't care how many people have told me it will never change... I can site over 20 specific instances where the user has come back to me after and said, "ummm sorry, I know I said it would never change..."

Tony Hopkinson
Tony Hopkinson

If a user says this will never change, it will Rule Two We will never need / want to do this... You know I said... Rule Three I can't think of any reason for this oops apart from these times. Any of these phrases should put a devloper in high alert, condition alpha homicidal communist islamic aliens have landed mode.

Tony Hopkinson
Tony Hopkinson

I find it very scary that something as basic as this come as a surprise to people.

Jaqui
Jaqui

;) Storing it in a database table instead of the application output file is an unusual way to keep it, since that data is specific for the particular document / project and would most likely need to be in any output file. At least, I tend to use the output file to keep the meta data in so it is available on different networks when it is needed. [ undo / redo history I store in a separate file so that there isn't a high possibility of disclosing confidential data, like when altering an existing letter with account data from one client to another. ]

Jaqui
Jaqui

most of the network enabled apps I have worked with work through a web interface, so everything is configured for multiple systems and even db connection strings are changeable. :D I did run into the work hours per day, and not just limited to 8 hours, but hard coded so that the work day was monday through friday 8 am to 5 pm. [ I chose not to use the scheduler / project management tools for K Office because of that stupidity. I may one day edit the sources and find that to fix it, but haven't really worried about it. ]

Tony Hopkinson
Tony Hopkinson

including password. See that one regular. Classic I've seen are number of hours in a working day. Working days in a year.... Standard income rate percentage VAT percentage. It's by people who don't think things through, but there are a shed load of them about.

Jaqui
Jaqui

is for variable names or constant data, variable data that needs to be persistent has to be written elsewhere. I've not run into any software that does hard code the "magic number" if that is variable data.

Tony Hopkinson
Tony Hopkinson

Where config stops and meta starts can be a bit blurry at time. For insnace I keep XSD's in the database to generate blank XMLs from. which database to hit xcan be in a config file and there is settings table to chcek the database schema version against the one the app code is written for. Also used by an installation checker utility which store a go no go in the database which the various apps in the suite check. Its'a tad messy at the moment and one 'method' would be nice, which one is much of a muchness though. Anything has got to be better than a hard coded magic number.