tips and tricks - time hopefully for some discussion

I always appreciate reading other peoples responses to questions posted on the net. Sometimes seeing a fresh approach jogs the odd brain cell ...

To improve on this could ...

1. People post their replies if they think it would benefit a wider audience then the original poster.

2 ) I work at at large oracle site, the main problem we have is the code gets better the more you write, and with oracle the first few attempts not only are suspect but can bring computers to their knees ... so rather than us all wandering around in the dark if youve discovered a nifty way of doing something in oracle drop a line.

right I'll get off my soap box now and add the first tip and trick session

Use of lookup codes and values ...

problem : avoiding the hard coding of values within forms

description :

At some time or another we have the problem of whether or not to hard code values into a program. You can probably answer yes, if the values are boolean 'Y' or 'N'. If there are enough values, we can justify the creation of a table ... but do we do it for all our constants and what is the break point at which we say a table is justified ?

possible solution :

the solution we adopted is in part borrowed from financials and some old relational notes from uni.

If the field has one value then we place it in a parameter file table, such as for a legal system we are doing at the moment ...

table ccm_param_file

fields       pf_default_printer   
             pf_current_location
             pf_supervisor
             prison_men
             prison_female
             

This places the constants within one structure, it also provides the benefit that if the system is installed at many sites (4 in the above example) only the fields in the table need be updated.

ok, what about multiple values .... we implement the following

table ccm_lookup

fields		l_type            - type of item
                l_desc            - description of item
                l_multiple_values - does it have multiple values
                l_user_update     - can a user manipulate values
                
table ccm_lookup_code
fields          l_type            - foriegn key to ccm_lookup
                lc_value          - actual value

We have setup two tables that are referenced from forms. The first table contains all the different types of items we are interested in. The second contains the values that these type can have. We also allow user to access these lists and modify as they need to.

examples, hopefully to clear the muddy waters ...

We can specify a default printer ....

           select lc_value
           into   default_printer
           from   ccm_lookup_code
           where  l_type = 'DEFAULT_PRINTER';

We can select people who are active employees ....         

           select p_surname
           from   ccm_personnel
           where  p_status NOT IN (
                       select lc_value
                       from   ccm_lookup_code
                       where  l_type = 'INACTIVE_STATUS' )

  in this case the lc_values could be ('FIRED','DEAD','LEAVE' ... ) but   the statuses are now at the descretion of the user and distinct from   the forms.

ok, I now expect some of the purists to flame me and question why I dont want to create tables but it works !!

Anyway, lets have some comments ... and a few articles of a similar vein.

cheers

+=======================================================================+

|          Mark Hayes                                                   |
|  ,-_|\   Corporate Systems               Internet: mah_at_DIALix.oz.au   |
| /     \  83 Mill Point Road              Phone: +61 9 264 1616        |
| *_,-._/  South Perth  WA 6151            Fax:   +61 9 474 2553        |
|      v                                                                |


+=======================================================================+

          Theres a mountain of code to my next ski trip ....