Re: Using packaged constants in queries

Thank you very much for finding time for me.

I agree with your approach. Storing constants in a table has a lot of benefits. Flexibility, extendibility, etc. The reasons why instead of this pure approach I use constants are as follows -

1. These constants are static lookup IDs (type IDs, access level IDs, etc.) they do not change over time. If instead of specifying these IDs in where clauses of my views I do joins with a constants table it will have a noticeable impact on the performance. Sooner or later I will have to optimize these views by getting rid of joins and replacing them with constants.
2. Resolving constant names into IDs in PL/SQL code not only adds overhead but also makes the code look a bit bulky.

By using constants I solve these problems but inevitably create other problems. I believe that in my particular case constants are all right.

> All I could see from what you were going through was that storing your
> constants in a table and getting them out that way with simple selects that
> can be done in either SQL or PL/SQL seemed a lot less complicated; I am not
> criticizing your work or saying we could not make it work, but unless there
> was a compelling reason to store and retrieve constant variables (is that an
> oxymoron?) in code, in general, I do not think is a good practice, except as
> I say, if there are extraordinary reasons.
>
> to grab variables out of your "constant variables" table,
> into PL/SQL, use a
>
> SELECT INTO....
>
> and then you are good to go with whatever constants.
>
> Plus, if the gravimetric acceleration, the speed of light, or Pi ever
> change, you can update it in a table.!
>
> :)
>
> RSH.

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG