Examples

create or replace procedure Encrypt40_test
  is
  l_blob_10 blob;
  l_blob_101 blob;
begin
  select t.blob_file
    into l_blob_10
  from store_blob_tk t
  where t.filename = 'plpdf-toolkit-install-v100.pdf';
  
  dbms_lob.createtemporary(l_blob_101,TRUE);

  l_blob_101 := plpdf_toolkit.encrypt40(
    p_inputfile => l_blob_10,
    p_opassword => 'apple',
    p_upassword => null,
    p_canextractcontent => false,
    p_canmodify => true,
    p_canmodifyannotations => true,
    p_canprint => false
    );

  delete from store_blob_tk where id = 101;
  insert into store_blob_tk 
    (id,blob_file,filename,desctext) 
    values 
    (101,l_blob_101,'Encrypt40.pdf','id:10 plpdf-toolkit-install-v100.pdf Encrypt40');
  commit;
end;  
/

create or replace procedure Encrypt128_test 
  is
  l_blob_10 blob;
  l_blob_102 blob;
begin
  select t.blob_file
    into l_blob_10
  from store_blob_tk t
  where t.filename = 'plpdf-toolkit-install-v100.pdf';
  
  dbms_lob.createtemporary(l_blob_102,TRUE);

  l_blob_102 := plpdf_toolkit.encrypt128(
    p_inputfile => l_blob_10,
    p_opassword => 'pear12345',
    p_upassword => null,
    p_canAssemble => false,
    p_canextractcontent => false,
    p_canextractforaccessibility => false,
    p_canfillinform => false,
    p_canmodify => false,
    p_canmodifyannotations => false,
    p_canprint => false,
    p_canprintdegraded => false
    );
  
  delete from store_blob_tk where id = 102;
  insert into store_blob_tk
    (id,blob_file,filename,desctext) 
    values 
    (102,l_blob_102,'Encrypt128.pdf','id:10 plpdf-toolkit-install-v100.pdf Encrypt128');
  commit;  
end;
/

create or replace procedure Decrypt_test 
  is
  l_blob_20 blob;
  l_blob_103 blob; 
begin
  select t.blob_file
    into l_blob_20
  from store_blob_tk t
  where t.filename = 'plpdf-toolkit-install-v100_sec.pdf';
  
  dbms_lob.createtemporary(l_blob_103,TRUE);

  l_blob_103 := plpdf_toolkit.decrypt(
    p_inputfile => l_blob_20,
    p_password => 'pass12345'
    );
    
  delete from store_blob_tk where id = 103;
  insert into store_blob_tk 
    (id,blob_file,filename,desctext) 
    values 
    (103,l_blob_103,'Decrypt.pdf','id:20 plpdf-toolkit-install-v100_sec.pdf Decrypt');
  commit;    
end;  
/

create or replace procedure ExtractText_test
    is
  l_blob_10 blob;
  l_blob_104 blob;
begin
  select t.blob_file
    into l_blob_10
  from store_blob_tk t
  where t.filename = 'plpdf-toolkit-install-v100.pdf';
  
  dbms_lob.createtemporary(l_blob_104,TRUE);

  l_blob_104 := plpdf_toolkit.ExtractText(
   p_password => null,
   p_encoding => 'UTF-8',
   p_startPage => 1,
   p_endPage => 1,
   p_inputfile => l_blob_10
  );

  delete from store_blob_tk where id = 104;
  insert into store_blob_tk 
    (id,blob_file,filename,desctext) 
    values 
    (104,l_blob_104,'ExtractText.txt','id:10 plpdf-toolkit-install-v100.pdf ExtractText');
  commit;
end; 
/

create or replace procedure Merge_test
  is
  l_blob_10 blob;
  l_blob_30 blob;
  l_blob_105 blob;
begin
   select t.blob_file
    into l_blob_10
  from store_blob_tk t
  where t.filename = 'plpdf-toolkit-install-v100.pdf';
  
  select t.blob_file
    into l_blob_30
  from store_blob_tk t
  where t.filename = '00_ReadMe.pdf';
  
  dbms_lob.createtemporary(l_blob_105,TRUE);
  
  l_blob_105 := plpdf_toolkit.Merge(
    p_inputfile1 => l_blob_10,
    p_inputfile2 => l_blob_30
    );
    
  delete from store_blob_tk where id = 105;
  insert into store_blob_tk 
    (id,blob_file,filename,desctext) 
    values 
    (105,l_blob_105,'Merge.pdf','id:10 + id:30 Merge');
  commit;  
end;  
/

create or replace procedure ReplaceUrl_test
  is
  l_blob_40 blob;
  l_blob_106 blob;
