Package

Inicial - Banco de Códigos

Gerador Dinâmico de HTML/CSV

Data de Inclusão: 24/11/2006 | Última Alteração: 24/11/2006
Tipo:Package | Enviada por: Geraldo Viana De Paula Junior (nulaya@yahoo.com)

-- Versão do RDBMS testada: 9.2.0.4
create or replace package nk4xgrid
timestamp '1998-07-07:10:10:10'
authid current_user
is
------------------------------------------------------------------
-- (c) Copyright 1998-2004 Nulaya Knowledge
------------------------------------------------------------------
-- NK4XGRID: Dynamic HTML/CSV Grid Generator
------------------------------------------------------------------

------------------------------------------------------------------
-- Collections, Records, Variables, Constants, Exceptions, Cursors
------------------------------------------------------------------
-- Oracle types constants
varchar2_type constant integer := 1;
number_type constant integer := 2;
long_type constant integer := 8;
varchar_type constant integer := 9;
date_type constant integer := 12;
rowid_type constant integer := 69;
char_type constant integer := 96;
clob_type constant integer := 112;
blob_type constant integer := 113;
bfile_type constant integer := 114;
cfile_type constant integer := 115;
urowid_type constant integer := 208;

-- Format Constants
csv_format constant varchar2(10) := 'CSV';
html_format constant varchar2(10) := 'HTML';

-- HTML Constants
justify_left constant varchar2(10) := 'LEFT';
justify_right constant varchar2(10) := 'RIGHT';

-- GRID Structure
type grid_columns is table of varchar2(4000) index by binary_integer;
type grid_array is table of grid_columns index by binary_integer;

-- Buffers
type buffer_array is table of varchar2(32767) index by binary_integer;
gt_public_buffer buffer_array;

------------------------------------------------------------------
-- GRID
------------------------------------------------------------------
procedure grid(ft_buffer out buffer_array,
fv_stmt in varchar2,
fv_format in varchar2);

------------------------------------------------------------------
-- HTML_DEFAULT
------------------------------------------------------------------
procedure html_default(ft_buffer out buffer_array,
ft_grid in out nocopy grid_array,
ft_template in grid_columns);

------------------------------------------------------------------
-- CSV_DEFAULT
------------------------------------------------------------------
procedure csv_default(ft_buffer out buffer_array,
ft_grid in out nocopy grid_array);

end nk4xgrid;
/
show errors





create or replace package body nk4xgrid
timestamp '1998-07-07:10:10:10'
is
------------------------------------------------------------------
-- (c) Copyright 1998-2004 Nulaya Knowledge
------------------------------------------------------------------
-- NK4XGRID: Dynamic HTML/CSV Grid Generator
------------------------------------------------------------------

------------------------------------------------------------------
-- Collections, Records, Variables, Constants, Exceptions, Cursors
------------------------------------------------------------------
gv_package_name constant varchar2(30) := 'NK4XGRID';

-- Subtypes
subtype dbms_sql_array is dbms_sql.varchar2a;

-- Current cursor
gv_cursor integer;

-- Allowed column title chars
gv_allowedchars constant varchar2(256) := 'abcdefghijklmnopqrstuvwxyz' ||
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ||
'~!@#$%^&*()_+-=/|\?,[.]{;}<:>0123456789' ||
'ÀÁÂÃÄàáâãäÈÉÊËèéêëÌÍÎÏìíîïÒÓÔÕÖòóôõöÙÚÛÜùúûü' ||
'ÇçÑñªº®£ƒ¥" ';

