Andrew Fraser DBA

Oracle Forms Dropdown List Item values populated from database table

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.