A select list with LOV type “SQL Query” and the following SQL statement works fine:
with
function is_authorized_yn(p_authorization_name in varchar2)
return varchar2
is
l_res boolean;
begin
l_res := apex_authorization.is_authorized(p_authorization_name);
if l_res then
return 'Y';
end if;
return 'N';
end;
select 'Public' as d
, 'PUBLIC' as r
from dual
union
select 'Authorized' as d
, 'AUTH' as r
from dual
where is_authorized_yn('Test') = 'Y'
When I then convert the source to a shared component LOV and reuse it with the same query, I get this error:
Exception in "P20_CHANNEL":
Error Stack: ORA-32034: unsupported use of WITH clause
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230200", line 801
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230200", line 785
ORA-06512: at "APEX_230200.WWV_FLOW_DYNAMIC_EXEC", line 2037
ORA-06512: at "APEX_230200.WWV_FLOW_DYNAMIC_EXEC", line 2011
Backtrace: ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230200", line 785
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230200", line 801
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230200", line 785
ORA-06512: at "APEX_230200.WWV_FLOW_DYNAMIC_EXEC", line 2037
ORA-06512: at "APEX_230200.WWV_FLOW_DYNAMIC_EXEC", line 2011
ORA-06512: at "APEX_230200.WWV_FLOW_DYNAMIC_EXEC", line 990
I am able to reproduce the bug on apex.oracle.com (currently 23.2.4).