gv_grid_stmt_1 constant varchar2(512) := '
declare
lv__ constant varchar2(100) := ''NK4XGRID.GV_GRID_STMT'';
lv_cursor constant integer := :lv_cursor;
lv_format constant varchar2(255) := :lv_format;
lt_grid nk4xgrid.grid_array;
lt_template nk4xgrid.grid_columns;';
--LT_DECLARE
gv_grid_stmt_2 varchar2(512) := '
lv_rows integer;
i binary_integer := 2;
begin';
--LT_TEMPLATE
--LT_TITLE
--LT_DEFINE
gv_grid_stmt_3 constant varchar2(512) := '
lv_rows := dbms_sql.execute(lv_cursor);
while (dbms_sql.fetch_rows(lv_cursor) > 0) loop';
--LT_COLVAL
--LT_GRIDDING
gv_grid_stmt_4 constant varchar2(1024) := '
i := i + 1;
end loop;
if (lv_format = nk4xgrid.csv_format) then
nk4xgrid.csv_default(ft_buffer => nk4xgrid.gt_public_buffer,
ft_grid => lt_grid);
else
nk4xgrid.html_default(ft_buffer => nk4xgrid.gt_public_buffer,
ft_grid => lt_grid,
ft_template => lt_template);
end if;
lt_grid.delete;
lt_template.delete;
exception when others then
raise_application_error(-20904, lv__ || chr(10) || sqlerrm);
end;';

