Oracle Apex : How to execute procedure and display the results on a page

2
votes

I have a procedure that dynamically takes application id as input and displays the result as per application id.

So if :

exec get_app_data(11);

Result would be:

A   B   C

1   2   3
1   2   3

And if:

exec get_app_data(12);

D   E   F

1   2   3
1   2   3

So basically it caters the requirement of dynamically changing column headers and displaying dynamic views.

Now how do i add this procedure to oracle apex page? I understand it would be first added as a page process for execution, but how do i display the results also since the results are dynamic and at runtime?

EDIT: For further clarity this is the procedure which is being executed:

1
oraclestored-proceduresplsqloracle-apex

1 Answers

3
votes

Consider using a region of type Classic Report, where its source is based on a PL/SQL Function Body Returning SQL Query.

You would then simplify your code by removing the cursor stuff and have the parameter be based on a page item. Perhaps something like this:

declare
    l_query varchar2(4000);
begin
    SELECT 'SELECT  *' || CHR(10) ||
           '  FROM  DATA_VALUE' || CHR(10) ||
           '  PIVOT(' || CHR(10) ||
           '        MAX(VAL)' || CHR(10) ||
           '        FOR SEQ IN (' || CHR(10) ||
           '                    ' || LISTAGG(
                                             SEQ || ' "' || LABEL || '"',
                                             ',' || CHR(10) ||'                    '
                                            )
                                     WITHIN GROUP(ORDER BY SEQ) || CHR(10) ||
           '                   )' || CHR(10) ||
           '       )' || CHR(10) ||
           '  WHERE APP_ID = ' || APP_ID
      INTO l_query
      FROM DATA_HEADER
     WHERE APP_ID = :P1_APP_ID
     GROUP BY APP_ID;

    return l_query;
end;