Retrieving Reference Codelist Names and Values

Some database object type attributes (those ending in the string _rc) have a fixed set of allowed values stored in a lookup (reference codelist). These attributes correspond to fields in the user interface with a drop-down or pop-up list of values. To supply or change one of these values you must enter the exact string stored in the reference codelist, with the codelist name surrounded by dollar signs and followed by a codelist value.

For example, the API to create any object includes a parameter of type cdr_naming_version_obj_type, one of whose attributes is object_type_rc. You must enter the correct string for the type of object you want to create.

Reference codelists are stored in a table you access through the view cdr_lookups. The following columns contain the following information:

• lookup_type: reference codelist names
• lookup_code: reference codelist values
• meaning: the text that is displayed in the user interface
• description: additional information (sometimes)

If you have LSH Setup Admin privileges you can look up reference codelists in the Applications user interface; see "Querying and Viewing Lookups" in the Oracle Life Sciences Data Hub System Administrator's Guide.

You can browse the view in a tool like SQL Developer to find these values. However, it is not always easy to guess the name of the reference codelist. In that case, you can go into the Oracle LSH user interface to where they are displayed and note one of the allowed values, then query.

For example, object types are displayed in the Add drop-down list in the Work Area Properties screen. You can see that one object type is Business Area, so you can use the following query:

select lookup_type, lookup_code, meaning from cdr_lookups where meaning like '%Business Area%';

Now you know that the lookup_type for object types is CDR_OBJECT_TYPES and you can use the following query to get all the other values:

select distinct lookup_code, meaning from cdr_lookups where lookup_type = 'CDR_OBJECT_TYPES';