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?

Answer 1

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