Tuesday, March 11 2014
Keywords: pl-sql, pl/sql, oracle database server, key value, table, lookup, parameter, system parameter save load, stored procedure
Quick & easy "Key Value" starter pack with table & Get/Set stored procedures, Oracle PL/SQL version
You could use key value tables to stored any kind of system or app level parameters you require. Almost all serious applications I have seen had used a form of key value table...and with the exception of a few, the great majority had a simple schema for storing these values...simplicity is a good principle in software design.
I am going to walk you through easy steps to create a key value sub-system in your Oracle database. You will find a fully functional PL/SQL sample downloadable at the bottom of this page. I have provided a SQL Server T-SQL version of this article as well in a separate article.
Here are the assumptions / business requirements for my app:
- Key is varchar2(100) and its size is more than enough for this app.
- Value is varchar2(1000) and its size is more than adequate for this app.
- My app will not be storing any Unicode key or value. If yours does, change the varchar2 to nvarchar2.
- Null or empty strings values will not be accepted neither as a key, nor as a value.
- If you look up a non-existent key, an error is raised. I want my middle-tier to be aware that the key did not even exist in the first place...maybe the app was doing something wrong.
- Key must be unique.
1) Launch "SQL Developer" and connect to a database of your choice where you will be creating these objects. If you do not have a test database to play around with...create a new database.
2) Download the zip file on the bottom of this page. Extract the files. Open all the .sql files with "SQL Developer". Run the script in "CreateTable.sql" to create the keyvalue table. notice the key is the primary key of the table...this will make sure that a unique index will be created on the key column...making the key lookups ultra fast...and will also assure the uniqueness of keys. Remember that uniqueness was one of our business requirements.
3) Create the SetParameter stored proc now. You should get a message on the bottom that "Procedure SetParamter Compiled.". Note that Null or empty string values are not being accepted into keyvalue table.
4) Create the GetParamter stored procedure. Note that if the looked up key is non-existent in the keyvalue table...then an error will be raised. This is intentional. Unless otherwise required by an app...I would like the calling application to know that the key did not exist in the table in the first place...for many apps this could be an indication of something wrong.
5) A quick test of SetParameter shows us that it works easily and instantaneously.
6) Let's do a Select on keyvalue table, to see with our own eyes that the record we just attempted to insert with SetParameter did infact get inserted into the table.
7) On SQL Developer's menu select View ---> Dbms Outut.
8) A Dbms Output window will show up...click the plus sign on it to add a connection.
9) Choose a connection from the list.
10) Now that we are 100% assured that the newly inserted record does exist...let's test our GetParameter stored procedure. If all is done well..you should get 'some value' returned to you.
11) Let's test another business requirement: lookup of non-existent key should generate an error.
12) And finally, let's test the integrity of our keyvalue table...by trying to insert a duplicate key.
We are done! You could take this fully functional starter-kit and build on top of it as you require.
Written by Montreal DBA Team