begin
  select t.blob_file
    into l_blob_40
  from store_blob_tk t
  where t.filename = 'link.pdf';

  dbms_lob.createtemporary(l_blob_106,TRUE);
  
  l_blob_106 := plpdf_toolkit.ReplaceUrl(
    p_inputfile => l_blob_40,
    p_oldURI => 'http://www.yahoo.com/',
    p_newURI => 'http://www.plpdf.com/'
    );

  delete from store_blob_tk where id = 106;
  insert into store_blob_tk 
    (id,blob_file,filename,desctext) 
    values 
    (106,l_blob_106,'ReplaceUrl.pdf','id:40 ReplaceUrl');
  commit;  
end;  
/

create or replace procedure JPGStamp_test
  is
  l_blob_30 blob;
  l_blob_50 blob;
  l_blob_107 blob;
begin
  select t.blob_file
    into l_blob_30
  from store_blob_tk t
  where t.filename = '00_ReadMe.pdf';
  
  select t.blob_file
    into l_blob_50
  from store_blob_tk t
  where t.filename = 'image1.jpg';

  dbms_lob.createtemporary(l_blob_107,TRUE);
  
  l_blob_107 := plpdf_toolkit.JPGStamp(
    p_inputfile => l_blob_30,
    p_imagefile => l_blob_50,
    p_x => -1,
    p_y => -1,
    p_width => 200,
    p_height => 200
    );
    
  delete from store_blob_tk where id = 107;
  insert into store_blob_tk 
    (id,blob_file,filename,desctext) 
    values 
    (107,l_blob_107,'JPGStamp.pdf','id:30 + id:50 JPGStamp');
  commit;      
end;  
/

create or replace procedure JPGWaterMark_test
  is
  l_blob_30 blob;
  l_blob_50 blob;
  l_blob_108 blob;
begin
  select t.blob_file
    into l_blob_30
  from store_blob_tk t
  where t.filename = '00_ReadMe.pdf';
  
  select t.blob_file
    into l_blob_50
  from store_blob_tk t
  where t.filename = 'image1.jpg';

  dbms_lob.createtemporary(l_blob_108,TRUE);
  
  l_blob_108 := plpdf_toolkit.JPGWaterMark(
    p_inputfile => l_blob_30,
    p_imagefile => l_blob_50,
    p_x => -1,
    p_y => -1,
    p_width => 200,
    p_height => 200
    );
    
  delete from store_blob_tk where id = 108;
  insert into store_blob_tk 
    (id,blob_file,filename,desctext) 
    values 
    (108,l_blob_108,'JPGWaterMark.pdf','id:30 + id:50 JPGWaterMark');
  commit;      
end;  
/

create or replace procedure MetadataFromXML_test
  is
  l_blob_10 blob;
  l_blob_xml blob;
  l_blob_109 blob;
  l_xml sys.xmltype;
begin
  select t.blob_file
    into l_blob_10
  from store_blob_tk t
  where t.filename = 'plpdf-toolkit-install-v100.pdf';
  
  dbms_lob.createtemporary(l_blob_109,TRUE);
  dbms_lob.createtemporary(l_blob_xml,TRUE);
  
  l_xml := sys.xmltype.createXML(
'

PLPDF Toolkit author
PLPDF Toolkit subject
PLPDF Toolkit creator
Oracle PDF kw
PLPDF Toolkit producer
20080903183019
20080903183019
'
    );
  
  l_blob_xml := l_xml.getBlobVal(nls_charset_id('UTF8'));
  
  l_blob_109 := plpdf_toolkit.MetadataFromXML(
    p_inputfile => l_blob_10,
    p_xmlfile => l_blob_xml
    );

  delete from store_blob_tk where id = 109;
  insert into store_blob_tk 
    (id,blob_file,filename,desctext) 
    values 
    (109,l_blob_109,'MetadataFromXML.pdf','id:10 MetadataFromXML');
  commit;  
end; 
/

create or replace procedure MetadataFromPars_test
  is
  l_blob_10 blob;
  l_blob_110 blob;
begin
  select t.blob_file
    into l_blob_10
  from store_blob_tk t
  where t.filename = 'plpdf-toolkit-install-v100.pdf';
  
  dbms_lob.createtemporary(l_blob_110,TRUE);
  
  l_blob_110 := plpdf_toolkit.MetadataFromPars(
    p_inputfile => l_blob_10,
    p_title => 'PLPDF Toolkit Metadata title par',
    p_author => 'PLPDF Toolkit author par',
    p_subject => 'PLPDF Toolkit subject par',
    p_creator => 'PLPDF Toolkit creator par',
    p_keywords => 'Oracle PDF kw par',
    p_producer => 'PLPDF Toolkit producer par',
    p_createtime => to_date('20080101','YYYYMMDD'),
    p_modificationdate => sysdate
    );

  delete from store_blob_tk where id = 110;
  insert into store_blob_tk 
    (id,blob_file,filename,desctext) 
    values 
    (110,l_blob_110,'MetadataFromPars.pdf','id:10 MetadataFromPars');
  commit;      
