Dynamic Report Regions Tutorial

Sometimes requirements are such that you can’t write a simple SELECT statement to return the information you want to see on a page. For example, you might want a report that displays dates in the first column, names across the top and the task each person is performing on that date as the content of the report. I’ve written a scheduling application that requires a report exactly like this, however, I don’t know the date range or the people that the end user is going to want to see information for.

In another case we have a number of tables that all have the same structure. These are code tables that are used for Select Lists throughout our Forms and APEX applications. These tables all have the same four columns: identifier, code, meaning and an in use flag. There are hundreds of these tables so I don’t want to have to write a separate page to see the data in each of these tables. Not only would this take a considerable amount of time, but I’m likely to go crazy with boredom half way through! Luckily there is an easy solution to both these problems: Dynamic Report Regions.

In this tutorial we’ll use two tables with the same columns and discover how easy it is to create a report region that queries the table name in an item on the page. Before we start you’ll need to create two tables using the following create table statements:

CREATE TABLE SOFTWARE
( ID NUMBER,
SHORT_NAME VARCHAR2(100),
DESCRIPTION VARCHAR2(1000),
IN_USE VARCHAR2(1),
CONSTRAINT "SOFTWARE_PK" PRIMARY KEY (ID) ENABLE
)
/
CREATE TABLE ROOMS
( ID NUMBER,
SHORT_NAME VARCHAR2(100),
DESCRIPTION VARCHAR2(1000),
IN_USE VARCHAR2(1),
CONSTRAINT "ROOMS_PK" PRIMARY KEY (ID) ENABLE
)
/

Now we can insert some data into these tables so that the reports have something to return. Make sure the data is noticably different. Or use these insert statements:

INSERT INTO software (id, short_name, description, in_use) VALUES ('1', 'Oracle', 'Oracle 9iR2', 'N');
INSERT INTO software (id, short_name, description, in_use) VALUES ('2', 'Oracle DB', 'Oracle 10g', 'Y');
INSERT INTO software (id, short_name, description, in_use) VALUES ('3', 'Access', 'Microsoft Access', 'Y');
INSERT INTO software (id, short_name, description, in_use) VALUES ('4', 'Windows', 'MS Windows XP', 'Y');
INSERT INTO rooms (id, short_name, description, in_use) VALUES ('1', 'Lounge', 'The main living room', 'Y');
INSERT INTO rooms (id, short_name, description, in_use) VALUES ('2', 'Bed 1', 'The Master bedroom', 'Y');
INSERT INTO rooms (id, short_name, description, in_use) VALUES ('3', 'En-suite', 'The en-suite bathroom to the master bedroom', 'Y');
INSERT INTO rooms (id, short_name, description, in_use) VALUES ('4', 'Study', 'The home office', 'Y');

In the Application Builder:

  1. Create a new blank page within your application.
  2. On this page add a new Report Region, select SQL Report and set the Title to “Table Data” and click Next.
  3. In the section for the SQL Query, enter the following:
  4. DECLARE
    v_query varchar2(1000);
    BEGIN
    IF :P2_TABLE_NAME is not null THEN
    v_query := 'SELECT id, short_name, description, in_use FROM '||
    :P2_TABLE_NAME;
    ELSE
    v_query := 'SELECT 1 FROM dual WHERE 1=0';
    END IF;
    return(v_query);
    END;

  5. Ensure the Generic Columns radio is selected and click Create Region to create this region.
  6. Create a new Text Item in the Report Region callled “Pn_TABLE_NAME” where n is your page number.
  7. Create a new unconditional branch on the page, branching to your page so that when the page is submitted you are not taken to a different page.
  8. Finally, create a button on your page without its own branch.

Run the page and enter “rooms” in the text field and click the button. The page will refresh showing the data from the ROOMS table. Now enter “software” in the text field and click the button; the data from the SOFTWARE table is displayed.

This functionality can be built upon in many ways. You could change the Text Item to be a Select List of tables from all_tables and build the select statement from the information in all_tab_columns using the code below:

DECLARE
 v_query varchar2(1000);
 cursor c_columns is
 SELECT column_name
 FROM   all_tab_columns
 WHERE  table_name = upper(:P3_TABLE_NAME);
BEGIN
 IF :P3_TABLE_NAME is not null THEN
  FOR a IN c_columns LOOP
   IF v_query is null THEN
    v_query := 'SELECT '||a.column_name;
   ELSE
    v_query := v_query||', '||a.column_name;
   END IF;
  END LOOP;
  v_query := v_query||' FROM '||:P3_TABLE_NAME;
 ELSE
  v_query := 'SELECT 1 FROM dual WHERE 1=0';
 END IF;
 return(v_query);
END;

Building a select statement with a function opens up various opportunities in APEX for you to build a dynamic application that is not only easier to use but also saves a great deal of development time.

Sara

Sara Blair is a freelance Oracle developer with over 10 years Oracle experience. She has been working with Oracle APEX for nearly 4 years since htmldb 1.5, and is available to hire through Silvercore Solutions.

2 Responses to “Dynamic Report Regions Tutorial”

  • David Mann says:

    I have done a lot of web development and a lot of PL/SQL – now that I am developing in Apex I am getting used to doing both in the same workspace :)

    Thanks for explaining this very useful technique, I know I will be able to apply it in my applications.

    — Dave

  • kumar rajesh ranjan says:

    this is a good concept to generate the interactive report. through this we can create the any table report.

Comments can no longer be submitted.