as_pdv3_v5 (english)

PL/SQL package for create PDF

(versione italiana)
Some time ago I found this package to create PDF documents directly from PL / SQL. During this time I added some features for the creation of reports and today I wanted to publish my work.

Your comments, bug indication, correction of english,  are appreciated.



Thank to Anton Scheffer who made the original package.


Today I've added new features to the procedure query2table, now it accepts blob fields as image and insert it into cell grid, with resizing and alignment, see the NEW POST HERE.

Download my  Lastest vesion  and documentation Github
.


This example create a PDF with breaking on the first field;
it doesn't need table because it uses random data.

-- Created on 25/06/2014 by VALR 
declare 

        i              INTEGER;
        v_vFileName    VARCHAR2(255);
        v_vOddColor    VARCHAR2(6) := 'd0d0d0';
        v_vHeadColor   VARCHAR2(6) := 'e0ffff';
        v_vOraDir      VARCHAR2(50) := 'PDF';
        v_vPageProc    VARCHAR2(32000);
        r_Fmt  as_pdf3_v5.tp_columns:=as_pdf3_v5.tp_columns();
        v_vSQL varchar2(4000);
begin
  v_vFileName    := 'Test_as_pf3_v5.pdf';
  -- Define Sheet Format 
  as_pdf3_v5.init;
  as_pdf3_v5.set_page_format('A4');
  as_pdf3_v5.set_page_orientation('P');
  as_pdf3_v5.set_margins(30, 10, 15, 10, 'mm');
     
  -- Define Header and Footer
  v_vPageProc := q'[
  begin
    §.set_font('helvetica', 'B', 10 );
    §.put_txt('mm',  5, 5, 'Valerio Rossetti');
    §.put_txt('mm',  90, 5, 'Data: ');
    §.set_font('helvetica', 'N', 10);
    §.put_txt('mm', 115,5, ']'||to_char(sysdate,'dd/mm/yy')||q'[');    
    §.put_txt('mm', 175,5, 'Page #PAGE_NR# of #PAGE_COUNT#');
  end;
  ]';
     
  as_pdf3_v5.set_page_proc(v_vPageProc);
  
  --If you use barcode font, remove comment 
  --as_pdf3_v5.load_ttf_font('PDF', 'ean13.ttf', 'CID', TRUE);

    -- Define column format
    begin
      r_fmt.extend(9);
      i:=1; -- (riga di rottura
      r_fmt(i).colWidth:=25;
      r_fmt(i).colLabel:='cod mkt';
      r_fmt(i).hFontStyle:='B';
      r_fmt(i).hFontSize:=10;
      r_fmt(i).hAlignment:='C';
      r_fmt(i).hAlignVert:='T';
      r_fmt(i).tAlignment:='L';
      r_fmt(i).tAlignVert:='B';
      r_fmt(i).tFontSize:=8;
      r_fmt(i).tCHeight := 7;
      r_fmt(i).hCHeight := 7;
      r_fmt(i).cellRow := 1;
      
      i:=i+1;--2
      r_fmt(i).colWidth:=20;
      r_fmt(i).colLabel:='cod_art';
      r_fmt(i).hFontStyle:='B';
      r_fmt(i).hFontSize:=10;
      r_fmt(i).hAlignment:='R';
      r_fmt(i).hAlignVert:='T';
      r_fmt(i).tAlignVert:='T';
      --r_fmt(i).offsetX := 0;
      r_fmt(i).tCHeight := 7;
      r_fmt(i).hCHeight := 7;
      
      i:=i+1;--3
      r_fmt(i).colWidth:=22;
      r_fmt(i).colLabel:='pz imb';
      r_fmt(i).hFontStyle:='B';
      r_fmt(i).hFontSize:=10;
      r_fmt(i).hAlignment:='C';
      r_fmt(i).hAlignVert:='T';
      r_fmt(i).tAlignment:='R';
      r_fmt(i).tAlignVert:='M';
      
      i:=i+1;--4
      r_fmt(i).colWidth:=12;
      r_fmt(i).colLabel:='udm V';
      r_fmt(i).hFontStyle:='B';
      r_fmt(i).hFontSize:=10;
      r_fmt(i).hAlignment:='C';
      r_fmt(i).hAlignVert:='T';
      r_fmt(i).tAlignment:='C';
      r_fmt(i).tAlignVert:='B';
      r_fmt(i).tBorder := as_pdf3_v5.BorderType('TB');
      
      i:=i+1;--5
      r_fmt(i).colWidth:=15;
      r_fmt(i).colLabel:='udm Lt';
      r_fmt(i).hFontStyle:='B';
      r_fmt(i).hFontSize:=10;
      r_fmt(i).hAlignment:='C';
      r_fmt(i).hAlignVert:='T';
      r_fmt(i).tAlignment:='C';
      r_fmt(i).tAlignVert:='B';
      
      i:=i+1;--6
      r_fmt(i).colWidth:=20;
      r_fmt(i).colLabel:='prz. vend.';
      r_fmt(i).hFontStyle:='B';
      r_fmt(i).hFontSize:=10;
      r_fmt(i).hAlignment:='C';
      r_fmt(i).hAlignVert:='T';
      r_fmt(i).tAlignment:='R';
      r_fmt(i).tAlignVert:='B';
      i:=i+1;--7
      r_fmt(i).colWidth:=20;
      r_fmt(i).colLabel:='prz. cost';
      r_fmt(i).hFontStyle:='B';
      r_fmt(i).hFontSize:=10;
      r_fmt(i).hAlignment:='C';
      r_fmt(i).hAlignVert:='T';
      r_fmt(i).tAlignment:='R';
      r_fmt(i).tAlignVert:='B';
      i:=i+1;--8
      r_fmt(i).colWidth:=16;
      r_fmt(i).colLabel:='margin';
      r_fmt(i).hFontStyle:='B';
      r_fmt(i).hFontSize:=10;
      r_fmt(i).hAlignment:='C';
      r_fmt(i).hAlignVert:='T';
      r_fmt(i).tAlignment:='R';
      r_fmt(i).tAlignVert:='B';
      r_fmt(i).tBorder := 15;
      i:=i+1;--9
      r_fmt(i).colWidth:=150;
      r_fmt(i).colLabel:='product descrition';
      r_fmt(i).hFontStyle:='B';
      r_fmt(i).hFontSize:=10;
      r_fmt(i).hAlignment:='L';
      r_fmt(i).hAlignVert:='T';
      r_fmt(i).hCHeight := 8;
      r_fmt(i).tAlignment:='L';
      r_fmt(i).tAlignVert:='C';
      r_fmt(i).tFontSize:=8;
      r_fmt(i).offsetX := 0;
      r_fmt(i).tCHeight := 8;
      r_fmt(i).cellRow:=2;
      r_fmt(i).tBorder := as_pdf3_v5.BorderType('LRBT');

    end;

     v_vSQL := q'[
SELECT cod_mkt,
       c_art, 
       pcs_imb,
       udm_vend,
       udm_list,
       prz_vend,
       prz_vend*.8 prz_cost,
       prz_vend*.2 margin,
       description
from (       
SELECT case when rownum <5 then '5201001' else '5201003' end cod_mkt,
             rownum*1000+rownum*124 c_art, 
             (trunc(rownum/3)+1)*4  pcs_imb,
             'N'  udm_vend,
             'KG' udm_list,
             round(dbms_random.value(40,2),2) prz_vend,
             round(dbms_random.value(8,2),2)  margin,
             'ART '||to_char(rownum*1000+rownum*124) description
        FROM DUAL d CONNECT BY ROWNUM <= 10
)
order by 1
   ]';
   dbms_output.put_line(v_vSQL);
         
   as_pdf3_v5.query2table(v_vSQL,
     r_fmt,
     as_pdf3_v5.tp_colors('000000',v_vHeadColor,'000000',
                          '000000','ffffff','000000', 
                          '000000',v_vOddColor,'000000'),
     15,15, 'mm',0,1
     );
         
    as_pdf3_v5.save_pdf(v_vOraDir, v_vFileName, TRUE);
  END;