Re: Are one row, one column tables "acceptable"?

Thomas T wrote:

> I recently asked how to save a system variable into an Oracle database, and
> Sybrand Bakker, DA Morgan, and Hans Forbrich gave me some ideas I hadn't
> thought of (using a PL/SQL package constant, using a login trigger, etc).
> The general idea revolved around using the PL/SQL package to pull the value
> once from a table, instead of referencing the table over and over and over
> again.
>
> How acceptable are one row, one column tables? I've heard of people storing
> multiple system variables in two-column, multi-row tables, with column names
> such as "parameter_name" and "parameter_value". But is using a one row/one
> column table common? Or is it a way that works, but of bad design (like
> creating multiple public rollback segments in the System tablespace)?
>
> Are there any particular ways I should define this table? Do I -need- an
> index? (It's only one value.) Should I store this table in the system
> tablespace? Restrict the storage to a few kilobytes? Keep the tablespace
> from extending itself?
>
> Should I create a two-column, multi-row table anyway, for "future
> implementation"?
>
> Thanks!
>
> -Thomas

I like the answers Hans and Mark have provided but I routinely build a parameters table into applications I build. The table has two columns and serves the same purpose as one might find were v_$parameters a table.

It allows me to load a variety of initialization parameters that are required universally by an application. Some examples of possible parameters are:

1. company licensed to operate the software
2. number of licensed users
3. minimum acceptable date entry (to be used by triggers that do date validation)
4. address of email server
5. email address responsible DBA / SysAdmin resouce for UTL_SMTP

I am against ever hard-coding parameters that may later require modification. And sometimes it makes sense to encrypt those entries such as the number of licensed users.

Daniel Morgan Received on Sun Apr 06 2003 - 12:40:50 CDT