Tutorials

-- This PL/SQL procedure will write records to an Excel File.
-- We will use UTL_FILE ( You need an init.ora parameter set for this to work correctly).
-- UTL_FILE allows us to write a file on the server.


create or replace
package spread_sheet as
--
type owaSylkArray is table of varchar2(2000);
--
procedure show(
sheet_file in utl_file.file_type,
sheet_query in varchar2,
sheet_parm_names in owaSylkArray default owaSylkArray(),
sheet_parm_values in owaSylkArray default owaSylkArray(),
sheet_sum_column in owaSylkArray default owaSylkArray(),
sheet_max_rows in number default 10000,
sheet_show_null_as in varchar2 default null,
sheet_show_grid in varchar2 default 'YES',
sheet_show_col_headers in varchar2 default 'YES',
sheet_font_name in varchar2 default 'Courier New',
sheet_widths in owaSylkArray default owaSylkArray(),
sheet_titles in owaSylkArray default owaSylkArray(),
sheet_strisheet_html in varchar2 default 'YES' );
--
procedure show(
sheet_filein utl_file.file_type,
sheet_cursor in integer,
sheet_sum_column in owaSylkArray default owaSylkArray(),
sheet_max_rows in number default 10000,
sheet_show_null_as in varchar2 default null,
sheet_show_grid in varchar2 default 'YES',
sheet_show_col_headers in varchar2 default 'YES',
sheet_font_name in varchar2 default 'Courier New',
sheet_widths in owaSylkArray default owaSylkArray(),
sheet_titles in owaSylkArray default owaSylkArray(),
sheet_strisheet_html in varchar2 default 'YES' );
--
end spread_sheet;
/
show error

create or replace
package body spread_sheet as
--
g_cvalue varchar2(32767);
g_desc_t dbms_sql.desc_tab;

type vc_arr is table of varchar2(2000) index by binary_integer;
g_lengths vc_arr;
g_sums vc_arr;
--
--

g_file utl_file.file_type;


procedure p( sheet_str in varchar2 )
is
begin
utl_file.put_line( g_file, sheet_str );
exception
when others then null;
end;

function build_cursor(
q in varchar2,
n in owaSylkArray,
v in owaSylkArray ) return integer is
c integer := dbms_sql.open_cursor;
i number := 1;
begin
dbms_sql.parse (c, q, dbms_sql.native);
loop
dbms_sql.bind_variable( c, n(i), v(i) );
i := i + 1;
end loop;
return c;
exception
when others then
return c;
end build_cursor;
--
--
function str_html ( line in varchar2 ) return varchar2 is
x varchar2(32767) := null;
in_html boolean := FALSE;
s varchar2(1);
begin
if line is null then
return line;
end if;
for i in 1 .. length( line ) loop
s := substr( line, i, 1 );
if in_html then
if s = '>' then
in_html := FALSE;
end if;
else
if s = '<' then
in_html := TRUE;
end if;
end if;
if not in_html and s != '>' then
x := x || s;
end if;
end loop;
return x;
end str_html;
--
function ite( b boolean,
t varchar2,
f varchar2 ) return varchar2 is
begin
if b then
return t;
else
return f;
end if;
end ite;
--
procedure print_comment( sheet_comment varchar2 ) is
begin
return;
p( ';' || chr(10) || '; ' || sheet_comment || chr(10) || ';' );
end print_comment;
--
procedure print_heading( font in varchar2,
grid in varchar2,
col_heading in varchar2,
titles in owaSylkArray )
is
l_title varchar2(2000);
begin
p( 'ID;ORACLE' );
print_comment( 'Fonts' );
p( 'P;F' || font || ';M200' );
p( 'P;F' || font || ';M200;SB' );
p( 'P;F' || font || ';M200;SUB' );
--
print_comment( 'Global Formatting' );
p( 'F;C1;FG0R;SM1' ||
ite( upper(grid)='YES', '', ';G' ) ||
ite( upper(col_heading)='YES', '', ';H' ) );
for i in 1 .. g_desc_t.count loop
p( 'F;C' || to_char(i+1) || ';FG0R;SM0' );
end loop;
--
print_comment( 'Title Row' );
p( 'F;R1;FG0C;SM2' );
for i in 1 .. g_desc_t.count loop
g_lengths(i) := g_desc_t(i).col_name_len;
g_sums(i) := 0;
begin
l_title := titles(i);
exception
when others then
l_title := g_desc_t(i).col_name;
end;
if i = 1 then
p( 'C;Y1;X2;K"' || l_title || '"' );
else
p( 'C;X' || to_char(i+1) || ';K"' || l_title || '"' );
end if;
end loop;
end print_heading;
--
function print_rows(
c in integer,
max_rows in number,
sum_columns in owaSylkArray,
show_null_as in varchar2,
strisheet_html in varchar2 ) return number is
row_cnt number := 0;
line varchar2(32767) := null;
n number;
begin
loop
exit when ( row_cnt >= max_rows or
dbms_sql.fetch_rows( c ) <= 0 );
row_cnt := row_cnt + 1;
print_comment( 'Row ' || row_cnt );
--
p( 'C;Y' || to_char(row_cnt+2) );