end;  
/

create or replace procedure MetadaToXML_test
  is
  l_blob_10 blob;
  l_blob_111 blob;
begin
  select t.blob_file
    into l_blob_10
  from store_blob_tk t
  where t.filename = 'plpdf-toolkit-install-v100.pdf';
  
  dbms_lob.createtemporary(l_blob_111,TRUE);
  
  l_blob_111 := plpdf_toolkit.MetadaToXML(
    p_inputfile => l_blob_10
    );
    
  delete from store_blob_tk where id = 111;
  insert into store_blob_tk 
    (id,blob_file,filename,desctext) 
    values 
    (111,l_blob_111,'MetadaToXML.xml','id:10 MetadaToXML');
  commit;       
end;  
/

create or replace procedure MetadaToRecord_test
  is
  l_blob_10 blob;
  l_metadata plpdf_toolkit.t_metadata;
begin
  select t.blob_file
    into l_blob_10
  from store_blob_tk t
  where t.filename = 'plpdf-toolkit-install-v100.pdf';
    
  l_metadata := plpdf_toolkit.MetadaToRecord(
    p_inputfile => l_blob_10
    );
    
  dbms_output.put_line(l_metadata.Title);
  dbms_output.put_line(l_metadata.Author);
  dbms_output.put_line(l_metadata.Subject);
  dbms_output.put_line(l_metadata.Creator);
  dbms_output.put_line(l_metadata.Keywords);
  dbms_output.put_line(l_metadata.Producer);
  dbms_output.put_line(l_metadata.CreationDate);
  dbms_output.put_line(l_metadata.ModDate);
end;  
/

create or replace procedure TextStamp_test
  is
  l_blob_30 blob;
  l_blob_112 blob;
  l_color plpdf_toolkit.t_color;
begin
  select t.blob_file
    into l_blob_30
  from store_blob_tk t
  where t.filename = '00_ReadMe.pdf';
  
  dbms_lob.createtemporary(l_blob_112,TRUE);
  
  -- Turquoise 64,224,208
  l_color.r := 64;
  l_color.g := 224;
  l_color.b := 208;
  
  l_blob_112 := plpdf_toolkit.TextStamp(
    p_inputfile => l_blob_30,
    p_message => 'PLPDF Text',
    p_x => 200,
    p_y => 400,
    p_font => plpdf_toolkit.helvetica_bold,
    p_fontSize => 40,
    p_color => l_color
    );

  delete from store_blob_tk where id = 112;
  insert into store_blob_tk 
    (id,blob_file,filename,desctext) 
    values 
    (112,l_blob_112,'TextStamp.pdf','id:30 TextStamp');
  commit;        
end;  
/

create or replace procedure TextWaterMark_test
  is
  l_blob_30 blob;
  l_blob_113 blob;
  l_color plpdf_toolkit.t_color;
begin
  select t.blob_file
    into l_blob_30
  from store_blob_tk t
  where t.filename = '00_ReadMe.pdf';
  
  dbms_lob.createtemporary(l_blob_113,TRUE);
  
  -- Yellow_green 154,205,50
  l_color.r := 154;
  l_color.g := 205;
  l_color.b := 50;
  
  l_blob_113 := plpdf_toolkit.TextWaterMark(
    p_inputfile => l_blob_30,
    p_message => 'PLPDF Text wm',
    p_x => -1,
    p_y => -1,
    p_font => plpdf_toolkit.helvetica_bold,
    p_fontSize => 40,
    p_color => l_color
    );

  delete from store_blob_tk where id = 113;
  insert into store_blob_tk 
    (id,blob_file,filename,desctext) 
    values 
    (113,l_blob_113,'TextWaterMark.pdf','id:30 TextWaterMark');
  commit;        
end;  
/

create or replace procedure PDFWatermark_test
  is
  l_blob_60 blob;
  l_blob_70 blob;
  l_blob_114 blob;
begin
  select t.blob_file
    into l_blob_60
  from store_blob_tk t
  where t.filename = 'plpdf_package-v170.pdf';
  
  select t.blob_file
    into l_blob_70
  from store_blob_tk t
  where t.filename = '1-5.pdf';  
  
  dbms_lob.createtemporary(l_blob_114,TRUE);  
  
  l_blob_114 := plpdf_toolkit.PDFWatermark(
    p_inputfile => l_blob_60,
    p_overlayinputfile => l_blob_70
    );

  delete from store_blob_tk where id = 114;
  insert into store_blob_tk 
    (id,blob_file,filename,desctext) 
    values 
    (114,l_blob_114,'PDFWatermark.pdf','id:60 + id:70 PDFWatermark');
  commit;    
end;  
/

/*01 Minimal*/
function test_01_HelloWorld
  return clob is
