Using Apex 21.2 and oracledb 21c xe , an IG region takes as source a 'function body returning SQL Query' using the q notation.
The code looks like
DECLARE
l_sql VARCHAR2(4000);
l_search VARCHAR2(400) DEFAULT '305,306' ;
BEGIN
--l_search := ''||:P153_GRP_LIST ||'';
l_search := '78,79' ;
--l_search := :P153_GRP ;
--PRINT2( 'l_search: : '||l_search ) ;
l_sql := q'~
select ROWID,
MOT_TOPGRP_ID,
GRUPPE,
ART_BESTAND
from MOT_TOPGRP
WHERE 1=1
AND GRUPPE IN ( ~' || l_search || q'~ )
~';
--IF ( 1 = 1 ) THEN
--l_sql := l_sql || q'~ AND GRUPPE IN ( ~' || :P153_GRP || q'~ ) ORDER BY 1 ~' ;
--END IF;
RETURN ( l_sql ) ;
END;
P153_GRP_LIST is a text-item on the page containing e.g. 407,408
means a list of GRUPPE numbers separated with comma in preparation for the IN ( ... ) part.
If l_search as a predefined string is used in the AND GRUPPE IN ( ... ) part it works,
but I did not succeed using the :P153_GRP_LIST for the enumeration part, the code gives a parsing SQL error
How would the correct syntax be when using :P153_GRP_LIST ?