IT-eye

To generate a pdf file in webforms with the product pldpf (www.plpdf.com) you can use the following routines :

• webform procedure exec_plpdf_report. Start generating to file, printer or screen.
• database package plpdf_util.

PROCEDURE EXEC_PLPDF_REPORT
( p_repname varchar2
, p_destype varchar2 default 'CACHE'
, p_parlist plpdf_util.plpdf_parlist
)
IS
l_web_host CONSTANT varchar2(200) := '123.123.123.123:1234/pls/abcde';
l_printer CONSTANT varchar2(100) := 'xdc123';
l_dir CONSTANT varchar2(30) := '/tmp';

l_filename varchar2(500);
l_html_string varchar2(500);
l_output blob;
BEGIN

if p_destype = 'CACHE'
then
--
-- Html string. The parlist is split in maxl 10 parameters.
-- It has to be varchar parameters, because on the URL
-- no parlist kan be used.
--
l_html_string := 'plpdf_util.show_plpdf_output?p_repnaam='||p_repname;
for i in p_parlist.first..least(p_parlist.last, 10)
loop
l_html_string := l_html_string||'&p_par'||i||'='||p_parlist(i);
end loop;
web.show_document('http://'||l_web_host||'/'||l_html_string, '_blank');

elsif p_destype in ( 'PRINTER', 'FILE')
then
--
-- Generate blob en download to file
--
l_filename := plpdf_util.plpdf_report_to_file (p_repname, p_parlist);

if p_destype = 'PRINTER'
then
--
-- Print file
--
host('/usr/bin/lp -d '||l_printer||' '||l_dir||'/'||l_filename);
message('File is printed');

else -- FILE
message('PDF file is gegenerated : '||l_dir||'/'||l_filename );

end if;

end if;

END;

create or replace package plpdf_util
is

type plpdf_parlist is table of varchar2(200) index by binary_integer;

procedure show_plpdf_output ( p_repname in varchar2
, p_par1 in varchar2 default null
, p_par2 in varchar2 default null
, p_par3 in varchar2 default null
, p_par4 in varchar2 default null
, p_par5 in varchar2 default null
, p_par6 in varchar2 default null
, p_par7 in varchar2 default null
, p_par8 in varchar2 default null
, p_par9 in varchar2 default null
, p_par10 in varchar2 default null
);

function plpdf_report_to_file ( p_repname in varchar2
, p_parlist in plpdf_util.plpdf_parlist
)
return varchar2;

function writeblobtofile ( p_blob in blob )
return varchar2;

end;
/

create or replace package body plpdf_util
is

procedure show_plpdf_output ( p_repname in varchar2
, p_par1 in varchar2 default null
, p_par2 in varchar2 default null
, p_par3 in varchar2 default null
, p_par4 in varchar2 default null
, p_par5 in varchar2 default null
, p_par6 in varchar2 default null
, p_par7 in varchar2 default null
, p_par8 in varchar2 default null
, p_par9 in varchar2 default null
, p_par10 in varchar2 default null
)
is
l_output blob;
l_plsql_blok varchar2(1000);
begin

l_plsql_blok := 'begin :l_output := ' || p_repname||
case when p_par1 is not null then '( '''||p_par1||''' ' end ||
case when p_par2 is not null then ', '''||p_par2||''' ' end ||
case when p_par3 is not null then ', '''||p_par3||''' ' end ||
case when p_par4 is not null then ', '''||p_par4||''' ' end ||
case when p_par5 is not null then ', '''||p_par5||''' ' end ||
case when p_par6 is not null then ', '''||p_par6||''' ' end ||
case when p_par7 is not null then ', '''||p_par7||''' ' end ||
case when p_par8 is not null then ', '''||p_par8||''' ' end ||
case when p_par9 is not null then ', '''||p_par9||''' ' end ||
case when p_par10 is not null then ', '''||p_par10||''' ' end ||
case when p_par1 is not null then ')' end ||
'; end;'
;

execute immediate l_plsql_blok using in out l_output;

--
-- generate html output
--
owa_util.mime_header('application/pdf',FALSE);
htp.p('Content-Length: ' || dbms_lob.getlength(l_output));
owa_util.http_header_close;
wpg_docload.download_file(l_output);

end;

function plpdf_report_to_file ( p_repname in varchar2
, p_parlist in plpdf_util.plpdf_parlist
)
return varchar2
is
l_output blob;
l_plsql_blok varchar2(1000);
begin

l_plsql_blok := 'begin :l_output := ' || p_repname;
for i in p_parlist.first..p_parlist.last
loop
if i = 1
then
l_plsql_blok := l_plsql_blok||' ( '''||p_parlist(i)||''' ';
else
l_plsql_blok := l_plsql_blok||' , '''||p_parlist(i)||''' ';
end if;
end loop;

if p_parlist.exists(1)
then
l_plsql_blok := l_plsql_blok||' ) ';
end if;
l_plsql_blok := l_plsql_blok||' ; end;';

--
-- Generate plpdf output
--
execute immediate l_plsql_blok using in out l_output;

--
-- write output to file and return the filename
--
return plpdf_util.writeblobtofile(l_output);

end;

function writeblobtofile ( p_blob in blob )
return varchar2
is
l_blob blob;
l_blob_length integer;
l_out_file utl_file.file_type;
l_blob_position integer := 1;
l_buffer raw(32767);
l_chunk_size binary_integer := 32767;

l_dir CONSTANT varchar2(30) := 'FILE_UPLOAD_DIR';
l_filename CONSTANT varchar2(500) := 'pr_'||to_char(sysdate,'hh24misssss')||
userenv('SESSIONID')||'.pdf';

begin

l_blob := p_blob;

-- retrieve the size of the blob
l_blob_length:=dbms_lob.getlength(l_blob);

-- open a handle to the location where you are going to write the blob to file
-- note: the 'wb' parameter means "write in byte mode"
l_out_file := utl_file.fopen (l_dir, l_filename, 'wb', l_chunk_size);

-- write the blob to file in chunks
while l_blob_position <= l_blob_length
loop
if l_blob_position + l_chunk_size - 1 > l_blob_length
then
l_chunk_size := l_blob_length - l_blob_position + 1;
end if;
dbms_lob.read(l_blob, l_chunk_size, l_blob_position, l_buffer);
utl_file.put_raw(l_out_file, l_buffer, true);
l_blob_position := l_blob_position + l_chunk_size;
end loop;

-- close the file handle
utl_file.fclose (l_out_file);

return l_filename;

end;

end; -- package body