AnyChart gantt with custom xml and more then 32k

Tobias Arnhold

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

Tobias Arnhold

Damn this way also runs into the same 32k issue!

ThomasL

You should try to put the clob and the loop in a database stored procedure, and give back the data to apex in chunks of 32K (or 4K ?).

Maybe it'll work with a stored procedure with 10 out parameters for all your items, or try a pipelined table function.

Tobias Arnhold

Hi Thomas,

it wont work.

The problem is the "custom xml" field it has a maximum limit of 32k. I could try to call my package function through a customized url inside the chart definition:

param name="movie" value="#IMAGE_PREFIX#flashchart/anygantt_4/swf/AnyGantt.swf?XMLFile=#HOST#my_function"

Tobias Arnhold

I found a workaround where I don't know if it is licensed by Oracle APEX.
AnyChart provides a text areas solution: http://www.anychart.com/products/anygantt/docs/users-guide/SetXMLAsString.html#textarea

Which I implemented into my page through a pl/sql region:
HTML Header:

<script src="#APP_IMAGES#anychart_intkit.js" language="javascript"></script>

PL/SQL Region:

...
  htp.p('<textarea cols="65" rows="17" id="rowData" style="display:none;">');

  /* 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 */
     htp.p(P_SET);
     
     /* 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); 

  htp.p('</textarea>');
  htp.p('<div id="chartDiv"></div>');

  htp.p(
    '<script type="text/javascript" language="javascript">' || chr(10) ||
    '/* Set default swf path */' || chr(10) ||
     'AnyChart.swfFile = ''/i/flashchart/anygantt_4/swf/AnyGantt.swf'';' || chr(10) ||
    '/* Create new gantt chart */' || chr(10) ||
     'var chart = new AnyChart();' || chr(10) ||
     'chart.width="2500";' || chr(10) ||
     'chart.height="1400";' || chr(10) ||
    '/* Get string data from text area */' || chr(10) ||
     'var data = document.getElementById(''rowData'').value.toString();' || chr(10) ||
    '/* Set data */' || chr(10) ||
     'chart.setData(data);' || chr(10) ||
    '/* Write chart to "chart" div */' || chr(10) ||
     'chart.write("chartDiv");' || chr(10) ||
    '</script>');
...

This solution goes far away from the standard APEX way. My next step would be to create a APEX plug-in but still wondering if this solutions is allowed?

Tobias

Tobias Arnhold

After I finished the plug-in and tested a bit around I got a new error:

"uncaught exception: Error in Actionscript. Use a try/catch block to find error."

The 32k problem is solved but one out of three times I get the uncaught exception error and the chart will not load. I created a ticket on the AnyChart support site. Maybe on of you had similar issues in combination with Anychart?

Tobias

Tobias Arnhold

After some more testing I found the "uncaught exception"! Instead of htp.p during the XML generation I need htp.prn:

  while P_LENGTH > 0 loop
  
     /* CLOB auslesen */
     dbms_lob.read(P_CLOB, P_CLOB_BUFFER, P_POS, P_SET); 
          
     /* In APEX Variablen schreiben */
     htp.prn(P_SET);
     
     /* 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;

That is it. My Gantt works perfect with content over 32k.

Kirsten

Hello Tobias,

I tried to set data to a resource gantt chart with help of your code. But it doesn't work. What is wrong?

- I created a page of type Chart => Resource Gantt
- I wrote a function apex_pkg.Get_Data_For_Gantt which returns the data as clob
- I created a html-region with region source:

******************************************************
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;
htp.p('<textarea cols="65" rows="17" id="rowData" style="">');

/* CLOB schreiben */
P_CLOB := apex_pkg.Get_Data_For_Gantt(...);
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 */
htp.prn(P_SET);

/* 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);
htp.prn('</textarea>');

******************************************************
When I run the report the xml I get can be seen in the html-region. The code is correct and if I set it to a page-item (P_1_DATA) and I include this page-item in the xml of the chart by replacing #DATA# by &P1_DATA it works.

But now I want to use the function setdata by adding the second part of your code to the html-region. And this doesn't work.
Do I need the part "var chart = new AnyChart()"?
In my Chart Region there already exists a chart because the type of my page is "Chart".



Kirsten

887666

Can you please tell me Where do I get anychart_iniki.js . I get the xml data in text area but the graph doesn't get drawn on screen . I feel it has something to do with javascript import . I tried to import AnyChart.js and AnyGantt.js . I downloaded it but it doesn't get installed . Please advice.

BrianBx

Hello Tobias,

I know it is a long time since you posted this thread but I am trying to follow it and adapt it for an Anychart Map but if I could get yours working it would be a great help.

Could you write down the steps needed and any special names we have to give to items or regions in order to recreate and use this code please.

If I can create a chart and populate it with some dummy data then I'm sure I can adapt it for a map.

Thus far, I have created two regions
region 1 for the PLSQL and
region 2 a Chart region called ChartDiv , however I do not kinow if I have even got that right.

Could you put the steps down please.

BB