begin
  plxls_xml.init; -- Initialize instance
  plxls_xml.addWorksheet(p_Name => 'FirstWS'); -- add worksheet
  plxls_xml.addRow; -- add a row to the current Worksheet
  plxls_xml.addCell_String_1('Hello World!'); -- add a cell to the current row

  return plxls_xml.getdoc;
end;
/

/*02 DocumentProperties*/
function test_02_DocumentProperties
  return clob is
  l_ws pls_integer;
  l_row pls_integer;
  l_cell pls_integer;
begin
  plxls_xml.init; -- Initialize instance
  plxls_xml.setDocumentProperties( -- metadata
    p_Title         => 'Title text',
    p_Subject       => 'Subject text',
    p_Author        => 'Author text',
    p_Keywords      => 'Keywords text',
    p_Description   => 'Description text',
    p_LastAuthor    => 'LastAuthor text',
    p_Created       => sysdate,
    p_Category      => 'Category text',
    p_Manager       => 'Manager text',
    p_Company       => 'Company text',
    p_HyperlinkBase =>  null,
    p_Version       => '1.2345'
    );
  l_ws := plxls_xml.addWorksheet( -- add worksheet
    p_Name => 'FirstWS'
    );
  l_row := plxls_xml.addRow( -- add a row
    p_ws => l_ws
    );
  l_cell := plxls_xml.addCell_String( -- add a cell
    p_ws   => l_ws,
    p_row  => l_row,
    p_data => 'Hello World!'
    );

  return plxls_xml.getdoc;
end;
/

/*03 ExcelWorkbook*/
function test_03_ExcelWorkbook
  return clob is
  l_ws pls_integer;
  l_row pls_integer;
  l_cell pls_integer;
begin
  plxls_xml.init; -- Initialize instance
  plxls_xml.setExcelWorkbook( -- workbook-level characteristics and properties
    p_WindowHeight     => 12405,
    p_WindowWidth      => 19020,
    p_WindowTopX       => 120,
    p_WindowTopY       => 45,
    p_ProtectStructure => False,
    p_ProtectWindows   => False
    );
  l_ws := plxls_xml.addWorksheet( -- add worksheet
    p_Name => 'FirstWS'
    );
  l_row := plxls_xml.addRow( -- add a row
    p_ws => l_ws
    );
  l_cell := plxls_xml.addCell_String( -- add a cell
    p_ws   => l_ws,
    p_row  => l_row,
    p_data => 'Hello World!'
    );

  return plxls_xml.getdoc;
end;
/

/*04 Style*/
function test_04_Style
  return clob is
  l_ws pls_integer;
  l_row pls_integer;
  l_cell pls_integer;
  l_style pls_integer;
begin
  plxls_xml.init; -- Initialize instance

  l_style := plxls_xml.addStyle(
    p_Name   => 'FirstStyle',
    p_Parent => null
    );

  l_ws := plxls_xml.addWorksheet( -- add worksheet
    p_Name => 'FirstWS'
    );
  l_row := plxls_xml.addRow( -- add a row
    p_ws => l_ws
    );
  l_cell := plxls_xml.addCell_String( -- add a cell
    p_ws   => l_ws,
    p_row  => l_row,
    p_StyleID => l_style,
    p_data => 'Hello World!'
    );

  return plxls_xml.getdoc;
end;
/

/*05 Style Alignment*/
function test_05_Style_Alignment
  return clob is
  l_ws pls_integer;
  l_row pls_integer;
  l_cell pls_integer;
  l_style_Horizontal pls_integer;
  l_style_Vertical pls_integer;
  l_style_Rotate pls_integer;
begin
  plxls_xml.init; -- Initialize instance

  l_style_Horizontal := plxls_xml.addStyle(
    p_Name   => 'Style_Horizontal',
    p_Parent => null
    );
  plxls_xml.Style_setAlignment(
    p_style      => l_style_Horizontal,
    p_Horizontal => 'Center'
    );

  l_style_Vertical := plxls_xml.addStyle(
    p_Name   => 'Style_Vertical',
    p_Parent => null
    );
  plxls_xml.Style_setAlignment(
    p_style   => l_style_Vertical,
    p_Vertical => 'Top'
    );

  l_style_Rotate := plxls_xml.addStyle(
    p_Name   => 'Style_Rotate',
    p_Parent => null
    );
  plxls_xml.Style_setAlignment(
    p_style  => l_style_Rotate,
    p_Rotate => 30
    );

  l_ws := plxls_xml.addWorksheet( -- add worksheet
    p_Name => 'FirstWS'
    );
  l_row := plxls_xml.addRow( -- add a row
    p_ws => l_ws
    );
  l_cell := plxls_xml.addCell_String( -- add a cell
    p_ws   => l_ws,
    p_row  => l_row,
    p_StyleID => l_style_Horizontal,
    p_data => 'Hello Horizontal!'
    );
  l_cell := plxls_xml.addCell_String( -- add a cell
    p_ws   => l_ws,
    p_row  => l_row,
    p_StyleID => l_style_Vertical,
    p_data => 'Hello Vertical!'
    );
  l_cell := plxls_xml.addCell_String( -- add a cell
    p_ws   => l_ws,
    p_row  => l_row,
    p_StyleID => l_style_Rotate,
    p_data => 'Hello Rotate!'
    );

  return plxls_xml.getdoc;
