create or replace package query_print is

/**
<b>Query Print</b>
<p>
The query_print package is designed to enable users to create PDF documents based on simple SQL 
statements with basic formatting options. The package is designed to give quick results with minimal 
design and development commitment from the user.
*/


type t_colsize is table of number index by pls_integer;

/**
Fills the available width according to the ratio of the width of the fields in the query.
%param p_query varchar2: the query
%param p_height number: height of cells
%param p_header_fill number default 0: specifies whether the header should be colored according to the fill color
%return -
*/
procedure query_print_01(
  p_query varchar2,
  p_height number,
  p_header_fill number default 0
  );

/**
Width of the columns/fields for the query is specified in a parameter.
%param p_query varchar2: the query
%param p_widths plpdf_components.t_colsize: width of the cells in the order of the fields of the query 
%param p_height number: height of cells
%param p_header_fill number default 0: specifies whether the header should be colored according to the fill color
%return -
*/  
procedure query_print_02(
  p_query varchar2,
  p_height number,
  p_widths t_colsize,
  p_header_fill number default 0
  );

/**
Width of the columns/fields is calculated according to the length of the data type of the cellwidth parameter.
%param p_query varchar2: the query
%param p_height number: height of cells
%param p_cellwidth number: cell/character width used for the calculation of the column/field width
%param p_header_fill number default 0: specifies whether the header should be colored according to the fill color
%return -
*/    
procedure query_print_03(
  p_query varchar2,
  p_height number,
  p_cellwidth number,
  p_header_fill number default 0
  );

/**
Pivots the fields in the query so that the column become rows.
%param p_query varchar2: the query
%param p_height number: height of cells
%param p_column_header varchar2: Text that appears in the column/field header
%param p_column_width number: Width of the column/field header
%param p_value_header varchar2: Text that appears in the value header
%param p_value_width number: Width of the value header
%param p_header_fill number default 0: specifies whether the header should be colored according to the fill color
%return -
*/    
procedure query_print_04(
  p_query varchar2,
  p_height number,
  p_column_header varchar2,
  p_column_width number,
  p_value_header varchar2,
  p_value_width number,
  p_header_fill number default 0
  );

/**
Pivots the fields in the query so that the column become rows with multiple columns.
%param p_query varchar2: the query
%param p_height number: height of cells
%param p_column_header varchar2: Text that appears in the column/field header
%param p_column_width number: Width of the column/field header
%param p_value_header varchar2: Text that appears in the value header
%param p_value_width number: Width of the value header
%param p_col_pcs number default 1: number of columns
%param p_col_gap number default 0: gap between columns
%param p_header_fill number default 0: specifies whether the header should be colored according to the fill color
%return -
*/    
procedure query_print_05(
  p_query varchar2,
  p_height number,
  p_column_header varchar2,
  p_column_width number,
  p_value_header varchar2,
  p_value_width number,
  p_col_pcs number default 1,
  p_col_gap number default 0,
  p_header_fill number default 0
  );

/**
Width of the columns/fields for the query is specified in a parameter as a percentage.
%param p_query varchar2: the query
%param p_widths plpdf_components.t_colsize: width of the cells specified as a percentage of the total width in the order of the fields of the query
%param p_height number: height of cells
%param p_header_fill number default 0: specifies whether the header should be colored according to the fill color
%return -
*/    
procedure query_print_06(
  p_query varchar2,
  p_height number,
  p_percents t_colsize,
  p_header_fill number default 0
  );

