Oracle APEX use button to change report source sql

Question

On the report page of my Oracle APEX application, the report source is defined by sql with

SELECT VALUE1, VALUE2, VALUE3 ... FROM TABLE1

When the user clicks a button, for example, button 'Sort', I would like to add JOIN, WHERE and ORDER BY clauses to the report source sql.

The new report source after the button click event should be

SELECT VALUE1, VALUE2, VALUE3 ... FROM TABLE1 
JOIN TABLE2 WHERE ID='123' ORDER BY VALUE2 ASC

How do you achieve this?

Why do you need separate button to do the filtering, sorting? It can be achieved by Dynamic report itself. — learningloop
This is part of our assignment. We are required to implement separate sort or filtering, such as sort by nearest distance first, which is not covered in the Dynamic report by default. — Andrew

hinotf hinotf · Accepted Answer · 2016-12-22T08:22:20

If you need only change filtering, sorting, visible column list and so on use Interactive Report feature. If you really want change query text choose "PL/SQL Function Returning SQL Query" as the report implementation type and create some code

DECLARE
  l_col_list  VARCHAR2(4000);
  l_join      VARCHAR2(4000);
  l_where     VARCHAR2(4000);
BEGIN
  l_col_List := ....;
  l_join := ....;
  l_where := ....;
  RETURN 'SELECT ' || l_col_list || ' FROM ' || l_join || ' WHERE ' || l_where;
END;

Example page: apex.oracle.com/pls/apex/f?p=54028:5

Oracle APEX use button to change report source sql

1 Answers