Forum Stats

  • 3,817,369 Users
  • 2,259,322 Discussions


Oracle Apex How to use Function returning sql query in IG

Vinipanda Member Posts: 106 Red Ribbon
edited May 26, 2021 7:37AM in SQL & PL/SQL


I had asked a question earlier in one of the threads.

The details are in thread here.

Requirement was to write query for interactive grid that would return previous quarter data if current quarter was unavailable.

To resolve i created function in database returning sql which implements same logic. But i am not sure how to use it in apex application.

My current IG query is:

  from DEPT_DATA

I need to modifiy this to use the function mentioned below retuning the sql .How can i do that?

As mentioned in thread, for project variable p_id, there is application item :PROJECT.

For year and quarter, i have app items A_YEAR AND A_QUARTER.

So function would be something like:

Q_VALUE(:A_YEAR,A_QUARTER, :PROJECT, :P1_DEPT --P1_DEPT is page item for departments

But how do i use this as source of IG?



  • BEDE
    BEDE Oracle Developer Member Posts: 2,405 Gold Trophy

    Do you really need that function?

    What if you change the query the following way:

    )with qtr as (
      from DEPT_DATA
    from qtr
    union all
      from DEPT_DATA
        not exists (select 1 from qtr)  

  • Vinipanda
    Vinipanda Member Posts: 106 Red Ribbon

    Thanks, in case i need to add more logic like:

    The quarter id's are such that 1 is Q1,2 is Q2, 3 is Q3 and 4 is Q4.

    So if i need to add logic like, if the quarter is first quarter of year, ex: Q1, 2021. Then it should fetch data from Q4 2020.

    How can this also be included in this query?


        V_YEAR :=P_YEAR-1;

        V_QUARTER :=4;

    I have added this to the function for better understanding.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,896 Red Diamond

    Analytics functions in SQL would be able to give you values from preceding "rows" under certain conditions if that's what you need.

    Using function code, especially dynamically generated SQL, is going to be less performant, difficult to debug and maintain and is open to SQL injection.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,807 Red Diamond

    In APEX, bind variables are MANDATORY in order to guard against SQL injection.

    The PL/SQL function should return a dynamic source SQL statement, with a static SQL projection, and variable predicates using page item bind variables.

    Simplistic SQL function block in APEX for adding not null page items to the SQL query's where clause:

     reportSQL varchar2(4000) := 'select t.* from ip_traffic t &FILTER';
     filter    varchar2(1000);
     filter := 'where 1=1';
     if :P19_PROTOCOL is not null then
       filter := filter || ' and t.protocol = :P19_PROTOCOL';
     end if;
     if :P19_VOLUME is not null then
       if to_number(:P19_VOLUME) > 0 then
        filter := filter || ' and t.bytes >= :P19_VOLUME';
        filter := filter || ' and t.bytes <= abs(:P19_VOLUME)';
       end if;
     end if;
     if :P19_IP_FILTER is not null then
       filter := filter || ' and t.source_ip like replace(:P19_IP_FILTER,''*'',''%'')';
     end if;
     reportSQL := replace( reportSQL, '&FILTER', filter );
     return( reportSQL );