for i in 1 .. g_desc_t.count loop
dbms_sql.column_value( c, i, g_cvalue );
g_cvalue := translate( g_cvalue,
chr(10)||chr(9)||';', ' ' );
g_cvalue := ite( upper( strisheet_html ) = 'YES',
str_html( g_cvalue ),
g_cvalue );
g_lengths(i) := greatest( nvl(length(g_cvalue),
nvl(length(show_null_as),0)),
g_lengths(i) );
line := 'C;X' || to_char(i+1);
line := line || ';K';
begin
n := to_number( g_cvalue );
if upper( sum_columns(i)) = 'Y' then
g_sums(i) := g_sums(i) + nvl(n,0);
end if;
exception
when others then
n := null;
end;
line := line ||
ite( n is null,
ite( g_cvalue is null,
'"'||show_null_as||
'"', '"'||g_cvalue||'"' ),
n );
p( line );
end loop;
--
end loop;
return row_cnt;
end print_rows;
--
procedure print_sums(
sum_columns in owaSylkArray,
row_cnt in number ) is
begin
if sum_columns.count = 0 then
return;
end if;
--
print_comment( 'Totals Row' );
p( 'C;Y' || to_char(row_cnt + 4) );
p( 'C;X1;K"Totals:"' );
--
for i in 1 .. g_desc_t.count loop
begin
if upper(sum_columns(i)) = 'Y' then
p( 'C;X' || to_char(i+1) || ';ESUM(R3C:R' ||
to_char(row_cnt+2) || 'C)' );
end if;
exception
when others then
null;
end;
end loop;
end print_sums;
--
procedure print_widths( widths owaSylkArray ) is
begin
print_comment( 'Format Column Widths' );
p( 'F;W1 1 7' );
for i in 1 .. g_desc_t.count loop
begin
p( 'F;W' || to_char(i+1) || ' ' ||
to_char(i+1) || ' ' ||
to_char(to_number(widths(i))) );
exception
when others then
p( 'F;W' || to_char(i+1) || ' ' ||
to_char(i+1) || ' ' ||
greatest( g_lengths(i), length( g_sums(i) )));
end;
end loop;
p( 'E' );
end print_widths;
procedure show(
sheet_filein utl_file.file_type,
sheet_cursor in integer,
sheet_sum_column in owaSylkArray default owaSylkArray(),
sheet_max_rows in number default 10000,
sheet_show_null_as in varchar2 default null,
sheet_show_grid in varchar2 default 'YES',
sheet_show_col_headers in varchar2 default 'YES',
sheet_font_name in varchar2 default 'Courier New',
sheet_widths in owaSylkArray default owaSylkArray(),
sheet_titles in owaSylkArray default owaSylkArray(),
sheet_strisheet_html in varchar2 default 'YES' ) is
--
l_row_cnt number;
l_col_cnt number;
l_status number;
begin
g_file := sheet_file;
dbms_sql.describe_columns( sheet_cursor, l_col_cnt, g_desc_t );
--
for i in 1 .. g_desc_t.count loop
dbms_sql.define_column( sheet_cursor, i, g_cvalue, 32765);
end loop;
--
print_heading( sheet_font_name,
sheet_show_grid,
sheet_show_col_headers,
sheet_titles );
l_status := dbms_sql.execute( sheet_cursor );
l_row_cnt := print_rows(
sheet_cursor,
sheet_max_rows,
sheet_sum_column,
sheet_show_null_as,
sheet_strisheet_html );
print_sums( sheet_sum_column, l_row_cnt );
print_widths( sheet_widths );
end show;
--
procedure show(
sheet_filein utl_file.file_type,
sheet_query in varchar2,
sheet_parm_names in owaSylkArray default owaSylkArray(),
sheet_parm_values in owaSylkArray default owaSylkArray(),
sheet_sum_column in owaSylkArray default owaSylkArray(),
sheet_max_rows in number default 10000,
sheet_show_null_as in varchar2 default null,
sheet_show_grid in varchar2 default 'YES',
sheet_show_col_headers in varchar2 default 'YES',
sheet_font_name in varchar2 default 'Courier New',
sheet_widths in owaSylkArray default owaSylkArray(),
sheet_titles in owaSylkArray default owaSylkArray(),
sheet_strisheet_html in varchar2 default 'YES' ) is
begin
show( sheet_file => sheet_file,
sheet_cursor => build_cursor( sheet_query,
sheet_parm_names,
sheet_parm_values ),
sheet_sum_column => sheet_sum_column,
sheet_max_rows => sheet_max_rows,
sheet_show_null_as => sheet_show_null_as,
sheet_show_grid => sheet_show_grid,
sheet_show_col_headers => sheet_show_col_headers,
sheet_font_name => sheet_font_name,
sheet_widths => sheet_widths,
sheet_titles => sheet_titles,
sheet_strisheet_html => sheet_strisheet_html );
end show;
--
end spread_sheet;
/
show error
declare
l_cursor number := dbms_sql.open_cursor;
output utl_file.file_type;
begin
output := utl_file.fopen( 'c:\temp\', 'emp2.slk', 'w',32000 );

dbms_sql.parse( l_cursor,
'select empno id, ename employee,
sal Salary, comm commission ' ||
'from scott.emp ' ||
'where job = ''MANAGER'' ' ||
'and sal > 2000',
dbms_sql.native );

spread_sheet.show(
sheet_file => output ,
sheet_cursor => l_cursor,
sheet_sum_column =>
spread_sheet.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
sheet_show_grid => 'NO' );
dbms_sql.close_cursor( l_cursor );
utl_file.fclose( output );
end;