Creating lookup tables that support a drop-down menu for categories

Want to create tables to support drop-down menus for categories in PL/SQL? Learn how in this tip from PL/SQL expert Dan Clamage.

I am a new software developer and would like you to help me since I am new with PL/SQL programming language. How can you create lookup tables which must have a drop-down menu with categories? Thanks for your support in advance.

To support a drop-down menu for categories, I would create a table containing a unique identifier, the menu name, a sort order, a flag for whether this item should initially be the one highlighted when the menu is displayed, and the category string. For example:

 CREATE TABLE dropdown_category (
dropdown_category_id NUMBER(10)
,dropdown_menu_name VARCHAR2(60)
,sort_order NUMBER(5)
,initial_focus NUMBER(1) -- 0 or 1
,category_name VARCHAR2(100)
,CONSTRAINT pk_dropdown_category PRIMARY KEY (dropdown_category_id)
,CONSTRAINT uk_dropdown_category UNIQUE (dropdown_menu_name, category_name)
,CONSTRAINT ck_initial_focus CHECK (initial_focus = 0 OR initial_focus = 1)


You can use a sequence object and a row-level Before-Insert trigger to provide values for the id.

The sort order is intended to be within a single menu, and I wouldn't expect each menu to have more than say, thousands of entries. Likewise, the category name should be unique within a menu.

If you want, you could use triggers to enforce the business rule that each drop-down menu (by name) can have exactly zero or one row with the initial_focus flag set. To implement this, you would need:

  • A row-level Before Insert or Update trigger to accumulate in a packaged collection the menu names and id's that have the flag set; remember a single DML statement can affect multiple rows.
  • A PL/SQL package to provide an API for initializing, adding, resetting and testing a private-global packaged collection that captures these id's.
  • A statement-level After Insert or Update trigger to test the id's captured, and raise an exception if there's more than one set to 1 for a menu name.
  • A statement-level Before Insert or Update trigger to reset (delete the entries in) the packaged collection.

This complexity arises because row-level triggers can't query the table they're operating against; you'll get the dreaded "Mutating Table" exception if you attempt to do so.

Some database designers don't care for the idea that every different kind of drop-down menu requires its own table. They try to design a generic "table within a table" that can store many kinds of drop-down menus with varying numbers of fields. This sounds great in theory. However, in practice, it doesn't scale. When you try to accommodate varying numbers and types of menu columns in your table, you find yourself writing complex pivot queries to extract the different sets of columns, and performance greatly suffers.

Don't be tempted to cram more than a single entity into a physical table. A category drop-down isn't the same entity as a country code/name drop-down; which is different from a state code/name drop-down. Even though the latter two have similar structures, logically they are different entities, and deserving of their own tables.

Have a PL/SQL question for Dan Clamage? Send an e-mail to [email protected]


Dig Deeper on Using Oracle PL-SQL