Create Dynamic Oracle Reports (Change Tables at Runtime)

Dear Friends,
                      If you want to create dynamic oracle reports then its very easy using record group and assigning them to reports from oracle forms. The steps are as follows:-

We will take an example here, consider a reports containing three fields as shown in query:-
Step 1:-
select emp_name, desig,dob from company.emp_mst;

Now you can see you have selected three columns from company.emp_mst in Q_EMP (Queryname in report).
Consider you want the same reports from a different table named contractor.user_mst.
Then what all you have to do is leave the report as it is and come to forms (from which you are calling this report.)

Step 2:-
In forms take two radio buttons as:-

Now while calling the report :-

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

declare

rg recordgroup;

gc1 groupcolumn;

gc2 groupcolumn;

gc3 groupcolumn;

q1 varchar2(500);

j number;

begin

---------- Change queries based on user input ----------------------

if :radio_btn='1' then

q1:='select emp_name,desig,dob from company.emp_mst';

else

q1:='select emp_name,desig,dob from contractor.user_mst';

end if;

rg := Find_Group( 'rng' ); 

  IF NOT Id_Null(rg) THEN 

    Delete_Group( rg ); 

  END IF; 

-------------- create columns ----------------------------------

rg := create_group('rng');

gc1 := add_group_column(rg,'EMP_NAME',char_column,50);

gc2 := add_group_column(rg,'DESIG',char_column,30);

gc3 := add_group_column(rg,'DOB',date_column);

--------------- Populate record group-----------------------

 j := Populate_Group_With_Query( rg,q1);

/* And now after creating paramlist and adding parameters we add another line 
for sending record group as data parameter */

Add_Parameter(pl_id, 'Q_EMP', DATA_PARAMETER, 'rng');

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

Q_EMP is name of the query you have created in report and rng is the name of record group created here in forms.

Now compile and run the report.

Note:- The datatype and number of columns should be same as in report and record groups.