Oracle APEX selected components export from API (APEX_EXPORT)

 Oracle APEX 20.1 introduced new functionality for application components export. Function APEX_EXPORT.get_application got a new parameter named P_COMPONENTS.

So, with this parameter it is possible to make a list of components for which a scripts will be returned. No need to export and split full application.

But beware!

P_SPLIT parameter (boolean value) is still functional and it has a great influence on function result!

If value of P_SPLIT parameter is FALSE, function will return one script for all components from P_COMPONENTS parameter.

If value is TRUE it will return multiple scripts. 

For every component 2 scripts will be created:

• delete component script
• create component script

Plus, additional scripts will be generated:

• set environment (as first script in collection)
• end environment (as second last script in collection)
• main install script which executes all generated scripts (as last script in collection)

So, do not make a mistake like me - to set a P_SPLIT parameter to TRUE and expect one script per component to be generated (for example 3 scripts for 3 pages). There are only two scenarios - one script for everything OR multiple scripts (environment, delete components, create components, finish install, main install script).

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

;

/