end;
/

/*06 Style_addBorder*/ 
function test_06_Style_addBorder
  return clob is
  l_ws pls_integer;
  l_row pls_integer;
  l_cell pls_integer;
  l_style pls_integer;
  l_border pls_integer;
begin
  plxls_xml.init; -- Initialize instance

  l_style := plxls_xml.addStyle(
    p_Name   => 'FirstStyle',
    p_Parent => null
    );

  l_border := plxls_xml.Style_addBorder(
    p_style     => l_style,
    p_Position  => 'Bottom',
    p_Color     => plx_xls_xdoc.c_Brown, --#A52A2A
    p_LineStyle => 'Continuous',
    p_Weight    => 1
    );

  l_ws := plxls_xml.addWorksheet( -- add worksheet
    p_Name => 'FirstWS'
    );
  l_row := plxls_xml.addRow( -- add a row
    p_ws => l_ws
    );
  l_cell := plxls_xml.addCell_String( -- add a cell
    p_ws   => l_ws,
    p_row  => l_row,
    p_StyleID => l_style,
    p_data => 'Hello World!'
    );

  return plxls_xml.getdoc;
end;
/

/*07 Style setFont*/
function test_07_Style_setFont
  return clob is
  l_ws pls_integer;
  l_row pls_integer;
  l_cell pls_integer;
  l_style pls_integer;
begin
  plxls_xml.init; -- Initialize instance

  l_style := plxls_xml.addStyle(
    p_Name   => 'FirstStyle',
    p_Parent => null
    );

  plxls_xml.Style_setFont(
    p_style         => l_style,
    p_Bold          => true,
    p_Color         => plx_xls_xdoc.c_Fuchsia, -- #FF00FF
    p_FontName      => 'Courier New',
    p_Italic        => true,
    p_Size          => 14,
    p_Family        => 'Modern'
    );

  l_ws := plxls_xml.addWorksheet( -- add worksheet
    p_Name => 'FirstWS'
    );
  l_row := plxls_xml.addRow( -- add a row
    p_ws => l_ws
    );
  l_cell := plxls_xml.addCell_String( -- add a cell
    p_ws   => l_ws,
    p_row  => l_row,
    p_StyleID => l_style,
    p_data => 'Hello World!'
    );

  return plxls_xml.getdoc;
end;
/

/*08 Style setInterior*/
function test_08_Style_setInterior
  return clob is
  l_ws pls_integer;
  l_row pls_integer;
  l_cell pls_integer;
  l_style pls_integer;
begin
  plxls_xml.init; -- Initialize instance

  l_style := plxls_xml.addStyle(
    p_Name   => 'FirstStyle',
    p_Parent => null
    );

  plxls_xml.Style_setInterior(
    p_style        => l_style,
    p_Color        => plx_xls_xdoc.c_LightCoral, -- #F08080
    p_Pattern      => 'Solid'
    );

  l_ws := plxls_xml.addWorksheet( -- add worksheet
    p_Name => 'FirstWS'
    );
  l_row := plxls_xml.addRow( -- add a row
    p_ws => l_ws
    );
  l_cell := plxls_xml.addCell_String( -- add a cell
    p_ws   => l_ws,
    p_row  => l_row,
    p_StyleID => l_style,
    p_data => 'Hello World!'
    );

  return plxls_xml.getdoc;
end;
/

/*09 Style setNumberFormat*/
function test_09_Style_setNumberFormat
  return clob is
  l_ws pls_integer;
  l_row pls_integer;
  l_cell pls_integer;
  l_style pls_integer;
begin
  plxls_xml.init; -- Initialize instance

  l_style := plxls_xml.addStyle(
    p_Name   => 'FirstStyle',
    p_Parent => null
    );

  plxls_xml.Style_setNumberFormat(
    p_style  => l_style,
    p_Format => 'General Number'
    );

  l_ws := plxls_xml.addWorksheet( -- add worksheet
    p_Name => 'FirstWS'
    );
  l_row := plxls_xml.addRow( -- add a row
    p_ws => l_ws
    );
  l_cell := plxls_xml.addCell_Number( -- add a cell
    p_ws   => l_ws,
    p_row  => l_row,
    p_StyleID => l_style,
    p_data => 12345.6789
    );

  return plxls_xml.getdoc;