/**
Width of the columns/fields is calculated according to the length of the data type of the cellwidth parameter. 
A parameter specifies how many cells should NUMBER and DATE data type fields be written into. For text data type 
fields the length of the text specifies the number of cells. Maximizes the width of cells.
%param p_query varchar2: the query
%param p_height number: height of cells
%param p_cellwidth number: cell/character width used for the calculation of the column/field width
%param p_numbercells number: specifies how many cells a NUMBER data type field is written into
%param p_datecells number: specifies how many cells a DATE data type field is written into
%param p_maxcells number default 0
%param p_header_fill number default 0: specifies whether the header should be colored according to the fill color
%return -
*/    
procedure query_print_07(
  p_query varchar2,
  p_height number,
  p_cellwidth number,
  p_numbercells number,
  p_datecells number,
  p_maxcells number default 0,
  p_header_fill number default 0
  );

/**
same as query_print_07, except if the columns do not fit on the page then each column width is reduced according to its ratio to the whole width.
%param p_query varchar2: the query
%param p_height number: height of cells
%param p_cellwidth number: cell/character width used for the calculation of the column/field width
%param p_numbercells number: specifies how many cells a NUMBER data type field is written into
%param p_datecells number: specifies how many cells a DATE data type field is written into
%param p_maxcells number default 0
%param p_header_fill number default 0: specifies whether the header should be colored according to the fill color 
%return -
*/    
procedure query_print_08(
  p_query varchar2,
  p_height number,
  p_cellwidth number,
  p_numbercells number,
  p_datecells number,
  p_maxcells number default 0,
  p_header_fill number default 0
  );

/**
same as query_print_07, except if the columns do not fit on the page then the column width of columns that are not NUMBER or DATE is reduced according to its ratio to the whole width.
%param p_query varchar2: the query
%param p_height number: height of cells
%param p_cellwidth number: cell/character width used for the calculation of the column/field width
%param p_numbercells number: specifies how many cells a NUMBER data type field is written into
%param p_datecells number: specifies how many cells a DATE data type field is written into
%param p_maxcells number default 0
%param p_header_fill number default 0: specifies whether the header should be colored according to the fill color 
%return -
*/    
procedure query_print_09(
  p_query varchar2,
  p_height number,
  p_cellwidth number,
  p_numbercells number,
  p_datecells number,
  p_maxcells number default 0,
  p_header_fill number default 0
  );

end;
/
create or replace package body query_print is
---------------------------------------------------------------------------------------------------
procedure query_print_01(
  p_query varchar2,
  p_height number,
  p_header_fill number default 0
  ) is

  l_colsize t_colsize;

  l_cur		integer default dbms_sql.open_cursor;
  l_colval  varchar2(4000);
  l_status  integer;
  l_colCnt  number default 0;
  l_desc    dbms_sql.desc_tab;
  l_wsum 	number := 0;
  l_align   varchar2(1) := 'L';
  ------------
  procedure header_print is
  begin
    for i in 1 .. l_colCnt loop
	  Plpdf.PrintCell(l_colsize(i),p_height,l_desc(i).col_name,'1',0,'C',p_header_fill);
	end loop;
	Plpdf.LineBreak(p_height);
  end;
  ------------
  begin

    dbms_sql.parse(  l_cur,  p_query, dbms_sql.native);
    dbms_sql.describe_columns( l_cur, l_colCnt, l_desc);

	-- calculate size of columns
    for i in 1 .. l_colCnt loop
	  l_wsum := l_wsum + greatest(l_desc(i).col_max_len,length(l_desc(i).col_name));
	end loop;
	for i in 1 .. l_colCnt loop
	  l_colsize(i):=  greatest(l_desc(i).col_max_len,length(l_desc(i).col_name))/l_wsum * plpdf.GetPageAvailableWidth;
	end loop;

	-- print table header
	header_print;

    for i in 1 .. l_colCnt loop
       dbms_sql.define_column( l_cur, i, l_colval, 4000 );
    end loop;

    l_status := dbms_sql.execute(l_cur);
	-- print rows
    while ( dbms_sql.fetch_rows(l_cur) > 0 ) loop
	  if plpdf.CheckPageBreak(p_height) then
	    header_print;
	  end if;
      for i in 1 .. l_colCnt loop
        dbms_sql.column_value( l_cur, i, l_colval );
		if l_desc(i).col_type in (2,3) then -- number
		  l_align := 'R';
		else
		  l_align := 'L';
		end if;
		Plpdf.PrintCell(l_colsize(i),p_height,l_colval,'1',0,l_align);
      end loop;
      Plpdf.LineBreak(p_height);
    end loop;
    dbms_sql.close_cursor(l_cur);

