Miscellaneous - Google - Visualization Orgchart

Skip to Main Content

An implementation of the Orgchart functionality of the Google Visualization javascript API.

More info on the API: https://developers.google.com/chart/interactive/docs/reference.

Page

Identification
	Page ID	1027
	Name	Miscellaneous - Google - Visualization Orgchart

Region

Identification
	Sequence	20
	Title	Orgchart
	Type	PL/SQL
Source
Source	Region Source	begin htp.p(ted_google_visual_orgchart.orgchart_test); end;
Attributes
	Static ID	the_div
	Region Attributes	style="overflow-x: scroll;"

Package

Package
	Name	TED_GOOGLE_VISUAL_ORGCHART
	Source	package ted_google_visual_orgchart is type t_orgchart_record is record ( name varchar2(4000) , manager varchar2(4000) , tooltip varchar2(4000) ) ; type t_orgchart_table is table of t_orgchart_record; function orgchart_test return clob ; end ted_google_visual_orgchart;

Package Body

Package Body
	Name	TED_GOOGLE_VISUAL_ORGCHART
	Source	package body ted_google_visual_orgchart is function orgchart ( p_rows in t_orgchart_table , p_html_element_id in varchar2 ) return clob is v_lf varchar2(2) := chr(10); v_c1_count pls_integer := 0; v_name varchar2(4000); v_result clob; begin dbms_lob.createtemporary(v_result , true, dbms_lob.call); -- init v_result dbms_lob.append(v_result, v_lf); dbms_lob.append(v_result, ' <script type="text/javascript" src="https://www.google.com/jsapi"></script>' \|\| v_lf); dbms_lob.append(v_result, ' <script type="text/javascript">' \|\| v_lf); dbms_lob.append(v_result, ' google.load("visualization", "1", {packages:["orgchart"]});' \|\| v_lf); dbms_lob.append(v_result, ' google.setOnLoadCallback(drawChart);' \|\| v_lf); dbms_lob.append(v_result, ' function drawChart() {' \|\| v_lf); dbms_lob.append(v_result, ' var data = new google.visualization.DataTable();' \|\| v_lf); dbms_lob.append(v_result, ' data.addColumn("string", "Name");' \|\| v_lf); dbms_lob.append(v_result, ' data.addColumn("string", "Manager");' \|\| v_lf); dbms_lob.append(v_result, ' data.addColumn("string", "ToolTip");' \|\| v_lf); -- open rows dbms_lob.append(v_result, ' data.addRows([' \|\| v_lf); if p_rows.count > 0 then for i in p_rows.first .. p_rows.last loop v_c1_count := v_c1_count + 1; -- build v_name v_name := p_rows(i).name; if v_name not like '{v%}' then v_name := '"' \|\| v_name \|\| '"'; end if; -- build row dbms_lob.append(v_result, ' '); if v_c1_count > 1 then dbms_lob.append(v_result, ', '); else dbms_lob.append(v_result, ' '); end if; dbms_lob.append ( v_result , '[' \|\| v_name \|\| ', "' \|\| p_rows(i).manager \|\| '"' \|\| ', "' \|\| p_rows(i).tooltip \|\| '"' \|\| ']' \|\| v_lf ) ; end loop; end if; -- close rows dbms_lob.append(v_result, ' ]);' \|\| v_lf); -- close v_result dbms_lob.append(v_result, ' var chart = new google.visualization.OrgChart(document.getElementById("' \|\| p_html_element_id \|\| '"));' \|\| v_lf); dbms_lob.append(v_result, ' chart.draw(data, {allowHtml:true});' \|\| v_lf); dbms_lob.append(v_result, ' }' \|\| v_lf); dbms_lob.append(v_result, ' </script>' \|\| v_lf); -- only return a result if there is anything to return if v_c1_count > 0 then return v_result; else return null; end if; end orgchart; function orgchart_test return clob is v_result clob; v_rows t_orgchart_table := t_orgchart_table(); v_rows_n pls_integer := 0; cursor c1 is select a.entry_text as name , a.parent_entry_text as manager , a.entry_text as tooltip from apex_application_list_entries a where a.application_id = v('APP_ID') and a.list_name = 'Navigation Menu' ; begin dbms_lob.createtemporary(v_result , true, dbms_lob.call); for c1_rec in c1 loop v_rows_n := v_rows_n + 1; v_rows.extend; v_rows(v_rows_n).name := c1_rec.name; v_rows(v_rows_n).manager := c1_rec.manager; v_rows(v_rows_n).tooltip := c1_rec.tooltip; end loop; v_result := orgchart ( p_rows => v_rows , p_html_element_id => 'the_div' ) ; return v_result; end orgchart_test; end ted_google_visual_orgchart;