Oracle CLOB performance
before :
we use null and assign string value to CLOB object directly. but it will enter the performance issue to print the content in the loop:
v_clob_xml_body CLOB := NULL;
v_clob_xml_body := '123344444...............'
Performance tunning
after :
v_clob_xml_body CLOB := empty_clob();
dbms_lob.createtemporary(v_clob_xml_body, true, 1024); --Initialize the clob
-- Open workbook
dbms_lob.append(v_clob_xml_body,
'<?xml version="1.0" encoding="ISO-8859-9"?>' || chr(10) ||
'<?mso-application progid="Excel.Sheet"?>'|| chr(10) ||
'<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"'||chr(10) ||
' xmlns:o="urn:schemas-microsoft-com:office:office"' || chr(10) ||
' xmlns:x="urn:schemas-microsoft-com:office:excel"' || chr(10) ||
' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"' || chr(10) ||
' xmlns:html="http://www.w3.org/TR/REC-html40">' || chr(10)
);
-- Set document properties
dbms_lob.append(v_clob_xml_body,
' <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">' || chr(10) ||
' <Author>Sunil Sahasrabudhe</Author>' || chr(10) ||
' <LastAuthor>Emerson Process Management</LastAuthor>' || chr(10) ||
' <Created>'||TO_CHAR(SYSDATE, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')||'</Created>' || chr(10) ||
' <LastSaved>'||TO_CHAR(SYSDATE, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')||'</LastSaved>' || chr(10) ||
' <Company>Emerson Process Management</Company>' || chr(10) ||
' <Version>14.00</Version>' || chr(10) ||
'</DocumentProperties>' || chr(10)
);
-- Set office document settings
dbms_lob.append(v_clob_xml_body,
' <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">' || chr(10) ||
' <AllowPNG/>' || chr(10) ||
' </OfficeDocumentSettings>' || chr(10)
);
-- Start excel workbook
dbms_lob.append(v_clob_xml_body,
' <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">' || chr(10) ||
' <WindowHeight>8445</WindowHeight>' || chr(10) ||
' <WindowWidth>15030</WindowWidth>' || chr(10) ||
' <WindowTopX>120</WindowTopX>' || chr(10) ||
' <WindowTopY>45</WindowTopY>' || chr(10) ||
' <ProtectStructure>False</ProtectStructure>' || chr(10) ||
' <ProtectWindows>False</ProtectWindows>' || chr(10) ||
' </ExcelWorkbook>' || chr(10)
);
-- Define styles
dbms_lob.append(v_clob_xml_body,
' <Styles>' || chr(10) ||
' <Style ss:ID="Default" ss:Name="Normal">' || chr(10) ||
' <Alignment ss:Vertical="Bottom"/>' || chr(10) ||
' <Borders/>' || chr(10) ||
' <Font ss:FontName="Arial" ss:Size="8"/>' || chr(10) ||
' <Interior/>' || chr(10) ||
' <NumberFormat/>' || chr(10) ||
' <Protection/>' || chr(10) ||
' </Style>' || chr(10) ||
' <Style ss:ID="s62">' || chr(10) ||
' <Borders>' || chr(10) ||
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>' || chr(10) ||
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>' || chr(10) ||
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>' || chr(10) ||
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>' || chr(10) ||
' </Borders>' || chr(10) ||
' <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="8" ss:Bold="1"/>' || chr(10) ||
' <Interior ss:Color="#D9D9D9" ss:Pattern="Solid"/>' || chr(10) ||
' </Style>' || chr(10) ||
' <Style ss:ID="s63">' || chr(10) ||
' <Borders>' || chr(10) ||
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>' || chr(10) ||
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>' || chr(10) ||
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>' || chr(10) ||
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>' || chr(10) ||
' </Borders>' || chr(10) ||
' <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="8"/>' || chr(10) ||
' </Style>' || chr(10) ||
' <Style ss:ID="s64">' || chr(10) ||
' <Borders>' || chr(10) ||
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>' || chr(10) ||
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>' || chr(10) ||
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>' || chr(10) ||
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>' || chr(10) ||
' </Borders>' || chr(10) ||
' </Style>' || chr(10) ||
' </Styles>' || chr(10)
);
-- Start of Item Sync Report sheet
dbms_lob.append(v_clob_xml_body,
' <Worksheet ss:Name="Item Sync Report">' || chr(10) ||
' <Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="'||
v_num_exp_rowcount||'" x:FullColumns="1"' || chr(10) ||
--' x:FullRows="1" ss:DefaultColumnWidth="42" ss:DefaultRowHeight="11.25">' || chr(10) ||
' x:FullRows="1" ss:DefaultColumnWidth="42" ss:AutoFitRowHeight="1">' || chr(10) ||
' <Column ss:Width="108.75"/>' || chr(10) ||
' <Column ss:Width="74.25"/>' || chr(10) ||
' <Column ss:Width="69"/>' || chr(10) ||
' <Column ss:Width="96"/>' || chr(10) ||
' <Column ss:Width="72"/>' || chr(10) ||
' <Column ss:Width="186"/>' || chr(10) ||
' <Row>' || chr(10) ||
' <Cell><Data ss:Type="String">Item Synchronization Report</Data></Cell>' || chr(10) ||
' </Row>' || chr(10) ||
' <Row>' || chr(10) ||
' <Cell><Data ss:Type="String">Date: '||to_char(SYSDATE, 'DD-MON-YYYY')||'</Data></Cell>' || chr(10) ||
' </Row>' || chr(10)
);
-- Write table header
dbms_lob.append(v_clob_xml_body,
' <Row ss:Index="4">' || chr(10) ||
' <Cell ss:StyleID="s62"><Data ss:Type="String">Item</Data></Cell>' || chr(10) ||
' <Cell ss:StyleID="s62"><Data ss:Type="String">Item Description</Data></Cell>' || chr(10) ||
' <Cell ss:StyleID="s62"><Data ss:Type="String">User Item Type</Data></Cell>' || chr(10) ||
' <Cell ss:StyleID="s62"><Data ss:Type="String">Item Catalog Group ID</Data></Cell>' || chr(10) ||
' <Cell ss:StyleID="s62"><Data ss:Type="String">VLS Item Status</Data></Cell>' || chr(10) ||
' <Cell ss:StyleID="s62"><Data ss:Type="String">Message</Data></Cell>' || chr(10) ||
' </Row>' || chr(10)
);
dbms_lob.append(v_clob_xml_body,
' <Row>' || chr(10) ||
' <Cell ss:StyleID="s63"><Data ss:Type="String">'||
tab_item_sync_rpt(v_chr_index).item_name||'</Data></Cell>' || chr(10) ||
' <Cell ss:StyleID="s63"><Data ss:Type="String">'||
tab_item_sync_rpt(v_chr_index).item_description||'</Data></Cell>' || chr(10) ||
' <Cell ss:StyleID="s63"><Data ss:Type="String">'||
tab_item_sync_rpt(v_chr_index).user_item_type||'</Data></Cell>' || chr(10) ||
' <Cell ss:StyleID="s64"><Data ss:Type="String">'||
to_char(tab_item_sync_rpt(v_chr_index).item_catalog_group_id)||'</Data></Cell>'|| chr(10)||
' <Cell ss:StyleID="s63"><Data ss:Type="String">'||
tab_item_sync_rpt(v_chr_index).item_status||'</Data></Cell>' || chr(10) ||
' <Cell ss:StyleID="s63"><Data ss:Type="String">'||
replace(tab_item_sync_rpt(v_chr_index).message,chr(10),' ')||'</Data></Cell>' || chr(10) ||
' </Row>' || chr(10)
);
-- Close workbook
dbms_lob.append(v_clob_xml_body,
' </Table>' || chr(10) ||
' <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">' || chr(10) ||
' <Print>' || chr(10) ||
' <ValidPrinterInfo/>' || chr(10) ||
' <HorizontalResolution>600</HorizontalResolution>' || chr(10) ||
' <VerticalResolution>600</VerticalResolution>' || chr(10) ||
' </Print>' || chr(10) ||
' <Selected/>' || chr(10) ||
' <Panes>' || chr(10) ||
' <Pane>' || chr(10) ||
' <Number>3</Number>' || chr(10) ||
' <ActiveRow>12</ActiveRow>' || chr(10) ||
' </Pane>' || chr(10) ||
' </Panes>' || chr(10) ||
' <ProtectObjects>False</ProtectObjects>' || chr(10) ||
' <ProtectScenarios>False</ProtectScenarios>' || chr(10) ||
' </WorksheetOptions>' || chr(10) ||
' </Worksheet>' || chr(10) ||
'</Workbook>' || chr(10)
);