end;
/

/*10 Style setProtection*/
function test_10_Style_setProtection
  return clob is
  l_ws pls_integer;
  l_row pls_integer;
  l_cell pls_integer;
  l_style pls_integer;
begin
  plxls_xml.init; -- Initialize instance

  l_style := plxls_xml.addStyle(
    p_Name   => 'FirstStyle',
    p_Parent => null
    );

  plxls_xml.Style_setProtection(
    p_style       => l_style,
    p_Protected   => true,
    p_HideFormula => true
    );

  l_ws := plxls_xml.addWorksheet( -- add worksheet
    p_Name => 'FirstWS'
    );
  l_row := plxls_xml.addRow( -- add a row
    p_ws => l_ws
    );
  l_cell := plxls_xml.addCell_String( -- add a cell
    p_ws   => l_ws,
    p_row  => l_row,
    p_StyleID => l_style,
    p_data => 'Hello World!'
    );

  return plxls_xml.getdoc;
end;
/

/*11 addWorksheet*/
function test_11_addWorksheet
  return clob is
begin
  plxls_xml.init; -- Initialize instance
  plxls_xml.addWorksheet(p_Name => 'WS1'); -- add worksheet
  plxls_xml.addRow; -- add a row to the current Worksheet
  plxls_xml.addCell_String_1('Hello WS1!'); -- add a cell to the current row

  plxls_xml.addWorksheet(p_Name => 'WS2'); -- add worksheet
  plxls_xml.addRow; -- add a row to the current Worksheet
  plxls_xml.addCell_String_1('Hello WS2!'); -- add a cell to the current row

  plxls_xml.addWorksheet(  -- add worksheet
    p_Name => 'WS last protected',
    p_Protected => true
    );
  plxls_xml.addRow; -- add a row to the current Worksheet
  plxls_xml.addCell_String_1('Hello Protected!'); -- add a cell to the current row

  return plxls_xml.getdoc;
end;
/

/*12 Worksheet setTable*/
function test_12_WS_setTable
  return clob is
  l_ws pls_integer;
begin
  plxls_xml.init; -- Initialize instance
  l_ws := plxls_xml.addWorksheet(p_Name => 'FirstWS'); -- add worksheet

  plxls_xml.WS_setTable(
    p_ws                  => l_ws,
    p_DefaultColumnWidth  => 100,
    p_DefaultRowHeight    => 20
    );

  plxls_xml.addRow; -- add a row to the current Worksheet
  plxls_xml.addCell_String_1('Hello World!'); -- add a cell to the current row

  return plxls_xml.getdoc;
end;
/

/*13 Worksheet setFooter*/
function test_13_WS_setFooter
  return clob is
  l_ws pls_integer;
begin
  plxls_xml.init; -- Initialize instance
  l_ws := plxls_xml.addWorksheet(p_Name => 'FirstWS'); -- add worksheet

  plxls_xml.WS_setFooter(
    p_ws     => l_ws,
    p_Margin => 20,
    p_Data   => '&C&P' -- Center, Page number
    );

  plxls_xml.addRow; -- add a row to the current Worksheet
  plxls_xml.addCell_String_1('Hello World!'); -- add a cell to the current row

  return plxls_xml.getdoc;
end;
/

/*14 Worksheet setHeader*/
function test_14_WS_setHeader
  return clob is
  l_ws pls_integer;
begin
  plxls_xml.init; -- Initialize instance
  l_ws := plxls_xml.addWorksheet(p_Name => 'FirstWS'); -- add worksheet

  plxls_xml.WS_setFooter(
    p_ws     => l_ws,
    p_Margin => 20,
    p_Data   => '&CPL/XLS' -- Center, Page number
    );

  plxls_xml.addRow; -- add a row to the current Worksheet
  plxls_xml.addCell_String_1('Hello World!'); -- add a cell to the current row

  return plxls_xml.getdoc;
end;
/

/*15 Worksheet setLayout*/
function test_15_WS_setLayout
  return clob is
  l_ws pls_integer;
begin
  plxls_xml.init; -- Initialize instance
  l_ws := plxls_xml.addWorksheet(p_Name => 'FirstWS'); -- add worksheet

  plxls_xml.WS_setLayout(
    p_ws               => l_ws,
    p_CenterHorizontal => true,
    p_CenterVertical   => true,
    p_Orientation      => 'Landscape'
    );

  plxls_xml.addRow; -- add a row to the current Worksheet
  plxls_xml.addCell_String_1('Hello World!'); -- add a cell to the current row

  return plxls_xml.getdoc;
end;
/

/*16 Worksheet setPageMargins*/
function test_16_WS_setPageMargins
  return clob is
  l_ws pls_integer;