end;
---------------------------------------------------------------------------------------------------
procedure query_print_02(
  p_query varchar2,
  p_height number,
  p_widths t_colsize,
  p_header_fill number default 0
  ) is

  l_cur		   integer default dbms_sql.open_cursor;
  l_colval     varchar2(4000);
  l_status     integer;
  l_colCnt     number default 0;
  l_desc       dbms_sql.desc_tab;
  l_wsum 	   number := 0;
  l_align_size number := 0;
  l_align   varchar2(1) := 'L';
  ------------
  procedure header_print is
  begin
    if l_align_size > 0 then
	  Plpdf.PrintCell(l_align_size,p_height,null);
	end if;
    for i in 1 .. l_colCnt loop
	  Plpdf.PrintCell(p_widths(i),p_height,l_desc(i).col_name,'1',0,'C',p_header_fill);
	end loop;
	Plpdf.LineBreak(p_height);
  end;
  ------------
  begin

    dbms_sql.parse(  l_cur,  p_query, dbms_sql.native);
    dbms_sql.describe_columns( l_cur, l_colCnt, l_desc);

    for i in 1 .. l_colCnt loop
	  l_wsum := l_wsum + p_widths(i);
	end loop;

	l_align_size := (plpdf.GetPageAvailableWidth - l_wsum) / 2;

	-- print table header
	header_print;

    for i in 1 .. l_colCnt loop
       dbms_sql.define_column( l_cur, i, l_colval, 4000 );
    end loop;

    l_status := dbms_sql.execute(l_cur);
	-- print rows
    while ( dbms_sql.fetch_rows(l_cur) > 0 ) loop
	  if plpdf.CheckPageBreak(p_height) then
	    header_print;
	  end if;
	  if l_align_size > 0 then
	    Plpdf.PrintCell(l_align_size,p_height,null);
	  end if;
      for i in 1 .. l_colCnt loop
	    if l_desc(i).col_type in (2,3) then -- number
		  l_align := 'R';
		else
		  l_align := 'L';
		end if;
        dbms_sql.column_value( l_cur, i, l_colval );
		Plpdf.PrintCell(p_widths(i),p_height,l_colval,'1',0,l_align);
      end loop;
      Plpdf.LineBreak(p_height);
    end loop;
    dbms_sql.close_cursor(l_cur);

