APEX

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