Popup LOV boxes that retrieve their list from a database table are relatively easy to set up in oracle forms, the equivalent for a dropdown list item takes a bit more work.
1) First, create a new item in the canvas with item type as ‘List Item’ – or, in my case I changed the item type of an existing ‘text item’ called CONTROL.CAS_UPLOAD to be a ‘list item’.
2) Second, create a procedure like the below. This is a procedure within the form, listed under program units, not a database stored procedure.
The second column selected here is the one that is actually passed into the form control, even although slightly confusingly the box will display the first column value after selection is made. Only the first column is shown in the drop down.
CONTROL.CAS_UPLOAD here is the name of my list item (cas_upload), qualified by block name (control).
PROCEDURE CREATE_Filenames_RG IS
-- Andrew Fraser v2.2 27th May 2010
-- Populate dynamic lookup
it_id1 Item := Find_Item('CONTROL.CAS_UPLOAD');
group1_id RecordGroup;
GRP_status NUMBER;
V_Space varchar2(10) := ''''||'0'||'''';
BEGIN
group1_id := Find_Group('FILENAMES_RG');
IF NOT Id_Null(group1_id) THEN
delete_group(group1_id);
END IF;
group1_id := Create_Group_From_Query('FILENAMES_RG',
'SELECT TO_CHAR(f.date_created, ''DD-Mon-YYYY HH24:MI'')||'||''''||' -- '||''''||'||f.file_name , f.file_name '
||' FROM filename_table f'
||' WHERE f.flag_processed = ''N'''
||' ORDER BY f.date_created, f.file_name') ;
Grp_status := Populate_Group('FILENAMES_RG');
IF Grp_status = 0 THEN
If Not Id_Null(it_id1) THEN
If Get_Item_Property(it_id1,Item_Type) = 'LIST' Then
Clear_List(it_id1);
Populate_List(it_id1,'FILENAMES_RG');
END IF;
END IF;
END IF;
End;
3) Create/edit triggers to call this:
On form startup:
When-New-Form-Instance on the form as a whole:
-- Andrew Fraser v2.2 27-May-2010 CREATE_Filenames_RG;
And/or on mouse click if you want the database table requeried every single time the user clicks on the dropdown:
When-Mouse-Click on the new item itself:
-- Andrew Fraser v2.2 27-May-2010
Clear_List('CAS_UPLOAD');
Clear_Item;
CREATE_Filenames_RG;
4) Note that hard-coded lists get created with a record group and an associated LOV. Counter-intuitively, these are not used at all in the above method for database lookups.