end;
---------------------------------------------------------------------------------------------------
procedure query_print_03(
  p_query varchar2,
  p_height number,
  p_cellwidth number,
  p_header_fill number default 0
  ) is

  l_colsize t_colsize;

  l_cur		integer default dbms_sql.open_cursor;
  l_colval  varchar2(4000);
  l_status  integer;
  l_colCnt  number default 0;
  l_desc    dbms_sql.desc_tab;
  l_wsum 	   number := 0;
  l_align_size number := 0;
  l_align   varchar2(1) := 'L';
  ------------
  procedure header_print is
  begin
    if l_align_size > 0 then
	  Plpdf.PrintCell(l_align_size,p_height,null);
	end if;
    for i in 1 .. l_colCnt loop
	  Plpdf.PrintCell(l_colsize(i),p_height,l_desc(i).col_name,'1',0,'C',p_header_fill);
	end loop;
	Plpdf.LineBreak(p_height);
  end;
  ------------
  begin

    dbms_sql.parse(  l_cur,  p_query, dbms_sql.native);
    dbms_sql.describe_columns( l_cur, l_colCnt, l_desc);

	-- calculate size of columns
	for i in 1 .. l_colCnt loop
	  l_colsize(i):=  greatest(l_desc(i).col_max_len,length(l_desc(i).col_name)) * p_cellwidth;
	  l_wsum := l_wsum + l_colsize(i);
	end loop;

	l_align_size := (plpdf.GetPageAvailableWidth - l_wsum) / 2;

	-- print table header
	header_print;

    for i in 1 .. l_colCnt loop
       dbms_sql.define_column( l_cur, i, l_colval, 4000 );
    end loop;

    l_status := dbms_sql.execute(l_cur);
	-- print rows
    while ( dbms_sql.fetch_rows(l_cur) > 0 ) loop
	  if plpdf.CheckPageBreak(p_height) then
	    header_print;
	  end if;
	  if l_align_size > 0 then
	    Plpdf.PrintCell(l_align_size,p_height,null);
	  end if;
      for i in 1 .. l_colCnt loop
	    if l_desc(i).col_type in (2,3) then -- number
		  l_align := 'R';
		else
		  l_align := 'L';
		end if;
        dbms_sql.column_value( l_cur, i, l_colval );
		Plpdf.PrintCell(l_colsize(i),p_height,l_colval,'1',0,l_align);
      end loop;
      Plpdf.LineBreak(p_height);
    end loop;
    dbms_sql.close_cursor(l_cur);

end;
---------------------------------------------------------------------------------------------------
procedure query_print_04(
  p_query varchar2,
  p_height number,
  p_column_header varchar2,
  p_column_width number,
  p_value_header varchar2,
  p_value_width number,
  p_header_fill number default 0
  ) is

  l_colsize t_colsize;

  l_cur		integer default dbms_sql.open_cursor;
  l_colval  varchar2(4000);
  l_status  integer;
  l_colCnt  number default 0;
  l_desc    dbms_sql.desc_tab;
  l_wsum 	number := 0;
  l_align_size number := 0;
  ------------
  procedure header_print is
  begin
    if l_align_size > 0 then
	  Plpdf.PrintCell(l_align_size,p_height,null);
	end if;
	Plpdf.PrintCell(p_column_width,p_height,p_column_header,'1',0,'C',p_header_fill);
	Plpdf.PrintCell(p_value_width,p_height,p_value_header,'1',0,'C',p_header_fill);
	Plpdf.LineBreak(p_height);
  end;
  ------------
  begin

    dbms_sql.parse(  l_cur,  p_query, dbms_sql.native);
    dbms_sql.describe_columns( l_cur, l_colCnt, l_desc);

	-- calculate align
	l_wsum := p_column_width + p_value_width;
	l_align_size := (plpdf.GetPageAvailableWidth - l_wsum) / 2;

	-- print table header
	header_print;

    for i in 1 .. l_colCnt loop
       dbms_sql.define_column( l_cur, i, l_colval, 4000 );
    end loop;

    l_status := dbms_sql.execute(l_cur);
	-- print rows
    while ( dbms_sql.fetch_rows(l_cur) > 0 ) loop -- only 1 row
      for i in 1 .. l_colCnt loop
	  if plpdf.CheckPageBreak(p_height) then
	    header_print;
	  end if;
	    if l_align_size > 0 then
	      Plpdf.PrintCell(l_align_size,p_height,null);
	    end if;
        dbms_sql.column_value( l_cur, i, l_colval );
		Plpdf.PrintCell(p_value_width,p_height,l_desc(i).col_name,'1',0,'L');
		Plpdf.PrintCell(p_value_width,p_height,l_colval,'1',0,'L');
		Plpdf.LineBreak(p_height);
      end loop;
    end loop;
    dbms_sql.close_cursor(l_cur);