begin
  plxls_xml.init; -- Initialize instance
  l_ws := plxls_xml.addWorksheet(p_Name => 'FirstWS'); -- add worksheet

  plxls_xml.WS_setPageMargins(
    p_ws     => l_ws,
    p_Bottom => 0.8,
    p_Left   => 0.6,
    p_Right  => 0.6,
    p_Top    => 0.8
    );

  plxls_xml.addRow; -- add a row to the current Worksheet
  plxls_xml.addCell_String_1('Hello World!'); -- add a cell to the current row

  return plxls_xml.getdoc;
end;
/

/*17 addColumn*/
function test_17_addColumn
  return clob is
  l_ws pls_integer;
begin
  plxls_xml.init; -- Initialize instance
  l_ws := plxls_xml.addWorksheet(p_Name => 'FirstWS'); -- add worksheet

  plxls_xml.addColumn(
    p_ws           => l_ws,
    p_AutoFitWidth => true
    );

  plxls_xml.addRow; -- add a row to the current Worksheet
  plxls_xml.addCell_String_1('Hello World!'); -- add a cell to the current row

  return plxls_xml.getdoc;
end;
/

/*18 addRow*/
function test_18_addRow
  return clob is
  l_style pls_integer;
begin
  plxls_xml.init; -- Initialize instance
  plxls_xml.addWorksheet(p_Name => 'FirstWS'); -- add worksheet

  plxls_xml.addRow; -- add a row to the current Worksheet
  plxls_xml.addCell_String_1('Hello World!'); -- add a cell to the current row


  l_style := plxls_xml.addStyle(
    p_Name   => 'FirstStyle',
    p_Parent => null
    );

  plxls_xml.Style_setInterior(
    p_style        => l_style,
    p_Color        => plx_xls_xdoc.c_LightCoral, -- #F08080
    p_Pattern      => 'Solid'
    );

  plxls_xml.addRow( -- add a row to the current Worksheet
    p_StyleID => l_style
    );

  plxls_xml.addCell_Number_1(2);
  plxls_xml.addCell_Number_1(3);

  return plxls_xml.getdoc;
end;
/

/*19 addCell*/
function test_19_addCell
  return clob is
  l_ws pls_integer;
  l_row pls_integer;
  l_style pls_integer;
  l_cell pls_integer;
begin
  plxls_xml.init; -- Initialize instance
  l_ws := plxls_xml.addWorksheet(p_Name => 'FirstWS'); -- add worksheet
  l_row := plxls_xml.addRow(  -- add a row to the current Worksheet
    p_ws => l_ws
    );

  l_style := plxls_xml.addStyle(
    p_Name   => 'HRefStyle',
    p_Parent => null
    );

  plxls_xml.Style_Font_Color(
    p_style => l_style,
    p_color => plx_xls_xdoc.c_Blue -- #0000FF
    );

  plxls_xml.Style_Font_Underline(
    p_style => l_style
    );

  l_cell := plxls_xml.addCell(
    p_ws            => l_ws,
    p_row           => l_row,
    p_HRef          => 'http://www.plsqlplus.com/',
    p_StyleID       => l_style
    );

  plxls_xml.Cell_setData(
    p_ws     => l_ws,
    p_row    => l_row,
    p_cell   => l_cell,
    p_Type   => plx_xls_xdoc.v_String,
    p_data   => 'Hello World!'
    );

  return plxls_xml.getdoc;
end;
/

/*20 Cell setComment*/
function test_20_Cell_setComment
  return clob is
  l_ws pls_integer;
  l_row pls_integer;
  l_cell pls_integer;
begin
  plxls_xml.init; -- Initialize instance
  l_ws := plxls_xml.addWorksheet(p_Name => 'FirstWS'); -- add worksheet
  l_row := plxls_xml.addRow(  -- add a row to the current Worksheet
    p_ws => l_ws
    );

  l_cell := plxls_xml.addCell(
    p_ws            => l_ws,
    p_row           => l_row
    );

  plxls_xml.Cell_setData(
    p_ws     => l_ws,
    p_row    => l_row,
    p_cell   => l_cell,
    p_Type   => plx_xls_xdoc.v_String,
    p_data   => 'Hello World!'
    );

  plxls_xml.Cell_setComment(
    p_ws     => l_ws,
    p_row    => l_row,
    p_cell   => l_cell,
    p_Author => 'PLAuthor',
    p_data   => 'Cell Comment Cell Comment Cell Comment Cell Comment Cell Comment'
    );

  return plxls_xml.getdoc;
end;
/

