Hello Everyone,
I have create interactive report in oracle apex for use PL/SQL Function body returning sql query, but problem when i use multi SELECT_LIST_FROM_QUERY then an error show: ORA-06502: PL/SQL: numeric or value error: character string buffer too small. How to solved this issue? I have given below for this query:
IF :P14_SWITCH = 'V' THEN
RETURN q'~
SELECT
APEX_ITEM.CHECKBOX(
p_idx => 1,
p_value => TO_CHAR(PID),
p_attributes => 'class="lead_chkbox" id="' || PID || '" checked') AS PID,
APEX\_ITEM.TEXT(
p\_idx => 2,
p\_value => SUB\_NAME,
p\_attributes => 'style="color:black; width:100%; overflow:auto;"') AS SUB\_NAME,
APEX\_ITEM.SELECT\_LIST\_FROM\_QUERY(
p\_idx => 3,
-- p\_lov\_name => 'ACC\_MAIN\_GRP\_LOV',
p\_query => 'SELECT GRP\_NAME d, PID r FROM ACC\_MAIN\_GRP',
p\_null\_text => '-Select-',
p\_value => PID\_MAIN\_GRP,
p\_attributes => 'style="width:100%;"') AS PID\_MAIN\_GRP,
APEX\_ITEM.SELECT\_LIST\_FROM\_QUERY(
p\_idx => 4,
-- p\_lov\_name => 'ACC\_SUB\_GRP\_LOV',
p\_query => 'SELECT SUB\_NAME d, PID r FROM ACC\_SUB\_GRP',
p\_null\_text => '-Select-',
p\_value => PARENT\_ID,
p\_attributes => 'style="width:100%;"') AS PARENT\_ID,
APEX\_ITEM.TEXT(
p\_idx => 5,
p\_value => SL\_NO,
p\_attributes => 'style="color:black; width:100%; overflow:auto;"') AS SL\_NO,
APEX\_ITEM.SELECT\_LIST\_FROM\_QUERY(
p\_idx => 6,
p\_query => 'SELECT UNIT\_NAME d, UNIT\_DEPT\_NO r FROM UNIT\_DEPT\_TBL',
p\_null\_text => '-Select-',
p\_value => UNIT\_DEPT\_NO,
p\_attributes => 'style="width:100%;"') AS UNIT\_DEPT\_NO
FROM ACC_SUB_GRP
WHERE (:P14_SEARCH IS NULL
OR UPPER(SUB_NAME) LIKE '%' || UPPER(:P14_SEARCH) || '%')
ORDER BY PID DESC
~';
ELSE
RETURN q'~
SELECT
APEX_ITEM.CHECKBOX(
p_idx => 1,
p_value => NULL,
p_attributes => 'class="lead_chkbox" id="' || ROWNUM || '" checked') AS PID,
APEX\_ITEM.TEXT(
p\_idx => 2,
p\_value => NULL,
p\_attributes => 'style="color:black; width:100%; overflow:auto;"') AS SUB\_NAME,
APEX\_ITEM.POPUP\_FROM\_LOV(
p\_idx => 3,
p\_lov\_name => 'ACC\_MAIN\_GRP\_LOV',
p\_value => NULL,
p\_attributes => 'style="width:100%;"') AS PID\_MAIN\_GRP,
APEX\_ITEM.POPUP\_FROM\_LOV(
p\_idx => 4,
p\_lov\_name => 'ACC\_SUB\_GRP\_LOV',
p\_value => NULL,
p\_attributes => 'style="width:100%;"') AS PARENT\_ID,
APEX\_ITEM.TEXT(
p\_idx => 5,
p\_value => NULL,
p\_attributes => 'style="color:black; width:100%; overflow:auto;"') AS SL\_NO,
APEX\_ITEM.SELECT\_LIST\_FROM\_QUERY(
p\_idx => 6,
p\_query => 'SELECT UNIT\_NAME d, UNIT\_DEPT\_NO r FROM UNIT\_DEPT\_TBL',
p\_null\_text => '-Select-',
p\_value => NULL,
p\_attributes => 'style="width:100%;"') AS UNIT\_DEPT\_NO
FROM DUAL
CONNECT BY LEVEL <= 1
~';
END IF;
=======================================================================