-- HTML Data
gv_html_begin constant varchar2(4096) :=
'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html><head><meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
<meta http-equiv="Expires" content="Thu, 01 Jan 1970 00:00:00 GMT">
<title>Nulaya Knowledge - GRID Generator</title>
<style type="text/css">
BODY {
Font-Family : Tahoma, Verdana, Helvetica, Sans-Serif;
Font-Size : 8pt;
Font-Weight : Normal;
Color : rgb(51, 102, 204);
Margin : 0px 0px 0px 0px;
Padding : 0px 0px 0px 0px;
Border : 0px solid #006400;
Background-Color : #FFFFFF; }
TABLE {
Float : Left;
Margin : 0px 0px 0px 0px;
Padding : 0px 0px 0px 0px;
Border : 0px solid rgb(51, 102, 204); }
TD {
Text-Align : Left;
Font-Family : MS Sans Serif, Arial, Verdana, Courier New, Courier, Monospace;
Font-Size : 8pt;
Font-Weight : Normal;
Color : rgb(0, 0, 0);
Vertical-Align : Top;
Border-Right : 1px solid rgb(220, 220, 220);
Border-Bottom : 1px solid rgb(220, 220, 220);
Padding : 0px 2px 2px 2px;
White-Space : NoWrap;
Background-Color : rgb(255, 255, 255); }
TD.hl {
Font-Family : Tahoma;
Font-Size : 10pt;
Font-Weight : Bold;
Padding : 2px 4px 0px 4px;
Background-Color : rgb(204, 255, 255); }
TD.hr {
Text-Align : Right;
Font-Family : Tahoma;
Font-Size : 10pt;
Font-Weight : Bold;
Padding : 2px 4px 0px 4px;
Background-Color : rgb(204, 255, 255); }
TD.ol {
Text-Align : Left;
Background-Color : rgb(255, 255, 255); }
TD.or {
Text-Align : Right;
Background-Color : rgb(255, 255, 255); }
TD.el {
Text-Align : Left;
Background-Color : rgb(255, 255, 255); }
TD.er {
Text-Align : Right;
Background-Color : rgb(255, 255, 255); }
PRE {
Font-Family : Courier New, Courier, Monospace;
Font-Size : 8pt;
Color : rgb(0, 0, 0);
Margin-Left : 6px;
Background-Color : rgb(255, 255, 237); }
</style></head><table cellspacing="0" cellpadding="0"><tbody>' || chr(10);
gv_tr_open constant varchar2(25) := '<tr>' || chr(10);
gv_thl constant varchar2(25) := '<td class="hl">'; -- Table Header: left justified
gv_thr constant varchar2(25) := '<td class="hr">'; -- Table Header: right justified
gv_tdol constant varchar2(25) := '<td class="ol">'; -- Table Data: odd row left justified
gv_tdor constant varchar2(25) := '<td class="or">'; -- Table Data: odd row right justified
gv_tdel constant varchar2(25) := '<td class="el">'; -- Table Data: even row left justified
gv_tder constant varchar2(25) := '<td class="er">'; -- Table Data: even row right justified
gv_td_close constant varchar2(25) := '</td>' || chr(10);
gv_tr_close constant varchar2(25) := '</tr>' || chr(10);
gv_html_end constant varchar2(25) := '</tbody></body></html>' || chr(10);

-- Internals
gv_format varchar(10);
gv_columns integer;
gt_buffer buffer_array;
gt_template grid_columns;

------------------------------------------------------------------
----------------------- Private Session --------------------------
------------------------------------------------------------------

------------------------------------------------------------------
-- FILTER_PVT - Remove any characters that wasn't allowed
------------------------------------------------------------------
procedure filter_pvt(fv_text in out nocopy varchar2,
fv_allowedchars in varchar2 := gv_allowedchars)
is
lv__ constant varchar2(100) := gv_package_name || '.FILTER_PVT: ';
lv_allowedchar constant varchar2(1) := substrb(fv_allowedchars, 1, 1);
lv_notallowedchars constant varchar2(32767) := replace(translate(fv_text, fv_allowedchars, lv_allowedchar), lv_allowedchar, null);
lv_nacslength constant integer := nvl(lengthb(lv_notallowedchars),0);
lv_notallowedchar constant varchar2(1) := substrb(lv_notallowedchars, 1, 1);
begin
if (lv_nacslength > 0) then
fv_text := replace(translate(fv_text, lv_notallowedchars,
rpad(lv_notallowedchar, lv_nacslength, lv_notallowedchar)), lv_notallowedchar, null);
end if;
exception when others then
raise_application_error(-20904, lv__ || chr(10) || sqlerrm);
end filter_pvt;

------------------------------------------------------------------
-- CLEANOUT_PVT
------------------------------------------------------------------
procedure cleanout_pvt
is
lv__ constant varchar2(100) := gv_package_name || '.CLEANOUT_PVT: ';
begin
gv_format := null;
gv_columns := null;
gt_buffer.delete;
gt_template.delete;
exception when others then
raise_application_error(-20904, lv__ || chr(10) || sqlerrm);
end cleanout_pvt;

------------------------------------------------------------------
-- ESCAPE_TAGS_PVT
------------------------------------------------------------------
procedure escape_tags_pvt(fv_text in out nocopy varchar2,
fv_format in varchar2)
is
lv__ constant varchar2(100) := gv_package_name || '.ESCAPE_TAGS_PVT: ';
begin
if (fv_format = html_format) then
fv_text := replace(replace(replace(replace(fv_text, '<', '&' || 'lt;'), '>', '&' || 'gt;'), chr(9), ' '), chr(10), '<br>');
fv_text := nvl(fv_text, '&' || 'nbsp;');
elsif (fv_format = csv_format) then
fv_text := replace(fv_text, '"', '""');
end if;
exception when others then
raise_application_error(-20904, lv__ || chr(10) || sqlerrm);
end escape_tags_pvt;

------------------------------------------------------------------
-- GRID_SANITY_PVT
------------------------------------------------------------------
procedure grid_sanity_pvt(ft_grid in grid_array)
is
lv__ constant varchar2(100) := gv_package_name || '.GRID_SANITY_PVT: ';
i binary_integer;
j binary_integer;
haetsl boolean := TRUE; -- has all elements the same length
begin
i := ft_grid.first;
while (i is not null) loop
if (i = ft_grid.first) then
j := ft_grid(i).count;
elsif (j <> ft_grid(i).count) then
haetsl := FALSE;
exit;
end if;
i := ft_grid.next(i);
end loop;
if (not haetsl) then
raise_application_error(-20904, '*** Grid data has distinct sizes ***');
end if;
if (gv_format = html_format) then
if (j <> gt_template.count) then
raise_application_error(-20904, '*** Invalid template size ***');
end if;
end if;
exception when others then
raise_application_error(-20904, lv__ || chr(10) || sqlerrm);
end grid_sanity_pvt;

------------------------------------------------------------------
-- CSV_DEFAULT_PVT
------------------------------------------------------------------
procedure csv_default_pvt(ft_grid in out nocopy grid_array)
is
lv__ constant varchar2(100) := gv_package_name || '.CSV_DEFAULT_PVT: ';
lv_column varchar2(4000);
lv_row varchar2(32767);
i binary_integer;
j binary_integer;
k binary_integer;
begin
grid_sanity_pvt(ft_grid => ft_grid);
i := ft_grid.first;
while (i is not null) loop
j := ft_grid(i).first;
while (j is not null) loop
lv_column := ft_grid(i)(j);
escape_tags_pvt(fv_text => lv_column,
fv_format => csv_format);
if (j <> ft_grid(i).last) then
lv_row := lv_row || '"' || lv_column || '";';
else
lv_row := lv_row || '"' || lv_column || '"';
end if;
j := ft_grid(i).next(j);
end loop;
gt_buffer(nvl(gt_buffer.last,0) + 1) := lv_row;
lv_row := null;
k := i;
i := ft_grid.next(i);
ft_grid(k).delete;
end loop;
ft_grid.delete;
exception when others then
raise_application_error(-20904, lv__ || chr(10) || sqlerrm);
end csv_default_pvt;

------------------------------------------------------------------
-- HTML_DEFAULT_PVT
------------------------------------------------------------------
procedure html_default_pvt(ft_grid in out nocopy grid_array)
is
lv__ constant varchar2(100) := gv_package_name || '.HTML_DEFAULT_PVT: ';
lv_text varchar2(32767);
shift boolean := TRUE;
i binary_integer;
j binary_integer;
k binary_integer;
begin
gt_buffer(nvl(gt_buffer.last,0) + 1) := gv_html_begin;
i := ft_grid.first;
while (i is not null) loop
gt_buffer(nvl(gt_buffer.last,0) + 1) := gv_tr_open;
j := ft_grid(i).first;
while (j is not null) loop
lv_text := ft_grid(i)(j);
escape_tags_pvt(fv_text => lv_text,
fv_format => html_format);
if (i = ft_grid.first) then -- Header
if (gt_template(j) = justify_right) then
lv_text := gv_thr || lv_text || gv_td_close;
else
lv_text := gv_thl || lv_text || gv_td_close;
end if;
else -- Rows
if (shift) then -- Odd row
if (gt_template(j) = justify_right) then
lv_text := gv_tdor || lv_text || gv_td_close;
else
lv_text := gv_tdol || lv_text || gv_td_close;
end if;
else -- Even row
if (gt_template(j) = justify_right) then
lv_text := gv_tder || lv_text || gv_td_close;
else
lv_text := gv_tdel || lv_text || gv_td_close;
end if;
end if;
end if;
gt_buffer(nvl(gt_buffer.last,0) + 1) := lv_text;
j := ft_grid(i).next(j);
end loop;
gt_buffer(nvl(gt_buffer.last,0) + 1) := gv_tr_close;
if (shift) then
shift := FALSE;
else
shift := TRUE;
end if;
k := i;
i := ft_grid.next(i);
ft_grid(k).delete;
end loop;
ft_grid.delete;
exception when others then
raise_application_error(-20904, lv__ || chr(10) || sqlerrm);
end html_default_pvt;

------------------------------------------------------------------
-- POP_GRID_PVT
------------------------------------------------------------------
procedure pop_grid_pvt(fv_format in varchar2)
is
lv__ constant varchar2(100) := gv_package_name || '.POP_GRID_PVT: ';
lt_stmt dbms_sql_array;
lt_columns dbms_sql.desc_tab2;
lv_columns integer;
lv_title varchar2(255);
lt_declare buffer_array;
lt_template buffer_array;
lt_title buffer_array;
lt_define buffer_array;
lt_colval buffer_array;
lt_gridding buffer_array;
job2do boolean := TRUE;
lv_cursor integer;
lv_rows integer;
i binary_integer;
j binary_integer := 1;
begin
dbms_sql.describe_columns2(gv_cursor, lv_columns, lt_columns);
i := lt_columns.first;
while (i is not null) loop
if (lt_columns(i).col_type = varchar2_type) then
lt_declare(lt_declare.count+1) := ' lv_col' || to_char(i) || ' varchar2(' || to_char(lt_columns(i).col_max_len) || ');' || chr(10);
lt_template(lt_template.count+1) := ' lt_template(' || to_char(j) ||') := nk4xgrid.justify_left;' || chr(10);
lt_define(lt_define.count+1) := ' dbms_sql.define_column(lv_cursor, ' || to_char(i) || ', lv_col' || to_char(i) ||
', ' || to_char(lt_columns(i).col_max_len) || ');' || chr(10);
lt_gridding(lt_gridding.count+1) := ' lt_grid(i)(' || to_char(j) ||') := lv_col' || to_char(i) || ';' || chr(10);
elsif (lt_columns(i).col_type = number_type) then
lt_declare(lt_declare.count+1) := ' lv_col' || to_char(i) || ' number;' || chr(10);
lt_template(lt_template.count+1) := ' lt_template(' || to_char(j) ||') := nk4xgrid.justify_right;' || chr(10);
lt_define(lt_define.count+1) := ' dbms_sql.define_column(lv_cursor, ' || to_char(i) || ', lv_col' || to_char(i) || ');' || chr(10);
lt_gridding(lt_gridding.count+1) := ' lt_grid(i)(' || to_char(j) || ') := to_char(lv_col' || to_char(i) || ');' || chr(10);
elsif (lt_columns(i).col_type = date_type) then
lt_declare(lt_declare.count+1) := ' lv_col' || to_char(i) || ' date;' || chr(10);
lt_template(lt_template.count+1) := ' lt_template(' || to_char(j) ||') := nk4xgrid.justify_left;' || chr(10);
lt_define(lt_define.count+1) := ' dbms_sql.define_column(lv_cursor, ' || to_char(i) || ', lv_col' || to_char(i) ||
');' || chr(10);
lt_gridding(lt_gridding.count+1) := ' lt_grid(i)(' || to_char(j) ||') := to_char(lv_col' || to_char(i) || ', ' ||
'''DD/MM/YYYY HH24:MI:SS'');' || chr(10);
elsif (lt_columns(i).col_type = char_type) then
lt_declare(lt_declare.count+1) := ' lv_col' || to_char(i) || ' varchar2(' || to_char(lt_columns(i).col_max_len) || ');' || chr(10);
lt_template(lt_template.count+1) := ' lt_template(' || to_char(j) ||') := nk4xgrid.justify_left;' || chr(10);
lt_define(lt_define.count+1) := ' dbms_sql.define_column(lv_cursor, ' || to_char(i) || ', lv_col' || to_char(i) ||
', ' || to_char(lt_columns(i).col_max_len) || ');' || chr(10);
lt_gridding(lt_gridding.count+1) := ' lt_grid(i)(' || to_char(j) ||') := lv_col' || to_char(i) || ';' || chr(10);
else
job2do := FALSE;
end if;
if (job2do) then
lt_colval(lt_colval.count+1) := ' dbms_sql.column_value(lv_cursor, ' || to_char(i) || ', lv_col' || to_char(i) || ');' || chr(10);
lv_title := substrb(lt_columns(i).col_name, 1, 255);
filter_pvt(fv_text => lv_title,
fv_allowedchars => gv_allowedchars);
lt_title(lt_title.count+1) := ' lt_grid(1)(' || to_char(j) ||') := ''' || lv_title || ''';' || chr(10);
j := j + 1;
end if;
job2do := TRUE;
i := lt_columns.next(i);
end loop;
lt_stmt(lt_stmt.count+1) := gv_grid_stmt_1;
for n in 1 .. lt_declare.count loop
lt_stmt(lt_stmt.count+1) := lt_declare(n);
end loop;
lt_declare.delete;
lt_stmt(lt_stmt.count+1) := gv_grid_stmt_2;
for n in 1 .. lt_template.count loop
lt_stmt(lt_stmt.count+1) := lt_template(n);
end loop;
lt_template.delete;
for n in 1 .. lt_title.count loop
lt_stmt(lt_stmt.count+1) := lt_title(n);
end loop;
lt_title.delete;
for n in 1 .. lt_define.count loop
lt_stmt(lt_stmt.count+1) := lt_define(n);
end loop;
lt_define.delete;
lt_stmt(lt_stmt.count+1) := gv_grid_stmt_3;
for n in 1 .. lt_colval.count loop
lt_stmt(lt_stmt.count+1) := lt_colval(n);
end loop;
lt_colval.delete;
for n in 1 .. lt_gridding.count loop
lt_stmt(lt_stmt.count+1) := lt_gridding(n);
end loop;
lt_gridding.delete;
lt_stmt(lt_stmt.count+1) := gv_grid_stmt_4;
lv_cursor := dbms_sql.open_cursor;
dbms_sql.parse(lv_cursor, lt_stmt, lt_stmt.first, lt_stmt.last, false, dbms_sql.native);
dbms_sql.bind_variable(lv_cursor, 'lv_cursor', gv_cursor);
dbms_sql.bind_variable(lv_cursor, 'lv_format', nk4xgrid.html_format);
lv_rows := dbms_sql.execute(lv_cursor);
dbms_sql.close_cursor(lv_cursor);
exception when others then
if (dbms_sql.is_open(lv_cursor)) then
dbms_sql.close_cursor(lv_cursor);
end if;
raise_application_error(-20904, lv__ || chr(10) || sqlerrm);
end pop_grid_pvt;

------------------------------------------------------------------
------------------------ Public Session --------------------------
------------------------------------------------------------------

------------------------------------------------------------------
-- GRID
------------------------------------------------------------------
procedure grid(ft_buffer out buffer_array,
fv_stmt in varchar2,
fv_format in varchar2)
is
lv__ constant varchar2(100) := gv_package_name || '.GRID: ';
begin
if (fv_stmt is not null) then
gv_cursor := dbms_sql.open_cursor;
dbms_sql.parse(gv_cursor, fv_stmt, dbms_sql.native);
pop_grid_pvt(fv_format => fv_format);
dbms_sql.close_cursor(gv_cursor);
end if;
ft_buffer := gt_public_buffer;
exception when others then
if (dbms_sql.is_open(gv_cursor)) then
dbms_sql.close_cursor(gv_cursor);
end if;
raise_application_error(-20904, lv__ || chr(10) || sqlerrm);
end grid;

------------------------------------------------------------------
-- HTML_DEFAULT
------------------------------------------------------------------
procedure html_default(ft_buffer out buffer_array,
ft_grid in out nocopy grid_array,
ft_template in grid_columns)
is
lv__ constant varchar2(100) := gv_package_name || '.HTML_DEFAULT: ';
begin
cleanout_pvt;
gt_template := ft_template;
html_default_pvt(ft_grid => ft_grid);
ft_buffer := gt_buffer;
cleanout_pvt;
exception when others then
raise_application_error(-20904, lv__ || chr(10) || sqlerrm);
end html_default;

------------------------------------------------------------------
-- CSV_DEFAULT
------------------------------------------------------------------
procedure csv_default(ft_buffer out buffer_array,
ft_grid in out nocopy grid_array)
is
lv__ constant varchar2(100) := gv_package_name || '.CSV_DEFAULT: ';
begin
cleanout_pvt;
gv_format := csv_format;
csv_default_pvt(ft_grid => ft_grid);
ft_buffer := gt_buffer;
cleanout_pvt;
exception when others then
raise_application_error(-20904, lv__ || chr(10) || sqlerrm);
end csv_default;

end nk4xgrid;
/
show errors