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:
- to create a view and then use it in region source (preferably)
- to use a WITH_PLSQL hint
Hint should be set as region property "Optimizer hint":
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
;
/
Zoran,
ReplyDeleteI've checked. And it appears that you *can* use such a query directly as a region source without the need for a view (though I personally think a view is better anyway).
The problem is with what is mentioned on slide 11 of the presentation I gave: a query that had plsql defined in the with clause must not be terminated with a semicolon, but with a slash (in client tools like SQL*Plus/SQLcl/SQL developer etc)
Now, terminating the query in APEX with a slash leads to error messages again.
But it works for me after entering the with_plsql hint in the optimizer hint field (Good catch!!!) and then using the query you mention here, but entered **without** a sql terminator (no semicolon, no slash), So just:
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
Erik,
ReplyDeleteI made an experiment and got following results: semicolon at the end of SELECT statement CAN NOT be used in combination with hint BUT IT CAN if hint is not used!
in fact, without hint it doesn't matter if there is semicolon or not at the end of SELECT statement - in both cases SELECT statement is verified as valid.