Example with PL/SQL

A PL/SQL package function called xxx_leave_balance_pkg.leave_balance_chart returns the XML that populates this chart. The PLSQL package body is displayed below:

/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

CREATE OR REPLACE PACKAGE BODY xxx_leave_balance_pkg  AS 

--
-- Private Global Types and Constants
--
g_PACKAGE_NAME CONSTANT varchar2(30) := 'xxx_leave_balance_pkg';
  
--
-- ------------------------------------------------------------------------ *
-- Name    : leave_balance_chart
-- ------------------------------------------------------------------------ *
FUNCTION leave_balance_chart
  (p_keys IN xxas_com_keys_type) RETURN XMLType
IS

  l_xml              XMLType;
  l_hol_taken        number;
  l_remaining        number;
  l_adjustments      number;
  l_used_text        varchar2(4000);
  l_remaining_text   varchar2(4000);
  l_adjustments_text varchar2(4000);
  
BEGIN

  --
  -- Retrieve the leave balance details
  --
  xxx_some_package.some_procedure
    (p_person_id        => p_keys.get_key('PERSON_ID')
    ,p_effective_date   => trunc(sysdate)
    ,p_hol_taken        => l_hol_taken
    ,p_remaining        => l_remaining
    ,p_adjustments      => l_adjustments
    ,p_used_text        => l_used_text
    ,p_remaining_text   => l_remaining_text
    ,p_adjustments_text => l_adjustments_text);

  SELECT XMLElement 
           ("chart" 
           ,XMLElement 
             ("type" 
             ,'PieChart') 
           ,XMLElement 
             ("data" 
             ,XMLElement 
               ("cols" 
               ,XMLAttributes 
                  ('true' as "json:force-array" 
                  ,'http://json.org/' as "xmlns:json") 
               ,XMLElement 
                 ("column" 
                 ,XMLForest 
                   ('Element' "label" 
                   ,'string' "type") 
                 ) 
               ,XMLElement 
                 ("column" 
                 ,XMLForest 
                   ('Absence' "label" 
                   ,'number' "type") 
                 ) 
                 ,XMLElement 
                 ("column" 
                 ,XMLForest 
                   ('style' "role" 
                   ,'string' "type") 
                 ) 
                ,XMLElement 
                 ("column" 
                 ,XMLForest 
                  ('tooltip' "role" 
                  ,'string' "type") 
                  ,XMLElement("p", XMLElement("Html", 'true'))     
                  ) 
               )
             ,XMLElement 
               ("rows" 
               ,XMLAttributes 
                 ('true' as "json:force-array" 
                 ,'http://json.org/' as "xmlns:json") 
                ,XMLElement 
                 ("row" 
                 ,XMLElement 
                   ("c" 
                   ,XMLAttributes 
                     ('true' as "json:force-array" 
                     ,'http://json.org/' as "xmlns:json") 
                   ,XMLElement 
                     ("value" 
                     ,xmlelement 
                       ("v", l_used_text) 
                     ) 
                   ,XMLElement 
                     ("value" 
                     ,XMLElement 
                       ("v", l_hol_taken) 
                     ) 
                   ) 
                 ) 
                ,XMLElement 
                 ("row" 
                 ,XMLElement 
                   ("c" 
                   ,XMLAttributes 
                     ('true' as "json:force-array" 
                     ,'http://json.org/' as "xmlns:json") 
                   ,XMLElement 
                     ("value" 
                     ,xmlelement 
                       ("v", l_adjustments_text) 
                     ) 
                   ,XMLElement 
                     ("value" 
                     ,XMLElement 
                       ("v", l_adjustments) 
                     ) 
                   ) 
                 ) 
               ,XMLElement 
                 ("row" 
                 ,XMLElement 
                   ("c" 
                   ,XMLAttributes 
                     ('true' as "json:force-array" 
                     ,'http://json.org/' as "xmlns:json") 
                   ,XMLElement 
                     ("value" 
                     ,xmlelement 
                       ("v", l_remaining_text) 
                     ) 
                   ,XMLElement 
                     ("value" 
                     ,XMLElement 
                       ("v", l_remaining) 
                     ) 
                   ) 
                 )               
              ) 
            ) 
         ,XMLElement 
           ("options" 
           ,XMLElement 
             ("colors" 
             ,XMLAttributes 
               ('http://json.org/' as "xmlns:json" 
               ,'true' as "json:force-array") 
             ,XMLElement("color", '#808080') 
             ,XMLElement("color", '#ffa500') 
             ,XMLElement("color", '#008000') 
             ) 
           ,XMLForest 
             ( 
             '0.4' "pieHole" 
             ,'value' "pieSliceText" 
             )
           ,XMLElement 
             ("chartArea" 
               ,XMLElement("width", '88%')  
               ,XMLElement("height", '75%')  
             )
           ,XMLElement 
             ("legend" 
             ,xmlelement("textStyle" 
              ,XMLElement("showColorCode", 'true')) 
             ) 
           ,XMLElement 
             ("tooltip" 
             ,XMLElement("trigger",'none') 
             ,XMLElement("isHtml",'true') 
             ,XMLElement("text", 'value') 
             ,XMLElement
                ("textStyle" 
                ,XMLElement("showColorCode", 'true') 
                 ) 
              ) 
           ) 
         ) "data" 
  INTO l_xml 
  FROM   dual;
  
  RETURN l_xml;

END leave_balance_chart;

END xxx_leave_balance_pkg;
/
COMMIT;
EXIT;

This package can be called as follows:

SELECT xxas_absence_pkg.absence_chart
         (xxas_com_keys_type
           (xxas_com_named_value_table
             (xxas_com_named_value_type('PERSON_ID', :person_id)))) chart_xml
FROM  dual;

Here you simulate creating the same keys object that the page creates automatically. This is then passed into the PL/SQL function, which returns an XMLType object. In this example, you can pass in a test Person Id through the: person_id bind variable.