I have a package with functions and procedures that all have the repetitive logic of looking up a static table for fetching a code value.
I am not sure what is the best practice for achieving this. Will Oracle cache the table after the first call? Do I only need to put the common query to a function and call it from other functions/procedures? Does that mean every run on the functions/procedures, the select will run again? Is there a way of caching the table?
Any advice and ideas will be appreciated.
How much time are those lookups adding to your process? It may not be a problem worth solving if your volume is low.
Single value lookups by unique key will used buffered single block reads which means the blocks will nearly all end up in the database buffer cache, so as long as your lookup table isn't hundreds of GB in size and in excess of your caching capacity (unlikely), there won't be hardly any I/O involved when it gets cranked up.
The lookup SQL will be pre-parsed (assuming you are using bind variables, which in PL/SQL you would be unless you're using dynamic SQL, which is doubtful in this scenario), so the CPU overhead for each lookup quite minimal. We're talking milliseconds here.
If you extend your index to cover not only the unique key but also the column(s) you are retrieving, you can avoid looking at block buffers from the table completely. At high frequencies, this can noticeably reduce CPU overhead.
You can use the result cache. Just make sure the table isn't commonly modified, as the result cache can cause problems in a database if invalidations are too frequent. However for 1:1 lookups (one row result for one source table row) the result cache is not a very efficient mechanism. It's better suited for bypassing expensive aggregations.
For small lookup tables you can preload name-value pairs into PL/SQL associative arrays and simply lookup the value in the associative array in code rather than using SQL. This is very fast.
For larger lookups but with significant skew (many values not used, a few values used much more often), you can do your lookup with SQL but cache the result yourself in an associative array. Each lookup would then check the array first and only execute the lookup SQL if it's not already in the array. Values never needed won't be loaded. Just be sure you have enough PGA to hold the eventual size of the array.