AS_PDF3_V5 new features

AS_PDF3_V5 new features 

Hello everyone, a few days ago I posted the SQL for create PDF document directly from PL/SQL, based on original package by Anton Scheffer.
Now I've added some new features (version 3.5.2):

• Procedure query2table now accept CLOB query fields, treat this as images and locate it into array of cells, using horizontal and vertical alignment and risizing if specified.
• New procedere query2labels that work like query2table but dispose record into multiple columns and rows. The scope is to create sheets with the same label with different contents like a mailing list.
• Begin of multilanguage errore messages (English or Italian).
• Added offsetY parameter to columns format type, it works like offsetX, but obviously acts on the ordered. Used in combination with cellRow and tRowHeight, allows the positioning of each individual field in an independent manner.
• Added a simplified call to query2table and query2label whit colors parameter as a simpliest list of hex rgb colors comma separated,
  for example '000000,e0ffff,000000,000000,ffffff,000000,000000,d0d0d0,000000' .

 2014-11-13. little bugfix on log procedure
 2016-05-11. bugfix on callingh query2table with p_color parameter set to null
 2016-09-06  bugfix in recursive call of write (for text on multiple lines)

comments and corrections, as always, are welcome

Download Ultima versione (0.3.5.11)   Example  Documentation Github

AS_PDF3_V5 nuove funzionalità

Salve a tutti, qualche giorno fa ho postato il codice SQL per creare documenti PDF direttamente da PL/SQL, basato sul package originale di Anton Scheffer.
Ora ho aggiunto alcune nuove funzionalià:

• Procedure query2table: ora la query acetta campi CLOB, li tratta come immagini e li posiziona all'interno della griglia, con la possibilità di allineamento orizzontale, verticale e ridimensionamento se specificato.
• Nuova procedere query2labels: funziona come query2table, ma dispone i record su colonne e righe miltiple. Lo scopo è di realizare fogli di etichette un po' come farebbe un programma di mailing list per la stampa delle etichette indirizzi.
• Inizio della gestione degli errori personalizzati con messaggi in Italiano e Inglese.
• Aggiunto il parametro offsetY al record dei formati colonne, funziona come offsetX, ma ovviamente agisce sulle ordinate. Usato in combinazione con cellRow e tRowHeight, permette di posizionare ogni singolo campo in modo indipendente.
• Aggiunta una chaimata più semplice per  query2table e query2label dove il parametro dei colori è una semplice lista di colori rgb separata da virgol,
  esempi: '000000,e0ffff,000000,000000,ffffff,000000,000000,d0d0d0,000000' .

2014-11-13. lieve bugfix alla procedura di log
2016-05-11. bugfix con parametro p_color null
2016-09-06  bugfix nelle chiamate ricorsive della procedura write (avvengono per il testo su più righe)
Come sempre commenti e correzioni saranno graditi.

Download Last version (0.3.5.11)    Esempio  Documentazione Github


Change Log
-------------------------------------------------------------------------------
** Date: 18-09-2014             Version:  0.3.5.03
**   bugfix and impovement suggested by Giuseppe Polo
**   +query2table               added Interline parameter
**    setCellFont               bugfix for Header
** Date: 26-09-2014             Version:  0.3.5.04
**   bugfix for recursive call of function Write
** Date: 29-09-2014             Version:  0.3.5.05
**   +query2table               added pFrame parameter ex:  'L=2pt; C=FF0000'
**                                       where L=Linesize and C=rgb hex colour
**   +query2table               p_colors also accept CSV string of rgb colours   
**   +set_Language              Set language for erorr messages.
**                                       (English, Italian)
**   +put_image                 add parameters p_cellWidth, p_cellheight
**   +Columns                   can contain blob IMAGE 
**   +FullJustify Alignment     for write and query2table functions
**
** Date: 25-11-2014             Version:  0.3.5.06
**   bugfix for query with more than 200 records
**   +query2table               Add optional parameter p_bulk_size:=200
**   +cursor2table              if = 0 buffer is autodetected,
**                              but query runs 2 times!
** Date: 24-06-2015             Version:  0.3.5.07
**   BugFix query2table         Reset rowHeith when
**                              RowHeight Min or Exacat as specified
** Date: 30-06-2015             Version:  0.3.5.08
**   BugFix PrepareRecord       Fix problem with rowHeight
** Date: 26-08-2015                    Version:  0.3.5.09
**   BugFix colorTable                 Fix problem with undefined collection
**   WARNING! if you change package name, propertly set g_package variabile
** Date: 14-12-2015                    Version:  0.3.5.10
**   Bugix in PrepareRecord
** Date: 11-05-2016                    Version:  0.3.5.11
**   Bugix error when calling with null colours
**   colorTable changed and moved before query2Table
**   query2table & query2label changed
**   when calling query2table you must use empty string '' instead of null for p_colors parameter
**

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;

as_pdv3_v5 (italiano)

Un package PL/SQL per creare PDF

(english version)
Tempo fa ho trovato questo package per la creazione di documenti PDF direttamente da PL/SQL, con il tempo ho aggiunto alcune funzionalità per la realizzazione di report ed oggi ho voluto pubblicare il mio lavoro.
Ringrazio Anton Scheffer che ha realizzato il package originale.

La versione descritta di questo post è stata corretta ed aggiornata, consultate il nouvo POST per la descrizione delle nuove funzionalità
Download Codice (ultima versione)  e della documentazione.
o meglio consultate il progetto su Github



questo è un esempio per la creazione di un PDF con rottura sul primo campo;
non ha bisogno di tavole perché usa dati casuali.

-- 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';
  -- FORMATTAZZIONE FOGLIO
  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');
     
  -- Definisco Intestazione e piede Pagina
  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, 'Pagina #PAGE_NR# di #PAGE_COUNT#');
  end;
  ]';
     
  as_pdf3_v5.set_page_proc(v_vPageProc);
  
  --Se vuoi usare dei font per i barcode    
  --as_pdf3_v5.load_ttf_font('PDF', 'ean13.ttf', 'CID', TRUE);

    -- Definizione dei formati
    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. costo.';
      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:='margine';
      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:='des.prodotto';
      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, 
       pezzi_imb,
       udm_vendita,
       udm_listino,
       prz_vendita,
       prz_vendita*.8 prz_costo,
       prz_vendita*.2 margine,
       descrizione
from (       
SELECT case when rownum <5 then '5201001' else '5201003' end cod_mkt,
             rownum*1000+rownum*124 c_art, 
             (trunc(rownum/3)+1)*4  pezzi_imb,
             'N'  udm_vendita,
             'KG' udm_listino,
             round(dbms_random.value(40,2),2) prz_vendita,
             round(dbms_random.value(8,2),2) margine,
             'ARTICOLO '||to_char(rownum*1000+rownum*124) descrizione
        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;