Oracle PL/SQL for DBAs

Standardizing Object and Collection Names

After implementing table functions in several applications, I was alarmed to see the number of duplicate object and collection types I had implemented. For example, I had created these two objects:

    SQL> DESC experiment_results_o
     Name                                      Null?    Type
     ----------------------------------------- -------- ------
     SAMPLE_AMT                                         NUMBER

    SQL> DESC research_tallies_o
     Name                                      Null?    Type
     ----------------------------------------- -------- ------
     TALLY_TOTAL                                        NUMBER

And then rolled them up into collections with similar names—replacing the “_o” (underscore o) suffix with “_t”. This is just the simplest example of the clutter I created because I hadn’t focused on the database as a whole. I’ve since gone back and replaced the two objects with a single one like this:

    SQL> DESC number_o
     Name                                      Null?    Type
     ----------------------------------------- -------- ------
     COL1                                               NUMBER

I also have similar generic objects for other datatypes including several standard lengths of VARCHAR2 fields.

Another standard I follow is that my object names have “_o” appended while my collections (or tables) have “_t” appended. This allows me to discern quickly what type they are.