Forum Stats

  • 3,769,286 Users
  • 2,252,943 Discussions


Run PL/SQL prior to refresh region

Scott Wesley
Scott Wesley Member Posts: 6,127 Gold Crown

Similar to this question, I was looking for a way to conditionally change the date format for only a particular region, for instance using

 DBMS_SESSION.SET_NLS('nls_date_format', '"dd-MON-yyyy hh24:mi"');

The purpose was to enable searching by the time component of a column value. Ideally without changing the date format of the entire application.

The date format would need modification as the inherent filtering using the IR uses INSTR, which does an implicit character conversion. I don't want to to_char my column because that destroys sorting. And the format mask applies after this point.

instr( upper("MY_DATE"),upper(:apex$f1)) > 0 

I thought for a moment this may be my first use-case for the 'before refresh' dynamic action, but that doesn't behave like a Forms pre-query trigger, rather just a browser event prior to the AJAX call to do the partial page refresh.

The only other location to run PL/SQL prior to a region refresh is the application's Initialisation PL/SQL code, but that would need to be for any request on that page, unless I knew of a safe way to resolve the request value the PPR, which is


Are there some other considerations I've missed?




  • Oleh Tyshchenko
    Oleh Tyshchenko Member Posts: 711 Gold Trophy

    Maybe try to wrap your region's query into a pipelined function

  • Scott Wesley
    Scott Wesley Member Posts: 6,127 Gold Crown

    Interesting thought, but it returns

    cannot perform a DDL, commit or rollback inside a query or DML

    The following was used as the test case

    CREATE TYPE apx_test_row AS OBJECT (
     key varchar2(256),
     value VARCHAR2(2048),
     dt date
    CREATE TYPE apx_test_tab IS TABLE OF apx_test_row;
    create or replace function apx_test RETURN apx_test_tab PIPELINED as
     DBMS_SESSION.SET_NLS('nls_date_format', '"dd-MON-yyyy hh24:mi"');
     PIPE ROW(apx_test_row('A', 'row 1', trunc(sysdate)));
     PIPE ROW(apx_test_row('B', 'row 2', trunc(sysdate)-1/86400));
     PIPE ROW(apx_test_row('C', 'row 3', trunc(sysdate)-2/86400));
    -- query used in IR
    select * from table(apx_test);

  • Oleh Tyshchenko
    Oleh Tyshchenko Member Posts: 711 Gold Trophy

    Yes, the approach with pipelined function doesn't work the way you want it, even when I move DBMS_SESSION.SET_NLS to an autonomous transaction.

    Another idea, how about basing your IR on a Function Body returning SQL Query?