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.

Posted in Oracle forms
8 comments on “Oracle Forms Dropdown List Item values populated from database table
  1. gbadebo awoyale says:

    I WANT SOLUTION IN A SITUATION WHERE STATE NAME, LOCAL GOVT/DISTRICT NAME ARE ON THE SAME FORM AND
    EACH STATE WILL DISPLAY ITS CORRESPONDING LOCAL GOVT NAME. tHAT TWO DEPENDENT LIST ITEM ON THE SAME FORM

  2. himanshu says:

    its not working. i tried it….

  3. himanshu says:

    not working…

  4. Big Akis says:

    It wont work when the list item is required and the initial value set in the forms designer is not contained in the result sets. Set the item as not required and remove the initial value.
    I cannot find a way to change the initial value setting by using pl/sql

  5. xnnc says:

    For my need, it works perfectly 🙂

  6. Atom says:

    Hi all,
    btw how to set value for “code” and “description” to column that it will insert to database.
    i mean like return value if using LOV, but i want both of code and description.

    thanks b4

  7. ziey_kjsb says:

    hi, all…i’m new learner in oracle form..i want to ask on how to set value for list item

  8. Sri says:

    How to customize or filter Poplist values from forms personalization?

Leave a Reply

Your email address will not be published. Required fields are marked *

*