create or replace procedure test_plxls_parser(
   p_xls_data blob
   ) is
   v_sheets plxls_parser.t_sheets;
 begin
   v_sheets := plxls_parser.ParseXLS(p_xls_data);
   for l_s in 0 .. v_sheets.count - 1 loop
     dbms_output.put_line( to_char(l_s) || '. Sheet: ' || v_sheets(l_s).name);
	 for l_row in 1..v_sheets(l_s).numRows loop
	   for l_col in 1..v_sheets(l_s).numCols loop
	     begin
		   dbms_output.put_line('Cell-' || to_char(l_row) || '-' ||to_char(l_col) || ':' || v_sheets(l_s).cells(l_row)(l_col).cell_text);
		 exception when others then null;
         end;
	   end loop;
	 end loop;
   end loop;
 end;
/ 

create or replace procedure plzip_minimal as
  /* 01. Example: Minimal example   */
  /* Before running, load the following file with a 
     given filename into the ZIP_EXAMPLES table:
     
    ID |    FILENAME     |       FILE
    ------------------------------------------    
     1 |   1010.bmp       | \Examples\1010.bmp
      
  */
  l_name varchar2(4000 char);    -- filename
  l_file blob;    -- source blob
  l_zip blob;     -- result (ZIP)
begin
  -- get a source file and its name.
  select filename, ori_file into l_name, l_file from zip_example 
    where id=1;
  -- reset PL/ZIP package
  plzip.createNewZip;
  -- add file to the archive
  plzip.AddFile(
                p_filename => l_name,
                p_file => l_file
                );
  -- build and save ZIP
  l_zip := plzip.saveZip;
  insert into store_blob values(l_zip,sysdate);
  commit;
end plzip_minimal;
/

create or replace procedure plzip_internalpath as
  /* 02. Example: internal file structure */
  /* Before running, load the following files with a 
     given filename into the ZIP_EXAMPLES table:
        
   ID |    FILENAME      |       FILE
   --------------------------------------    
   2  | images/img09.bmp | \Examples\1009.bmp
   3  | images/img11.bmp | \Examples\1011.bmp
    
  */
  l_name varchar2(4000 char); -- filename
  l_file blob;    -- source blob
  l_zip blob;     -- result (ZIP)
begin
  -- get a source file
  select filename, ori_file into l_name, l_file from zip_example 
    where id=2;
  -- reset PL/ZIP package
  plzip.createNewZip;
  -- add file to the archive and the filename contains the internal path
  plzip.AddFile(
                p_filename => l_name,
                p_file => l_file
                );
  -- get next source file
  select filename, ori_file into l_name, l_file from zip_example 
    where id=3;
  -- add file to the archive and the filename contains the internal path
  plzip.AddFile(
                p_filename => l_name,
                p_file => l_file
                );
  -- build and save ZIP
  l_zip := plzip.saveZip;
  insert into store_blob values(l_zip,sysdate);
  commit;
end plzip_internalpath;
/

create or replace procedure plzip_precompression as
  /* 03. Example: Precompressed parts */
  /* This example represents, how to add frequently used files without
     compressing it every time. */
  /* Before running, load the following file with a 
     given filename into the ZIP_EXAMPLES table:
     
    ID |    FILENAME     |       FILE
    ------------------------------------------    
     4 |   img.bmp       | \Examples\1012.bmp
      
  */
  
  l_name  varchar2(4000 char); -- filename
  l_file  blob;    -- source blob
  l_zip   blob;     -- zip
  l_id    number := 4; -- file ID
  
  l_prcfile   blob;   -- compressed file
  l_crc32     raw(8); -- precomputed CRC32 checksum
  l_osize     raw(8); -- original filesize record
begin
  -- get a source file
  select filename, ori_file into l_name, l_file from zip_example 
    where id=l_id;
  -- reset PL/ZIP package
  plzip.createNewZip;
  -- create precompressed file part, therefor frequently used files no need to compress every time again. 
  plzip.createPrecompressedFile( p_file => l_file,     -- source file (IN)
                                 p_cfile => l_prcfile, -- deflated file (OUT)
                                 p_crc32 => l_crc32,   -- precalculated CRC32 (OUT)
                                 p_osize => l_osize    -- original file size (OUT)
                                );
  /* 
  --If you store these values, you can add the file again (without compress again)
  update zip_example 
    set pc_crc32=l_crc32,       -- save CRC32
        pc_orisize=l_osize,     -- save _original_file_size_ record
        pc_file=l_prcfile       -- save precompressed file
      where id=l_id;
  -- after this statement, check the zip_example table.
  */
  for i in 1..9 loop
    -- add compressed file part with existing CRC32 checksum and file lenght record
    plzip.addPrecompressedFile( p_filename => 'Folder' || i || '/' || l_name, -- name (path) of file
                                p_file => l_prcfile,  -- precompressed part
                                p_crc32 => l_crc32,   -- precalculated CRC32 value
                                p_osize => l_osize    -- original size
                              );                     
  end loop;                            
  -- build and save ZIP
  l_zip := plzip.saveZip;
  insert into store_blob values(l_zip,sysdate);
  commit;
end plzip_precompression;
/