Hi,
I use aa AnyGantt chart with custom xml. Until now I referenced the custom xml with a text area item and everything worked fine. Now the size of 32k (32767 byte) was reached and I got a nice error: ORA-06502: PL/SQL: numeric or value error
The problem is described in several forum posts. Most common way is to use the htp.p function to generate the data.
My problem: I need the apex element to reference it inside my "Chart Attributes" > "Custom XML" part: &P1_CUSTOM_XML_CODE.
To understand how the referencing works take a look into this example: http://apex.oracle.com/pls/apex/f?p=36648:60
My workaround is to create several APEX text area elements and fill them through a before header process. They are referenced like that inside my "Custom XML" part:
&P1_CODE1.&P1_CODE2.&P1_CODE3.&P1_CODE4.&P1_CODE5.&P1_CODE6.&P1_CODE7.&P1_CODE8.&P1_CODE9.&P1_CODE10.
Before Header process:
DECLARE
P_USER VARCHAR2(32767);
P_CLOB CLOB;
P_SET VARCHAR2(32767);
P_LENGTH NUMBER;
P_LOOP NUMBER := 1;
P_POS NUMBER := 1;
P_CLOB_BUFFER NUMBER := 32000;
BEGIN
P_USER := :APP_USER;
/* Reset APEX Items */
:P1_CODE1 := '';
:P1_CODE2 := '';
:P1_CODE3 := '';
:P1_CODE4 := '';
:P1_CODE5 := '';
:P1_CODE6 := '';
:P1_CODE7 := '';
:P1_CODE8 := '';
:P1_CODE9 := '';
:P1_CODE10 := '';
/* CLOB schreiben */
P_CLOB := MY_PACKAGE.F_APEX_GANTT_COMPLETE ( P_USER );
dbms_lob.open(P_CLOB, dbms_lob.lob_readonly);
P_LENGTH := dbms_lob.getlength (P_CLOB);
while P_LENGTH > 0 loop
/* CLOB auslesen */
dbms_lob.read(P_CLOB, P_CLOB_BUFFER, P_POS, P_SET);
/* In APEX Variablen schreiben */
CASE
WHEN P_LOOP = 1 THEN :P1_CODE1 := P_SET;
WHEN P_LOOP = 2 THEN :P1_CODE2 := P_SET;
WHEN P_LOOP = 3 THEN :P1_CODE3 := P_SET;
WHEN P_LOOP = 4 THEN :P1_CODE4 := P_SET;
WHEN P_LOOP = 5 THEN :P1_CODE5 := P_SET;
WHEN P_LOOP = 6 THEN :P1_CODE6 := P_SET;
WHEN P_LOOP = 7 THEN :P1_CODE7 := P_SET;
WHEN P_LOOP = 8 THEN :P1_CODE8 := P_SET;
WHEN P_LOOP = 9 THEN :P1_CODE9 := P_SET;
WHEN P_LOOP = 10 THEN :P1_CODE10 := P_SET;
END CASE;
/* Zaehler erhoehen */
P_POS := P_POS + P_CLOB_BUFFER;
P_LOOP := P_LOOP + 1;
/* Groesse der CLOB Laenge verkleinern */
P_LENGTH := P_LENGTH - P_CLOB_BUFFER;
end loop;
dbms_lob.close(P_CLOB);
END;
I wonder if there is another solution available? Maybe the APEX item referencing inside my chart attributes isn't the best way to create custom xml code via a package function?
Best regards,
Tobias