p_file & p_user_heading
Stephen Wood, March 09, 2004 - 5:16 am UTC
Good code from Chris Beck but it removes the p_file parameter making the original example script using p_file not work. If anyone's interested here's a version that has both p_file and p_user_heading parameters.
create or replace
package owa_sylk as
--
type owaSylkArray is table of varchar2(2000);
--
procedure show(
p_file in utl_file.file_type,
p_query in varchar2,
p_parm_names in owaSylkArray default owaSylkArray(),
p_parm_values in owaSylkArray default owaSylkArray(),
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES',
p_user_heading in varchar2 default null );
--
procedure show(
p_file in utl_file.file_type,
p_cursor in integer,
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES',
p_user_heading in varchar2 default null );
--
end owa_sylk;
/
create or replace
package body owa_sylk as
--
g_cvalue varchar2(32767);
g_desc_t dbms_sql.desc_tab;
g_lengths owa.vc_arr;
g_sums owa.vc_arr;
g_user_heading number := 0;
g_file utl_file.file_type;
--
--
procedure p( p_str in varchar2 )
is
begin
utl_file.put_line( g_file, p_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( p_comment varchar2 ) is
begin
return;
p( ';' || chr(10) || '; ' || p_comment || chr(10) || ';' );
end print_comment;
--
procedure print_heading( font in varchar2,
grid in varchar2,
col_heading in varchar2,
titles in owaSylkArray,
htext varchar2 default null )
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;
--
if htext is not null then
print_comment( 'User Heading' );
g_user_heading := 3;
p( 'F;R1;FG0L;SM0' );
p( 'C;Y1;X1;K"' || htext || '"' );
end if;
--
print_comment( 'Title Row' );
p( 'F;R' || to_char(1+g_user_heading) || ';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;Y' || to_char(1+g_user_heading) || ';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,
strip_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+1+g_user_heading) );
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( strip_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 + g_user_heading) );
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(R' || to_char(2+g_user_heading) ||
'C:R' ||
to_char(row_cnt+2+g_user_heading) || '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(
p_file in utl_file.file_type,
p_cursor in integer,
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES',
p_user_heading in varchar2 default null ) is
--
l_row_cnt number;
l_col_cnt number;
l_status number;
begin
g_file := p_file;
dbms_sql.describe_columns( p_cursor, l_col_cnt, g_desc_t );
--
for i in 1 .. g_desc_t.count loop
dbms_sql.define_column( p_cursor, i, g_cvalue, 32765);
end loop;
--
print_heading( p_font_name,
p_show_grid,
p_show_col_headers,
p_titles,
p_user_heading );
l_status := dbms_sql.execute( p_cursor );
l_row_cnt := print_rows(
p_cursor,
p_max_rows,
p_sum_column,
p_show_null_as,
p_strip_html );
print_sums( p_sum_column, l_row_cnt );
print_widths( p_widths );
end show;
--
procedure show(
p_file in utl_file.file_type,
p_query in varchar2,
p_parm_names in owaSylkArray default owaSylkArray(),
p_parm_values in owaSylkArray default owaSylkArray(),
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES',
p_user_heading in varchar2 default null ) is
begin
show( p_file => p_file,
p_cursor => build_cursor( p_query,
p_parm_names,
p_parm_values ),
p_sum_column => p_sum_column,
p_max_rows => p_max_rows,
p_show_null_as => p_show_null_as,
p_show_grid => p_show_grid,
p_show_col_headers => p_show_col_headers,
p_font_name => p_font_name,
p_widths => p_widths,
p_titles => p_titles,
p_strip_html => p_strip_html,
p_user_heading => p_user_heading );
end show;
--
end owa_sylk;
/
p_file & p_user_heading
Stephen Wood, March 09, 2004 - 5:18 am UTC
Good code from Chris Beck but it removes the p_file parameter making the original example script using p_file not work. If anyone's interested here's a version that has both p_file and p_user_heading parameters.
create or replace
package owa_sylk as
--
type owaSylkArray is table of varchar2(2000);
--
procedure show(
p_file in utl_file.file_type,
p_query in varchar2,
p_parm_names in owaSylkArray default owaSylkArray(),
p_parm_values in owaSylkArray default owaSylkArray(),
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES',
p_user_heading in varchar2 default null );
--
procedure show(
p_file in utl_file.file_type,
p_cursor in integer,
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES',
p_user_heading in varchar2 default null );
--
end owa_sylk;
/
create or replace
package body owa_sylk as
--
g_cvalue varchar2(32767);
g_desc_t dbms_sql.desc_tab;
g_lengths owa.vc_arr;
g_sums owa.vc_arr;
g_user_heading number := 0;
g_file utl_file.file_type;
--
--
procedure p( p_str in varchar2 )
is
begin
utl_file.put_line( g_file, p_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( p_comment varchar2 ) is
begin
return;
p( ';' || chr(10) || '; ' || p_comment || chr(10) || ';' );
end print_comment;
--
procedure print_heading( font in varchar2,
grid in varchar2,
col_heading in varchar2,
titles in owaSylkArray,
htext varchar2 default null )
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;
--
if htext is not null then
print_comment( 'User Heading' );
g_user_heading := 3;
p( 'F;R1;FG0L;SM0' );
p( 'C;Y1;X1;K"' || htext || '"' );
end if;
--
print_comment( 'Title Row' );
p( 'F;R' || to_char(1+g_user_heading) || ';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;Y' || to_char(1+g_user_heading) || ';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,
strip_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+1+g_user_heading) );
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( strip_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 + g_user_heading) );
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(R' || to_char(2+g_user_heading) ||
'C:R' ||
to_char(row_cnt+2+g_user_heading) || '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(
p_file in utl_file.file_type,
p_cursor in integer,
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES',
p_user_heading in varchar2 default null ) is
--
l_row_cnt number;
l_col_cnt number;
l_status number;
begin
g_file := p_file;
dbms_sql.describe_columns( p_cursor, l_col_cnt, g_desc_t );
--
for i in 1 .. g_desc_t.count loop
dbms_sql.define_column( p_cursor, i, g_cvalue, 32765);
end loop;
--
print_heading( p_font_name,
p_show_grid,
p_show_col_headers,
p_titles,
p_user_heading );
l_status := dbms_sql.execute( p_cursor );
l_row_cnt := print_rows(
p_cursor,
p_max_rows,
p_sum_column,
p_show_null_as,
p_strip_html );
print_sums( p_sum_column, l_row_cnt );
print_widths( p_widths );
end show;
--
procedure show(
p_file in utl_file.file_type,
p_query in varchar2,
p_parm_names in owaSylkArray default owaSylkArray(),
p_parm_values in owaSylkArray default owaSylkArray(),
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES',
p_user_heading in varchar2 default null ) is
begin
show( p_file => p_file,
p_cursor => build_cursor( p_query,
p_parm_names,
p_parm_values ),
p_sum_column => p_sum_column,
p_max_rows => p_max_rows,
p_show_null_as => p_show_null_as,
p_show_grid => p_show_grid,
p_show_col_headers => p_show_col_headers,
p_font_name => p_font_name,
p_widths => p_widths,
p_titles => p_titles,
p_strip_html => p_strip_html,
p_user_heading => p_user_heading );
end show;
--
end owa_sylk;
/
p_widths in OWA_SYLK
atul, April 10, 2007 - 11:30 pm UTC
Hi,
I am using user very very useful Owa_sylk utility.
I am getting data in Excel in strating column without break .
test,test,test,test....
I want it to look like
test,test,test
test,test,test
sp i think i have to incrase p_width to fit it like i want.
I tried passing p_width in below
declare
l_cursor number := dbms_sql.open_cursor;
output utl_file.file_type;
begin
output := utl_file.fopen( '/tmp', 'test2.slk', 'w',32000);
dbms_sql.parse( l_cursor,
'select get_transposed(name) PRG_Summary from emp where rownum=1',
dbms_sql.native );
owa_sylk.show(
p_file => output ,
p_cursor => l_cursor,
p_show_grid => 'YES' ,
p_widths => 80
dbms_sql.close_cursor( l_cursor );
utl_file.fclose( output );
end;
But it is saying wrong arguments..Could you please help
April 11, 2007 - 11:11 am UTC
sorry - no idea what you are trying to do or what you are trying to do it against.
Excel to Oracle
Farhan Ashraf, December 04, 2007 - 12:43 am UTC
How to write a pl/sql procedure to transfer data from excel to oracle table (without converting the excel
file to .csv file OR not coverting excel --->dbase---->oracle OR without using sqlloader or import utility ) ?