end;
---------------------------------------------------------------------------------------------------
procedure query_print_05(
  p_query varchar2,
  p_height number,
  p_column_header varchar2,
  p_column_width number,
  p_value_header varchar2,
  p_value_width number,
  p_col_pcs number default 1,
  p_col_gap number default 0,
  p_header_fill number default 0
  ) is

  l_colsize t_colsize;

  l_cur		integer default dbms_sql.open_cursor;
  l_colval  varchar2(4000);
  l_status  integer;
  l_colCnt  number default 0;
  l_desc    dbms_sql.desc_tab;
  l_wsum 	number := 0;
  l_align_size number := 0;
  l_row number := 0;
  ------------
  procedure header_print is
  begin
    if l_align_size > 0 then
	  Plpdf.PrintCell(l_align_size,p_height,null);
	end if;
	for l_i in 1..p_col_pcs loop
	  Plpdf.PrintCell(p_column_width,p_height,p_column_header,'1',0,'C',p_header_fill);
	  Plpdf.PrintCell(p_value_width,p_height,p_value_header,'1',0,'C',p_header_fill);
	  if p_col_gap > 0 and l_i < p_col_pcs then
	    Plpdf.PrintCell(p_col_gap,p_height,null);
	  end if;
	end loop;
	Plpdf.LineBreak(p_height);
  end;
  ------------
  begin

    dbms_sql.parse(  l_cur,  p_query, dbms_sql.native);
    dbms_sql.describe_columns( l_cur, l_colCnt, l_desc);

	-- calculate align
	l_wsum := p_col_pcs * (p_column_width + p_value_width) + ( (p_col_pcs - 1) * p_col_gap );
	l_align_size := (plpdf.GetPageAvailableWidth - l_wsum) / 2;
	-- print table header
	header_print;

    for i in 1 .. l_colCnt loop
       dbms_sql.define_column( l_cur, i, l_colval, 4000 );
    end loop;

    l_status := dbms_sql.execute(l_cur);
	-- print rows
    while ( dbms_sql.fetch_rows(l_cur) > 0 ) loop -- only 1 row
      for i in 1 .. l_colCnt loop
	  if plpdf.CheckPageBreak(p_height) then
	    header_print;
	  end if;
	    l_row := l_row + 1;
	    if l_align_size > 0 and mod(l_row,p_col_pcs) = 1 then
	      Plpdf.PrintCell(l_align_size,p_height,null);
	    end if;
          dbms_sql.column_value( l_cur, i, l_colval );
		  Plpdf.PrintCell(p_value_width,p_height,l_desc(i).col_name,'1',0,'L');
		  Plpdf.PrintCell(p_value_width,p_height,l_colval,'1',0,'L');
		  if p_col_gap > 0 and mod(l_row,p_col_pcs) <> 0 then
	        Plpdf.PrintCell(p_col_gap,p_height,null);
	      end if;
		 if mod(l_row,p_col_pcs) = 0 then
	       Plpdf.LineBreak(p_height);
		 end if;
      end loop;
    end loop;
    dbms_sql.close_cursor(l_cur);

