Email Us :
Free Training > SQL PL / SQL Forms Reports Oracle Applications
< Previous Next >

Forms - Record Groups

It is a collection of data.
It is source for LOV.
It will hold data physically.

Record Groups can be created in three types.
1) Based on select statement.
2) Based on static values.
3) Created through code dynamically.

Used to display more than one column of the table on to screen at once.

LOV ( List of values )
Used to display the content of the record group at runtime.
It will not hold any data.
It is a GUI component used to represent data in the record group.

Create table EMPEIGHT same as standard EMP table.
Create form based on EMPEIGHT table by using data block and layout wizard.
Save the form with the name EMP_RG.fmb

In Object Navigator, select Record Groups Click on create button  Data in the Record Group Select based on query radio button.
Enter the following query:
select * from dept; Ok
Name the Radio Groups as RG1

Select LOV , Click on create button Use LOV wizard Existing Record Group ( RG1 ) Modify existing Record Group Next Next Move all the columns from Radio Group columns to LOV columns   select return value of deptno column  , click on lookup return item Select EMPEIGHT.DEPTNO Next Title - Dept Information Next Next Move EMPEIGHT.DEPTNO to Assigned Items Finish.

In the Object Navigator, we can see that LOV is created.

As we have created LOV using wizard, LOV is assigned to DEPTNO item.

In the property palette of DEPTNO item , Under List of Values
We can see LOV41 ( our LOV ) is assigned.

Save the form.
Compile the form.
Run the form.

Keep the cursor on item DEPTNO, we can see that List of values ( hint message ) is displayed.

Keep the cursor on Item Deptno and press Ctrl+L to display LOV.

Select value from LOV, deptno is returned.

Displaying LOV through code
We can display LOV automatically using code , instead of pressing Ctrl + L
Create trigger  WHEN-NEW-ITEM-INSTANCE  on DEPTNO item, provide the following code and compile.

                a boolean;
                a := show_lov ( 'LOV41');

Save the form.
Compile the form.
Run the form.

Now, the moment you keep the cursor in DEPTNO item, LOV is displayed.

Enter the following values in the form.

Empno - 11
Deptno - 50 

We get a message " Transaction complete: 1 records applied and saved."

Note: 50 is not value present in the LOV.
So, by default you can enter any value.

We can restrict the end user to enter a value present in the LOV.
By using the property validate from list.

In the property palette of the item DEPTNO
List of values
Validate from List -- Yes

Save the form.
Compile the from.
Run the from.


Enter the following values in the form.

Empno - 2222
Deptno - 50 

We get a message  "Invalid value for the field DEPTNO"

Records Groups based on static values

Open an existing form EMP_RG.fmb
Create new Record Group Select static values radio button
Column Name

Column Values

Click on OK Button.

Name the Record Group as RG2

Creating LOV Manually
In the Object Navigator Create new LOV Select Build a new LOV manually OK
Name the LOV as LOV2

In the property palette of LOV2, provide the following details
Title -- JOB
Record Group  -- RG2
Column mapping properties
Column Names
Return Item
Display width
Column Title

Now, we need to attach LOV to the text item.
Under Data Block EMPEIGHT Select JOB In the property palette
List of Values
List of Values --  LOV2

Save the form.
Compile the form.
Run the form.

Keep the cursor on JOB and press Ctrl+L, we can see the LOV  is displayed.

Creating Record Group at runtime using code
Record Group can be created through code and can be assigned to LOV at runtime.
Memory will be saved.
LOV cannot be created at runtime.

Built-in used to create Record Groups at run time.




Used to create Record Group based on a select statement dynamically. It returns ID of the Record Group


Executes the query associated with the given record group and returns a number indicating success or failure of the query.
Upon a successful query, POPULATE_GROUP returns a 0 (zero).
An unsuccessful query generates an ORACLE error number that corresponds to the particular SELECT statement failure.


Searches the record group and returns a record group ID when it find.
You must define an appropriately typed variable to accept the return value.
Define the variable with a type of RecordGroup.


Used to remove the existing Record Group


Built-in for LOV




Used to change the properties of LOV at runtime.

Syntax: set_lov_property ( lov_name, property, value )


It is a operator. Checks the ID number is null value or not.

It if is null, it  returns TRUE

Open existing form EMP_RG.fmb
In the layout editor, create new button. In the property palette
Name -- B1
Label - Click Here

Create WHEN-BUTTON-PRESSED trigger and provide the following code and compile.

                rg_name varchar2(20) := 'sal_range';
                rg_id RecordGroup;
                errcode                number;
                a boolean;
                rg_id := find_group ( rg_name);
                if NOT id_null ( rg_id) THEN
                                delete_group ( rg_id);
                end if;
                rg_id := create_group_from_query ( rg_name , 'Select job, min(sal) lowpay , max(sal) highpay
                             from emp group by job' );

                errcode := populate_group ( rg_id);
                set_lov_property ( 'rlov' , group_name, rg_name);
                a := show_lov( 'rlov');

Now, In the above code , look at first parameter of set_lov_property  ie  rlov.
rlov is the name of the lov.

So, now we need to create LOV with the name RLOV with null values at the design time.
It holds the run runtime Record Group content.
LOV must have same structure of runtime Record Group.


In the Object Navigator,  Create new LOV  using LOV wizard New Record group based  on the query SQL Query statement -- select job, min(sal) lowpay, max(sal) highpay from emp where job is null group by job  Move all columns from Record Group columns to LOV Columns ForJOB column provide return value -- Click on Lookup return item Select EMPEIGHT.JOB Title - JOB Next Next Move Return Item ( EMPEIGHT.JOB ) to Assigned Item Next Finish.

Name the LOV as RLOV.

Save the form.
Compile the form.
Run the form.

Click on the button, the code in the trigger WHEN-BUTTON-PRESSED  is executed, at runtime Record Group is created and the LOV is displayed.


< Previous Next >