Use SELECT statement with inline PLSQL in APEX

If someone wants to use a SELECT statement which contains inline PLSQL as region source in APEX it will not work out-of-the-box.

Example of such a statement is:

WITH

    FUNCTION f_2x(p_number number) RETURN number IS

    BEGIN

        RETURN p_number * 2;

    END;

SELECT 

    level as c_level,

    f_2x(level) as c_x2

FROM dual

CONNECT BY level < 10

;

APEX validates it as a valid statement BUT when page is run it returns an error "Unsupported use of WITH clause":

There are 2 possible solutions:

1. to create a view and then use it in region source (preferably)
2. to use a WITH_PLSQL hint

Hint should be set as region property "Optimizer hint":

but once set... SELECT statement can not be validate any more!

Maybe this will be solved once in future versions of APEX... but until then I suggest to create and use database view for such a scenarios.

Database view creation example:

CREATE OR REPLACE VIEW v_tica_test AS

WITH

    FUNCTION f_2x(p_number number) RETURN number IS

    BEGIN

        RETURN p_number * 2;

    END;

SELECT 

    level as c_level,

    f_2x(level) as c_x2

FROM dual

CONNECT BY level < 10

;

/

or with outside wrapper (example of HINT usage):

CREATE OR REPLACE VIEW v_example AS

SELECT /*+ WITH_PLSQL */

    v.*

FROM 

    (

    WITH

        FUNCTION f_2x(p_number number) RETURN number IS

        BEGIN

            RETURN p_number * 2;

        END;

    SELECT 

        level,

        f_2x(level) as x2_value

    FROM dual

    CONNECT BY level < 10

    ) v

;

/