end;
---------------------------------------------------------------------------------------------------
procedure query_print_06(
  p_query varchar2,
  p_height number,
  p_percents t_colsize,
  p_header_fill number default 0
  ) is

  l_cur		   integer default dbms_sql.open_cursor;
  l_colval     varchar2(4000);
  l_status     integer;
  l_colCnt     number default 0;
  l_desc       dbms_sql.desc_tab;
  l_wsum 	   number := 0;
  l_align_size number := 0;
  l_align   varchar2(1) := 'L';
  l_colsize t_colsize;
  ------------
  procedure header_print is
  begin
    if l_align_size > 0 then
	  Plpdf.PrintCell(l_align_size,p_height,null);
	end if;
    for i in 1 .. l_colCnt loop
	  Plpdf.PrintCell(l_colsize(i),p_height,l_desc(i).col_name,'1',0,'C',p_header_fill);
	end loop;
	Plpdf.LineBreak(p_height);
  end;
  ------------
  begin

    dbms_sql.parse(  l_cur,  p_query, dbms_sql.native);
    dbms_sql.describe_columns( l_cur, l_colCnt, l_desc);

    for i in 1 .. l_colCnt loop
	  l_colsize(i) :=  p_percents(i)/100 * plpdf.GetPageAvailableWidth;
	  l_wsum := l_wsum + p_percents(i);
	end loop;

	if l_wsum < 100 then
	  l_align_size := (plpdf.GetPageAvailableWidth * (1 - (l_wsum/100))) / 2;
	end if;

	-- print table header
	header_print;

    for i in 1 .. l_colCnt loop
       dbms_sql.define_column( l_cur, i, l_colval, 4000 );
    end loop;

    l_status := dbms_sql.execute(l_cur);
	-- print rows
    while ( dbms_sql.fetch_rows(l_cur) > 0 ) loop
	  if plpdf.CheckPageBreak(p_height) then
	    header_print;
	  end if;
	  if l_align_size > 0 then
	    Plpdf.PrintCell(l_align_size,p_height,null);
	  end if;
      for i in 1 .. l_colCnt loop
	    if l_desc(i).col_type in (2,3) then -- number
		  l_align := 'R';
		else
		  l_align := 'L';
		end if;
        dbms_sql.column_value( l_cur, i, l_colval );
		Plpdf.PrintCell(l_colsize(i),p_height,l_colval,'1',0,l_align);
      end loop;
      Plpdf.LineBreak(p_height);
    end loop;
    dbms_sql.close_cursor(l_cur);

end;
---------------------------------------------------------------------------------------------------
procedure query_print_07(
  p_query varchar2,
  p_height number,
  p_cellwidth number,
  p_numbercells number,
  p_datecells number,
  p_maxcells number default 0,
  p_header_fill number default 0
  ) is

  l_colsize t_colsize;

  l_cur		integer default dbms_sql.open_cursor;
  l_colval  varchar2(4000);
  l_status  integer;
  l_colCnt  number default 0;
  l_desc    dbms_sql.desc_tab;
  l_wsum 	   number := 0;
  l_align_size number := 0;
  l_align   varchar2(1) := 'L';
  ------------
  procedure header_print is
  begin
    if l_align_size > 0 then
	  Plpdf.PrintCell(l_align_size,p_height,null);
	end if;
    for i in 1 .. l_colCnt loop
	  Plpdf.PrintCell(l_colsize(i),p_height,l_desc(i).col_name,'1',0,'C',p_header_fill);
	end loop;
	Plpdf.LineBreak(p_height);
  end;
  ------------
  begin

    dbms_sql.parse(  l_cur,  p_query, dbms_sql.native);
    dbms_sql.describe_columns( l_cur, l_colCnt, l_desc);

	-- calculate size of columns
	for i in 1 .. l_colCnt loop
	  if l_desc(i).col_type in (2,3) then -- number
	    l_colsize(i) :=  p_numbercells * p_cellwidth;
	  elsif l_desc(i).col_type = 12 then -- date
	    l_colsize(i) :=  p_datecells * p_cellwidth;
	  else
	    l_colsize(i) :=  greatest(l_desc(i).col_max_len,length(l_desc(i).col_name)) * p_cellwidth;
	  end if;
	  if p_maxcells <> 0 and ( l_colsize(i) > (p_maxcells * p_cellwidth))  then
		  l_colsize(i) := p_maxcells * p_cellwidth;
		end if;
	  l_wsum := l_wsum + l_colsize(i);
	end loop;

	l_align_size := (plpdf.GetPageAvailableWidth - l_wsum) / 2;

	-- print table header
	header_print;

    for i in 1 .. l_colCnt loop
       dbms_sql.define_column( l_cur, i, l_colval, 4000 );
    end loop;

    l_status := dbms_sql.execute(l_cur);
	-- print rows
    while ( dbms_sql.fetch_rows(l_cur) > 0 ) loop
	  if plpdf.CheckPageBreak(p_height) then
	    header_print;
	  end if;
	  if l_align_size > 0 then
	    Plpdf.PrintCell(l_align_size,p_height,null);
	  end if;
      for i in 1 .. l_colCnt loop
	    if l_desc(i).col_type in (2,3) then -- number
		  l_align := 'R';
		else
		  l_align := 'L';
		end if;
        dbms_sql.column_value( l_cur, i, l_colval );
		Plpdf.PrintCell(l_colsize(i),p_height,l_colval,'1',0,l_align);
      end loop;
      Plpdf.LineBreak(p_height);
    end loop;
    dbms_sql.close_cursor(l_cur);

