Some of you will be using a single code table to hold all of your codes. This function will allow you to populate any list item for any type of code from a single code table. For example, assume that you have create the following table:

1 Make Tartan

2 Model 46

3 Make Catalina

4 Rigging Cutter

5 Rigging Sloop

6 Make Hunter

7 Rigging Ketch

Create a program unit function called Init_Codes_Multi and type in the following code:

-----------------------------------------------------

-- Function Init_Codes_Multi

-- Written by: Andreas M. Olligschlaeger

-- Date Written: October 1996

-- Last Modified:

-- Purpose: Populates a list items from a code table

-- with multiple code fields. Returns 0 on

-- success, -1 on failure.

-----------------------------------------------------

FUNCTION INIT_CODES_MULTI(V_TABLE IN VARCHAR2, V_KEY IN VARCHAR2,

V_ITEM IN VARCHAR2, V_FIELD IN VARCHAR2)

RETURN INTEGER IS

GROUP_ID RECORDGROUP;

LIST_ID ITEM;

V_TEST NUMBER;

BEGIN

GROUP_ID := CREATE_GROUP_FROM_QUERY('VECTOR',

'SELECT DESCRIPTION, TO_CHAR('||V_KEY||') FROM '||V_TABLE||

' WHERE CODE_TYPE = '''||V_FIELD||''' ORDER BY DESCRIPTION');

IF ID_NULL(GROUP_ID) THEN

V_TEST := SHOW_ALERT('DATABASE_ERROR');

RETURN -1;

END IF;

V_TEST := POPULATE_GROUP('VECTOR');

LIST_ID := FIND_ITEM(V_ITEM);

IF ID_NULL(LIST_ID) THEN

V_TEST := SHOW_ALERT('DATABASE_ERROR');

RETURN -1;

END IF;

POPULATE_LIST(LIST_ID,GROUP_ID);

DELETE_GROUP(GROUP_ID);

RETURN 0;

END;

You can call the function in a manner similar to they way you called the Init_Codes_Small function in the lab. For example, if your code table is called Code_Table, the primary key of the code table is CODE_ID, you want to populate the make field, your boat block is called B_Boat, and the make list item is called Make_List, you would call the function using the following statements:

Declare

V_test number

Being

V_test := Init_Codes_Multi('Code_Table','Code_ID','B_Boat.Make_List','Make');

End;