end;
---------------------------------------------------------------------------------------------------
procedure query_print_08(
  p_query varchar2,
  p_height number,
  p_cellwidth number,
  p_numbercells number,
  p_datecells number,
  p_maxcells number default 0,
  p_header_fill number default 0
  ) is

  l_colsize t_colsize;

  l_cur		integer default dbms_sql.open_cursor;
  l_colval  varchar2(4000);
  l_status  integer;
  l_colCnt  number default 0;
  l_desc    dbms_sql.desc_tab;
  l_wsum 	   number := 0;
  l_align_size number := 0;
  l_align   varchar2(1) := 'L';
  ------------
  procedure header_print is
  begin
    if l_align_size > 0 then
	  Plpdf.PrintCell(l_align_size,p_height,null);
	end if;
    for i in 1 .. l_colCnt loop
	  Plpdf.PrintCell(l_colsize(i),p_height,l_desc(i).col_name,'1',0,'C',p_header_fill);
	end loop;
	Plpdf.LineBreak(p_height);
  end;
  ------------
  begin

    dbms_sql.parse(  l_cur,  p_query, dbms_sql.native);
    dbms_sql.describe_columns( l_cur, l_colCnt, l_desc);

	-- calculate size of columns
	for i in 1 .. l_colCnt loop
	  if l_desc(i).col_type in (2,3) then -- number
	    l_colsize(i) :=  p_numbercells * p_cellwidth;
	  elsif l_desc(i).col_type = 12 then -- date
	    l_colsize(i) :=  p_datecells * p_cellwidth;
	  else
	    l_colsize(i) :=  greatest(l_desc(i).col_max_len,length(l_desc(i).col_name)) * p_cellwidth;
	  end if;
	  if p_maxcells <> 0 and ( l_colsize(i) > (p_maxcells * p_cellwidth))  then
		  l_colsize(i) := p_maxcells * p_cellwidth;
		end if;
	  l_wsum := l_wsum + l_colsize(i);
	end loop;

	if l_wsum < plpdf.GetPageAvailableWidth then
	  l_align_size := (plpdf.GetPageAvailableWidth - l_wsum) / 2;
	elsif l_wsum > plpdf.GetPageAvailableWidth then
	  for i in 1 .. l_colCnt loop
	    l_colsize(i) := l_colsize(i) * plpdf.GetPageAvailableWidth / l_wsum;
	  end loop;
	end if;

	-- print table header
	header_print;

    for i in 1 .. l_colCnt loop
       dbms_sql.define_column( l_cur, i, l_colval, 4000 );
    end loop;

    l_status := dbms_sql.execute(l_cur);
	-- print rows
    while ( dbms_sql.fetch_rows(l_cur) > 0 ) loop
	  if plpdf.CheckPageBreak(p_height) then
	    header_print;
	  end if;
	  if l_align_size > 0 then
	    Plpdf.PrintCell(l_align_size,p_height,null);
	  end if;
      for i in 1 .. l_colCnt loop
	    if l_desc(i).col_type in (2,3) then -- number
		  l_align := 'R';
		else
		  l_align := 'L';
		end if;
        dbms_sql.column_value( l_cur, i, l_colval );
		Plpdf.PrintCell(l_colsize(i),p_height,l_colval,'1',0,l_align);
      end loop;
      Plpdf.LineBreak(p_height);
    end loop;
    dbms_sql.close_cursor(l_cur);

end;
---------------------------------------------------------------------------------------------------
procedure query_print_09(
  p_query varchar2,
  p_height number,
  p_cellwidth number,
  p_numbercells number,
  p_datecells number,
  p_maxcells number default 0,
  p_header_fill number default 0
  ) is

  l_colsize t_colsize;

  l_cur		integer default dbms_sql.open_cursor;
  l_colval  varchar2(4000);
  l_status  integer;
  l_colCnt  number default 0;
  l_desc    dbms_sql.desc_tab;
  l_wsum 	   number := 0;
  l_wsum_char 	   number := 0;
  l_align_size number := 0;
  l_align   varchar2(1) := 'L';
  ------------
  procedure header_print is
  begin
    if l_align_size > 0 then
	  Plpdf.PrintCell(l_align_size,p_height,null);
	end if;
    for i in 1 .. l_colCnt loop
	  Plpdf.PrintCell(l_colsize(i),p_height,l_desc(i).col_name,'1',0,'C',p_header_fill);
	end loop;
	Plpdf.LineBreak(p_height);
  end;
  ------------
  begin

    dbms_sql.parse(  l_cur,  p_query, dbms_sql.native);
    dbms_sql.describe_columns( l_cur, l_colCnt, l_desc);

	-- calculate size of columns
	for i in 1 .. l_colCnt loop
	  if l_desc(i).col_type in (2,3) then -- number
	    l_colsize(i) :=  p_numbercells * p_cellwidth;
	  elsif l_desc(i).col_type = 12 then -- date
	    l_colsize(i) :=  p_datecells * p_cellwidth;
	  else
	    l_colsize(i) :=  greatest(l_desc(i).col_max_len,length(l_desc(i).col_name)) * p_cellwidth;
	  end if;
	  if p_maxcells <> 0 and ( l_colsize(i) > (p_maxcells * p_cellwidth))  then
		  l_colsize(i) := p_maxcells * p_cellwidth;
		end if;
	  l_wsum := l_wsum + l_colsize(i);
	  if l_desc(i).col_type not in (2,3,12) then -- not number, date
	    l_wsum_char := l_wsum_char + l_colsize(i);
	  end if;
	end loop;

	if l_wsum < plpdf.GetPageAvailableWidth then
	  l_align_size := (plpdf.GetPageAvailableWidth - l_wsum) / 2;
	elsif l_wsum > plpdf.GetPageAvailableWidth then
	  for i in 1 .. l_colCnt loop
	    if l_desc(i).col_type not in (2,3,12) then
	      l_colsize(i) := l_colsize(i) *
		                  (plpdf.GetPageAvailableWidth - (l_wsum - l_wsum_char)) / l_wsum_char;
		end if;
	  end loop;
	end if;

	-- print table header
	header_print;

    for i in 1 .. l_colCnt loop
       dbms_sql.define_column( l_cur, i, l_colval, 4000 );
    end loop;

    l_status := dbms_sql.execute(l_cur);
	-- print rows
    while ( dbms_sql.fetch_rows(l_cur) > 0 ) loop
	  if plpdf.CheckPageBreak(p_height) then
	    header_print;
	  end if;
	  if l_align_size > 0 then
	    Plpdf.PrintCell(l_align_size,p_height,null);
	  end if;
      for i in 1 .. l_colCnt loop
	    if l_desc(i).col_type in (2,3) then -- number
		  l_align := 'R';
		else
		  l_align := 'L';
		end if;
        dbms_sql.column_value( l_cur, i, l_colval );
		Plpdf.PrintCell(l_colsize(i),p_height,l_colval,'1',0,l_align);
      end loop;
      Plpdf.LineBreak(p_height);
    end loop;
    dbms_sql.close_cursor(l_cur);

end;
---------------------------------------------------------------------------------------------------
end;
/