Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Shruti.

Asked: September 14, 2000 - 10:30 am UTC

Last updated: February 14, 2013 - 7:37 am UTC

Version: 4.0.7

Viewed 10K+ times! This question is

You Asked

Hi TOM, I have seen your hints about the above utility.

I am trying to query a table dynamically and trying to get the out put via Oracle application server into my client machines excel.

My server is HPunix.and I am trying get the output via my windows 98 machine


Follwoing hint you have given

Create or replace procedure emp_ss
as
begin
owa_sylk.show('select * from emp');
end;
But owa_sylk I am using has diffrent number of arguments for procedure show. Please point me to right one.

Please let me know if I am on right track.

Thanks,
Shruti

and Tom said...

Ok, the owa_sylk I put up for another question was for output to a file on disk. Here is the original one for output over a webserver. First the example:

ops$tkyte@ORA8I.WORLD> begin
2 owa_util.mime_header( 'application/excel' );
3 owa_sylk.show(
4 p_query => 'select empno id, ename employee,
5 sal Salary, comm commission ' ||
6 'from scott.emp ' ||
7 'where job = :JOB ' ||
8 'and sal > :SAL',
9 p_parm_names =>
10 owa_sylk.owaSylkArray( 'JOB', 'SAL'),
11 p_parm_values =>
12 owa_sylk.owaSylkArray( 'MANAGER', '2000' ),
13 p_sum_column =>
14 owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y'),
15 p_show_grid => 'NO' );
16 end;
17 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> exec owa_util.showpage
Content-type: application/excel

ID;ORACLE
P;FCourier New;M200
P;FCourier New;M200;SB
P;FCourier
New;M200;SUB
F;C1;FG0R;SM1;G
F;C2;FG0R;SM0
F;C3;FG0R;SM0
F;C4;FG0R;SM0
F;C5;FG0R;SM0
F;R1;FG0C;SM2
C
;Y1;X2;K"ID"
C;X3;K"EMPLOYEE"
C;X4;K"SALARY"
SION"
C;Y3
C;X2;K7782
C;X3;K"CLARK"
C;X4;K2450
C;X5;K""
C;Y4
C;X2;K7698
C;X3;K"BLAKE"
C;X4;K2850
C;X
5;K""
C;Y5
C;X2;K7566
C;X3;K"JONES"
C;X4;K2975
C;X5;K""
C;Y7
C;X1;K"Totals:"
C;X4;ESUM(R3C:R5C)
C;X5
;ESUM(R3C:R5C)
F;W1 1 7
F;W2 2 4
F;W3 3 8
F;W4 4 6
5 10
E

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> declare
2 l_cursor integer default dbms_sql.open_cursor;
3 begin
4 dbms_sql.parse( l_cursor,
5 'select empno id, ename employee,
6 sal Salary, comm commission ' ||
7 'from scott.emp ' ||
8 'where job = ''MANAGER'' ' ||
9 'and sal > 2000',
10 dbms_sql.native );
11
12 owa_util.mime_header( 'application/excel' );
13 owa_sylk.show(
14 p_cursor => l_cursor,
15 p_sum_column =>
16 owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
17 p_show_grid => 'NO' );
18 dbms_sql.close_cursor( l_cursor );
19 end;
20 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> exec owa_util.showpage
Content-type: application/excel

ID;ORACLE
P;FCourier New;M200
P;FCourier New;M200;SB
P;FCourier
New;M200;SUB
F;C1;FG0R;SM1;G
F;C2;FG0R;SM0
F;C3;FG0R;SM0
F;C4;FG0R;SM0
F;C5;FG0R;SM0
F;R1;FG0C;SM2
C
;Y1;X2;K"ID"
C;X3;K"EMPLOYEE"
C;X4;K"SALARY"
SION"
C;Y3
C;X2;K7782
C;X3;K"CLARK"
C;X4;K2450
C;X5;K""
C;Y4
C;X2;K7698
C;X3;K"BLAKE"
C;X4;K2850
C;X
5;K""
C;Y5
C;X2;K7566
C;X3;K"JONES"
C;X4;K2975
C;X5;K""
C;Y7
C;X1;K"Totals:"
C;X4;ESUM(R3C:R5C)
C;X5
;ESUM(R3C:R5C)
F;W1 1 7
F;W2 2 4
F;W3 3 8
F;W4 4 6
5 10
E

PL/SQL procedure successfully completed.


You have to call owa_util.mime_header first with the appropriate mime type and then it does the rest. Here is the code:


Rem
Rem $Id$
Rem
Rem Copyright (c) 1991, 1996, 1997 by Oracle Corporation
Rem NAME
Rem owasylk.sql - Dump to Spreadsheet with formatting
Rem DESCRIPTION
Rem This package provides an API to generate a file in the
Rem SYLK file format. This allow for formatting in a
Rem spreadsheet with only a ascii text file. This version
Rem of owa_sylk is specific to Oracle8.
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem clbeck 04/08/98 - Created.
Rem tkyte 09/10/00 - Made it use UTL_FILE.
Rem
Rem

/*
This package allows you to send the results of any query to
a spreadsheet using UTL_FILE

parameters:
p_query - a text string of the query. The query
can be parameterized
using the :VARAIBLE syntax. See example
below.

p_parm_names - an owaSylkArray of the paramter names
used as bind variables in p_query

p_parm_values - an owaSylkArray of the values of the
bind variable names. The values
muse reside in the same index as the
name it corresponds to.

p_cursor - an open cursor that has had the query
parsed already.

p_sum_column - a owaSylkArray of 'Y's and 'N's
corresponding to the location
of the columns selected in p_query.
A value of NYNYY will result
in the 2nd, 4th and 5th columns being
summed in the resulting
spreadsheet.

p_max_rows - the maxium number of row to return.

p_show_null_as - how to display nulls in the spreadsheet

p_show_grid - show/hide the grid in the spreadsheet.

p_show_col_headers - show/hide the row/column headers
in the spreadsheet.

p_font_name - the name of the font

p_widths - a owaSylkArray of column widths. This
will override the default column widths.

p_headings - a owaSylkArray of column titles.
This will override the default column
titles.

p_strip_html - this will remove the HTML tags from the
results before
displaying them in the spreadsheet cells.
Useful when the
query selects an anchor tag. Only the
text between <a href>
and </a> tags will be sent to the
spreadsheet.

examples:

This example will create a spreadsheet of all the MANAGERS
in the scott.emp table and will sum up the salaries
and commissions for them. No grid will be in the
spreadsheet.


begin
owa_sylk.show(
p_query => 'select empno id, ename employee,
sal Salary, comm commission ' ||
'from scott.emp ' ||
'where job = :JOB ' ||
'and sal > :SAL',
p_parm_names => owa_sylk.owaSylkArray( 'JOB', 'SAL' ),
p_parm_values =>
owa_sylk.owaSylkArray( 'MANAGER', '2000' ),
p_sum_column =>
owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
p_show_grid => 'NO' );
end;

This example will create the same spreadsheet but will
send in a pre-parsed cursor instead

declare
l_cursor number := dbms_sql.open_cursor;
begin
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 );
owa_sylk.show(
p_cursor => l_cursor,
p_sum_column =>
owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
p_show_grid => 'NO' );
dbms_sql.close_cursor( l_cursor );
end;

*/

create or replace
package owa_sylk as
--
type owaSylkArray is table of varchar2(2000);
--
procedure show(
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' );
--
procedure show(
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' );
--
end owa_sylk;
/
show error

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;
--
--

procedure p( p_str in varchar2 )
is
begin
htp.p( 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 )
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,
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+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( 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) );
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(
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' ) is
--
l_row_cnt number;
l_col_cnt number;
l_status number;
begin
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 );
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_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' ) is
begin
show( 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 );
end show;
--
end owa_sylk;
/
show error




Rating

  (48 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

what about Image

Reader, January 22, 2002 - 10:11 am UTC

Hi Tom,

Can you tell me how can I put my company's logo in
thro' this utility ?

Thanks,


How to call?

Srinivas, February 05, 2002 - 8:44 am UTC

How can I call this from application?

Page break

Pkgupta, September 06, 2002 - 3:43 pm UTC

Great Package.

In addition to this, is there any way to force a page break. Like after each Total -- Title row will be again.

Thanks in advance

Tom Kyte
September 06, 2002 - 3:56 pm UTC

You got the code, you can make it do *whatever* you want.

Small Problem

Priyaramnan, October 21, 2002 - 11:42 pm UTC

if u could kindly solve my prblem it will be of much use.
i'm getting an error if i run this procedure

declare
*
ERROR at line 1:
ORA-01003: no statement parsed
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1476
ORA-06512: at "SYS.DBMS_SQL", line 614
ORA-06512: at "DEPOSITS.OWA_SYLK", line 233
ORA-06512: at "DEPOSITS.OWA_SYLK", line 269
ORA-06512: at line 5

how to solve this

Tom Kyte
October 22, 2002 - 7:10 am UTC

so, what was the INPUT to this call.

What have you done (given that you actually have 100% of the source code and its not really complex) to debug this?

owa_util.showpage wrap lines containing spaces

Rui Brito, December 06, 2002 - 10:30 am UTC

Hi Tom
I create a .SLK file using the owa_util.show.
After this I made a spool to a file and call the owa_util.showpage. All strings with spaces between words are wrapped. I open the showpage source and see a instr(' ') inside this, even the line < 255 length. I can avoid this and put all string in same line ?

Example:
K"James Brown and Company" appears like
K"James Brown and
Company"

When I open the file in excel, they give a error

Thanks
Rui Brito


Tom Kyte
December 06, 2002 - 11:20 am UTC

use the UTL_FILE version of owa_sylk instead of the WEB version of owa_sylk.

showpage just dumps using dbms_output and splits (word wraps) where it feels like .

isn't work with data greater than 255 chars

Juan Carlos Erazo M., March 04, 2003 - 3:19 pm UTC

Hi, the web version sis very useful, but isn't working whit data greater than 255 characters. Let me explain: my select sentence return some column with more than 255 characters. When this happens Internet Explorer says: "Theres is a problem displaying record 148".

Could i fix it?

Tom Kyte
March 04, 2003 - 6:52 pm UTC

I don't know, can you?

I don't see what IE has to do with anything as it isn't a spreadsheet.

Error

Pavan Chelvaraj, April 03, 2003 - 6:39 pm UTC

Hi Tom

Can you please let me know why this error has occured.

  1  begin
  2  owa_util.mime_header( 'application/excel' );
  3  owa_sylk.show(
  4  p_query => 'select empno id, ename employee,
  5  sal Salary, comm commission ' ||
  6  'from scott.emp ' ||
  7  'where job = :JOB ' ||
  8  'and sal > :SAL',
  9  p_parm_names =>
 10  owa_sylk.owaSylkArray( 'JOB', 'SAL'),
 11  p_parm_values =>
 12  owa_sylk.owaSylkArray( 'MANAGER', '2000' ),
 13  p_sum_column =>
 14  owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y'),
 15  p_show_grid => 'NO' );
 16* end;
SQL> /
owa_sylk.owaSylkArray( 'JOB', 'SAL'),
*
ERROR at line 10:
ORA-06550: line 10, column 1:
PLS-00201: identifier 'OWA_SYLK.OWASYLKARRAY' must be declared
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored 

Tom Kyte
April 03, 2003 - 9:21 pm UTC

because we named it owa_sylk, not owaSylk

Still the same error

Pavan Chelvaraj, April 04, 2003 - 1:43 am UTC

Hi Tom

I am still getting the error message. Can you please help me with that.

  1  begin
  2  owa_util.mime_header( 'application/excel' );
  3  owa_sylk.show(
  4  p_query => 'select empno id, ename employee,
  5  sal Salary, comm commission ' ||
  6  'from scott.emp ' ||
  7  'where job = :JOB ' ||
  8  'and sal > :SAL',
  9  p_parm_names =>
 10  owa_sylk.owa_SylkArray( 'JOB', 'SAL'),
 11  p_parm_values =>
 12  owa_sylk.owa_SylkArray( 'MANAGER', '2000' ),
 13  p_sum_column =>
 14  owa_sylk.owa_SylkArray( 'N', 'N', 'Y', 'Y'),
 15  p_show_grid => 'NO' );
 16* end;
SQL> /
owa_sylk.owa_SylkArray( 'JOB', 'SAL'),
*
ERROR at line 10:
ORA-06550: line 10, column 1:
PLS-00201: identifier 'OWA_SYLK.OWA_SYLKARRAY' must be declared
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored 

Tom Kyte
April 04, 2003 - 6:34 am UTC

wait, back up -- owa_sylk.owasylkarray is the datatype (sorry about that)

you did INSTALL THIS package *we* wrote -- that is not shipped with the product -- right???

Ahh -- i think that is it, you just have grabbed the code from above and actually installed it perhaps?

excellent.

Lebon Mathew, April 04, 2003 - 2:14 pm UTC

Two subquestions.

1) when I exeuted in sample I got p/sql error.

--------------------------------------------------------
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Apr 4 11:11:06 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied



Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> declare
  2          l_cursor integer default dbms_sql.open_cursor;
  3      begin
  4          dbms_sql.parse( l_cursor,
  5              'select empno id, ename employee,
  6                      sal Salary, comm commission ' ||
  7                'from scott.emp ' ||
  8                'where job = ''MANAGER'' ' ||
  9                'and sal > 2000',
 10             dbms_sql.native );
 11     
 12         owa_util.mime_header( 'application/excel' );
 13         owa_sylk.show(
 14             p_cursor => l_cursor,
 15             p_sum_column =>
 16                 owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
 17             p_show_grid => 'NO' );
 18         dbms_sql.close_cursor( l_cursor );
 19   end;
 20  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 323
ORA-06512: at "SYS.HTP", line 860
ORA-06512: at "SYS.OWA_UTIL", line 373
ORA-06512: at line 12


SQL> 
Any idea why ?
-----

2)  When I ran the same test on web. I created the above stuff as a procedure called TEST and called it 

http://myserver/pls/scott/test
... it is not opening in excel ..  
( however I see the  stuff like 
;Y1;X2;K"ID"
C;X3;K"EMPLOYEE"
C;X4;K"SALARY"
SION"
C;Y3
C;X2;K7782
C;X3;K"CLARK"
C;X4;K2450
C;X5;K""
 )

Can you please guide me on how to get the excel open on a browser and see it

Thank you
Lebon Mathew
 

Tom Kyte
April 04, 2003 - 6:32 pm UTC

1) the htp/owa packages were not initialized as they would be via mod_plsql.

you can run something like:

declare
nm owa.vc_arr;
vl owa.vc_arr;
begin
nm(1) := 'WEB_AUTHENT_PREFIX';
vl(1) := 'WEB$';
owa.init_cgi_env( nm.count, nm, vl );
end;
/

to fix that up.

2) what exactly is in test and what exactly is your browser configured to do with application/excel mime types.

Please Ignore my 2nd Question above ... I got the answer.

Lebon Mathew, April 04, 2003 - 5:34 pm UTC

I had to put like below in the mime type to solve my problem

OWA_UTIL.mime_header ('application/vnd.ms-excel');

Thank you

PLS-00201: identifier 'OWA_SYLK.OWASYLKARRAY' must be declared

Pavan Chelvaraj, April 06, 2003 - 7:41 pm UTC

SQL> declare
  2      nm  owa.vc_arr;
  3      vl  owa.vc_arr;
  4  begin
  5      nm(1) := 'WEB_AUTHENT_PREFIX';
  6      vl(1) := 'WEB$';
  7      owa.init_cgi_env( nm.count, nm, vl );
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> ED
Wrote file afiedt.buf

  1  declare
  2  l_cursor integer default dbms_sql.open_cursor;
  3  begin
  4  dbms_sql.parse( l_cursor,
  5  'select empno id, ename employee,
  6  sal Salary, comm commission ' ||
  7  'from scott.emp ' ||
  8  'where job = ''MANAGER'' ' ||
  9  'and sal > 2000',
 10  dbms_sql.native );
 11  owa_util.mime_header( 'application/excel' );
 12  owa_sylk.show(
 13  p_cursor => l_cursor,
 14  p_sum_column =>
 15  owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
 16  p_show_grid => 'NO' );
 17  dbms_sql.close_cursor( l_cursor );
 18* end;
SQL> /
owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
*
ERROR at line 15:
ORA-06550: line 15, column 1:
PLS-00201: identifier 'OWA_SYLK.OWASYLKARRAY' must be declared
ORA-06550: line 12, column 1:
PL/SQL: Statement ignored

I do have these Packages installed on my server. 
GENK_VAL_WEA_RI
GENK_VAL_WER_RI
GENK_VAL_WE_RI
SSFK_VAL_WE
SSFK_VAL_WEA
SSFK_VAL_WER

Is there anything else which I need to run or execute before that. 

Regards

Pavan Chelvaraj 

Tom Kyte
April 06, 2003 - 8:04 pm UTC

gee, do you have owa_sylk -- which as I said is something WE WROTE HERE -- it is not part of the product, it would not be installed unless you installed it.

It is very clear what the error is.

You do not have owa_sylk installed in your schema.

Get it (right above)

Install it.

OWA_SYLK

Pavan Chelvaraj, April 07, 2003 - 1:51 am UTC

Can you please let me know where do I find or download owa_sylk utility/package.
I do have owa_util installed on my db.

Cheers

Pavan

Tom Kyte
April 07, 2003 - 7:57 am UTC

tell you what Pavan.

Reread this page from the top to the bottom. In doing so you will read over the source code. I tried to tell you that a couple of times already.

my very last comment:
...
It is very clear what the error is.

You do not have owa_sylk installed in your schema.

Get it (right above)

Install it.
....

Hilarious

A reader, April 07, 2003 - 5:50 am UTC

Pavan,

Have you ever thought about a change of career?

HK

Sun, October 03, 2003 - 5:15 am UTC

Hello Tom,

I create owa_sylk and run it, it shows no error, but where can I find my spreadsheet file? Should I specific the path to store it ? Thanks!

SQL> ed
Wrote file afiedt.buf

  1  declare
  2        l_cursor number := dbms_sql.open_cursor;
  3      begin
  4        dbms_sql.parse(
  5          l_cursor,
  6          'SELECT id, employee,
  7                             Salary, comm from emp',
  8          dbms_sql.native );
  9        owa_sylk.show(
 10          p_cursor => l_cursor,
 11          p_sum_column =>
 12              owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
 13          p_show_grid => 'NO' );
 14        dbms_sql.close_cursor( l_cursor );
 15*     end;
SQL> /

PL/SQL procedure successfully completed.

SQL> show error;
No errors.
 

Tom Kyte
October 03, 2003 - 8:26 am UTC

this version of owa_sylk uses htp -- its for the web. it didn't create a file, it created "http output"

if you owa_util.showpage, it'll dump on your screen. if you search for owa_sylk utl_file you'll find one that does files.

Very very Urgent Pls Help me on this

Suresh, October 20, 2003 - 1:44 pm UTC


Hi tom,

i am generated excel-report through pl/sql procedure,
in that i am using some format-specifications like
htp.p('F;C1;FG0R;SM1;G'),htp.p('P;FArial;M200')....so on..
but i dont' know the exact meanning's of all these type of format specification....and how to Use...

but client is asking about look and feel must Perfect...
like borders of excel-must bold(related to some cells),some rows(Column heading must be bold),Backgroung colour of some rows,foreground colour of some rows and colums...likethat...
and you are using these type of format-specification in this page with in the procedures in so many places....

what each format specification means ,where to get these format specifications and how to use....?

pls reply immd... and send any documents or links available on this htp.p('....') format specifications...

It's very urgent..Pls reply these Questions ....

Regards,
Suresh.


Tom Kyte
October 20, 2003 - 2:19 pm UTC

google for sylk.

sylk is an industry standard file format.
it is documented.
That is just part of that file format.


so, search for SYLK on the web, nothing to do with the database or Oracle at this point, all about Sylk.

very very Urgent Tom ....excle formats

suresh, October 21, 2003 - 3:48 am UTC


Hi Tom,

In google.com i am not find out any format specifications...like htp.p('F;C2;FG0R;SM0');
htp.p('P;FArial;M200');htp.p('F;SDM6;R1')... so on..

what each format string means, how it will work...

pls help me on this topic..it's very urgent...
pls send any links & documents on this ..

Regards,
suresh.

Tom Kyte
October 21, 2003 - 7:41 am UTC



search for sylk

it is an open systems standard

i did not invent it

i found ONLY enough about it to do what I needed

i found it via google searching

I know nothing more about it

searching the web will find it. hint try something as simple as:

"sylk file format"

and you may well find stuff (i know you will)

Can not get output recognized as csv

robert, February 05, 2004 - 6:23 pm UTC

Tom, I am using webdb's wwv_sys_sylk..but I can not get the output to be recognized as csv, can not auto-launch Excel either...in stead I get a prompt to Save or select an application to open the file. (output is recognized as "mypkg.theprocedure")

I'd like the link to function like HTMLDB's whose output is recognized as i.e "employee.csv"

this is portion of webdb.wwv_sys_sylk program that generates the mime-header....

thanks

PROCEDURE print_heading
( grid IN VARCHAR2,
col_heading IN VARCHAR2,
allignments IN vc_arr,
headings IN vc_arr
)
IS
l_align VARCHAR2(1);
l_heading VARCHAR2(1000);
BEGIN
owa_util.showpage;
owa_util.mime_header('application/excel');
htp.p('ID;ORACLE');

Tom Kyte
February 06, 2004 - 8:32 am UTC

what mime type does your browser show and what browser are you using and does your browser have excel registered to deal with mime types of "application/excel"

owa_util.mime_header for Excel

robert, February 06, 2004 - 3:27 pm UTC

Searched on Metalink...this does the trick

owa_util.mime_header('application/vnd.ms-excel');

thanks

Tom Kyte
February 07, 2004 - 2:06 pm UTC

that just means you did not have application/excel configured in your browser is all.

been using the same packge for over 2 years

steve, February 07, 2004 - 6:56 am UTC

i've been using this exact package since way back ,and had very little problem with it.
It's currently producing spreadsheet reports automatically ,Even tied it into javamail via a clob, so that they can be emailed automatically to managers & directors.

i just cannot believe the level of some these requests and how little work some people seem willing to do.




A reader, November 12, 2004 - 2:42 pm UTC

Are there any number of lines limitations to this?

I did select * from t -- 72000 rows

But when I used your owa_sylk to create a .slk file and opened it in Excel, it only had 10002 rows?

Thanks

Tom Kyte
November 12, 2004 - 3:37 pm UTC

and if you edit the file directly (the sylk file) and look at the bottom -- is the last row you see there, the last row excel is displaying?

utl_file won't limit the lines, excel may well not be able to handle a big file.

A reader, November 12, 2004 - 3:41 pm UTC

Yes, the last row in the slk file is the last row in Excel. So , it would seem that utl_file/owa_sylk is somehow silently chopping off data?

Tom Kyte
November 12, 2004 - 3:55 pm UTC

nope, it shouldn't -- see if you don't see the "missing data" in the sylk file.

i see chris beck (the author) put "when others" in the code, bummer (i'll be making fun of him for that). If you do not see the "missing data" in the sylk file -- please comment out ALL when others blocks and see if an error is being ignored.


Very Use full

Bachina, January 06, 2005 - 10:09 pm UTC

Hi Tom ,

The package owa_sylk is pretty good. I would like to know how to give color to the coloumns in the spread sheet while genrating the data in to it .

Could you please help me in this regard.

Tom Kyte
January 07, 2005 - 8:50 am UTC

google around for information on the options available to the SYLK format.

Also, excel handles 'html', you might be able to have more formatting control over it with html output.

Followup to November 12, 2004 comment from A reader

Krous, May 23, 2005 - 4:54 pm UTC

The Procedure show has a variable called p_max_rows which is defaulted to 10000 rows. If you do not change this value and remain with the default, the owa_sylk will not print more than 10k rows....

Formatting columns in XL spreadsheet

Srivatsa, March 27, 2006 - 5:05 pm UTC

Hello Tom

The codes provided below are very helpful. Thanks.

Few finetuning for me would be very useful
1) How do i change the aligment. Everything seems to come right aligned.
2)How do i change the column formatting to specify it to be a date or a numeric or whatever
3)Do you have example of CSV by anychance.

Thanks
==srivatsa==

Tom Kyte
March 27, 2006 - 8:22 pm UTC

1) google the sylk file format? and change the code to output what you want...

2) see #1

3) this one I can do </code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

Link for SYLK formatting help

Srivatsa, March 27, 2006 - 5:24 pm UTC

Tom

I found this link very useful for the question i asked

</code> http://www.wotsit.org/search.asp?page=2&s=database
(I downloaded the 2k or 4k file which lists all options for formatting)

Other useful links
http://www.fileformat.info/format/sylk/ <code>


I am all set with the SYLK formatting.

But if you have any example you can give me for CSV file, that would be very helpful

thanks
==srivatsa

Dave Thompson, March 28, 2006 - 5:52 am UTC

Here is a link I have found useful on this subject:

</code> http://en.wikipedia.org/wiki/SYmbolic_LinK_(SYLK <code>



Owa_Sylk Utility

Suren, June 14, 2006 - 8:00 am UTC

Hey Tom,

There are far too many version of OWA_SYLK package, i could not find the one i am looking for can you please direct me to link where the Excel is stored on the local disk.

Another one , how can we identify the latest generated file over the Web server, in UTL_FILE_DIR path?

Thanks in Advance,

Surender N.

Tom Kyte
June 14, 2006 - 8:58 am UTC

that only works with "forms"

so look for

owa_sylk text_io


text_io being the forms package that does IO to the file system where forms is running.

Download this utility?

chandini paterson, November 17, 2009 - 5:10 am UTC

Hi tom,
Is there anywhere I can download this utility of yours? I did find a few references to your code, but they are all incomplete. Thank you, Chandini

Truncation of leading 0s when turning on totals

Brendan D, June 28, 2010 - 1:21 am UTC

Hi

Firstly thank you so much for this amazing package.

I have noticed a slight anomoly though, which I have been unable to wrap my head around.

Why when pass in values for p_sum_column do I have the leading 0's truncated from every colum in my output file, even when they arent being summed?

If you could just give me a pointer on how to stop this from occuring it would be greatly appreciated.

Thank you
Tom Kyte
July 06, 2010 - 11:17 am UTC

huh? I'm not following you at all - please provide a complete example.


there are no such things as leading zeroes in a number, only strings would have that, but if you convert the string to a number in order to sum, the zeroes of course go away (since numbers do not have the concept of leading zeroes)

owa_sylk

sam, March 09, 2011 - 12:24 pm UTC

Tom:

This is an awesome package! You should have told me about it years ago. I get so many user requests to dump reports data to Excel.

Do you recommend using the CURSOR approach or the first one?

I tried a query "Select * from ORDER" for a table with 30 columns and 6000 records and i got a few errors from excel

cannot read record 4415.
continue reportingeach error.

I think it might be related to leaving this as col3 and col4 were strings.

p_sum_column =>
14 owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y'),


Does the package has any limitations in terms of the size of data getting dumped.
Tom Kyte
March 09, 2011 - 12:48 pm UTC

... Does the package has any limitations in terms of the size of data getting
dumped. ...

it is limited only by your PGA memory available.



owa_sylk

sam, March 12, 2011 - 10:10 pm UTC

Tom:

Is there a way to specify default data alignment in owa_sylk for data dumped to Excel.

I am dumping stock description to a spreadsheet and it is coming up right aligned in the spreadsheet. i want it left aligned.
Tom Kyte
March 14, 2011 - 7:46 am UTC

Look up the specification for the SYLK file format.

Research what it can and cannot do.

Then implement it since you have the code.

And if you think what you've done is really good - post it for others.


The internet: the way it works.

sam, March 14, 2011 - 11:35 am UTC

Tom:

I see what you are saying but it might take considerable time to do that. I have to deliver this report tomorrow unfortunately. I dont have much time to modify the package and test.

If i am correct you cant do it with the current pl/sql package options you implemented.


This package allows you to send the results of any query to
a spreadsheet using UTL_FILE

parameters:
p_query - a text string of the query. The query
can be parameterized
using the :VARAIBLE syntax. See example
below.

p_parm_names - an owaSylkArray of the paramter names
used as bind variables in p_query

p_parm_values - an owaSylkArray of the values of the
bind variable names. The values
muse reside in the same index as the
name it corresponds to.

p_cursor - an open cursor that has had the query
parsed already.

p_sum_column - a owaSylkArray of 'Y's and 'N's
corresponding to the location
of the columns selected in p_query.
A value of NYNYY will result
in the 2nd, 4th and 5th columns being
summed in the resulting
spreadsheet.

p_max_rows - the maxium number of row to return.

p_show_null_as - how to display nulls in the spreadsheet

p_show_grid - show/hide the grid in the spreadsheet.

p_show_col_headers - show/hide the row/column headers
in the spreadsheet.

p_font_name - the name of the font

p_widths - a owaSylkArray of column widths. This
will override the default column widths.

p_headings - a owaSylkArray of column titles.
This will override the default column
titles.

p_strip_html - this will remove the HTML tags from the
results before
displaying them in the spreadsheet cells.
Useful when the
query selects an anchor tag. Only the
text between <a href>
and </a> tags will be sent to the
spreadsheet.


Tom Kyte
March 14, 2011 - 12:11 pm UTC

that is your job sam. sorry, I don't have time to write your code either.

Sorry, couldn't resist

S, March 14, 2011 - 12:14 pm UTC

LOL

owa_sylk

sam, March 21, 2011 - 12:05 pm UTC

Tom:

Is there an option in the owa_sylk package to turn off the "Total" footer that gets printed.

I have set all the columns for p_sum_column to "N" but the total label always comes out at the bottom of spreadsheet.

thanks,
Tom Kyte
March 21, 2011 - 12:30 pm UTC

Sam,

did you see one?

But I looked, it took me about 15 seconds to figure out:

if the element passed into print_sums has a count of zero, it returns and doesn't print out anything. So, looking at what called print_sums, it was the show routine. It in turn just passes in the inputs you sent to it - so..... if it was sent an empty array for p_sum_columns - it will not print.

Now, testing out my theory (another 30 seconds of typing)

ops$tkyte%ORA11GR2> declare
  2          nm      owa.vc_arr;
  3          vl      owa.vc_arr;
  4  begin
  5          nm(1) := 'WEB_AUTHENT_PREFIX';
  6          vl(1) := 'WEB$';
  7          owa.init_cgi_env( nm.count, nm, vl );
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec owa_sylk.show( 'select * from all_users where rownum <= 2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from TABLE( get_page );

COLUMN_VALUE
-------------------------------------------------------------------------------
ID;ORACLE
P;FCourier New;M200
P;FCourier New;M200;SB
P;FCourier New;M200;SUB
F;C1;FG0R;SM1
F;C2;FG0R;SM0
F;C3;FG0R;SM0
F;C4;FG0R;SM0
F;R1;FG0C;SM2
C;Y1;X2;K"USERNAME"
C;X3;K"USER_ID"
C;X4;K"CREATED"
C;Y3
C;X2;K"SYS"
C;X3;K"0"
C;X4;K"05-SEP-10"
C;Y4
C;X2;K"SYSTEM"
C;X3;K"5"
C;X4;K"05-SEP-10"
F;W1 1 7
F;W2 2 8
F;W3 3 7
F;W4 4 9
E

25 rows selected.


I can see that if I do not pass in anything for p_sum_columns - nothing, nada, nunca - no totals get printed out.


Sam - question for you - you have the code, you have it all, it is right there, it is very simple code, it is well commented in the specification.

Why cannot you figure some of this stuff out?

excel

Sam, May 03, 2011 - 8:41 pm UTC

Tom:

Is there a way to write an SP to generate the Excel file behind the scenes using owa_sylk and store it in a table as BLOB.

instead of streaming it to user machine and having him save it on client and then uploading it to DB.
Tom Kyte
May 04, 2011 - 2:04 pm UTC

sure there is Sam, you are a plsql programmer - you can answer this question yourself if you think about it.

Of course you can insert an empty blob into a table and get the lob locator.

Or course you can use dbms_lob.write_append to add text to the end of it (using utl_raw.cast_to_raw).

Of course you can commit the changes and have them persist.

Of course you could also figure out that using BLOB would be wrong, it should be CLOB probably - but that is another discussion.

And of course, since you have the code, you can make all of these modifications yourself!

excel

sam, May 04, 2011 - 4:31 pm UTC

I know how to insert file into a blob.

THe part that i need to know is currently I create the Excel file on my PC using Excel program running on my PC.


How can i do that on the server if Excel is not installed there.
Don't you have to know how Excel internally stores data and write a program that does similar thing.


I thought you might already wrote something that does that. This site has some ideas.

http://akdora.wordpress.com/2009/02/06/how-to-write-excel-via-plsql-and-save-the-file-to-a-directory/

http://roelhartman.blogspot.com/2006/07/how-to-create-neatly-formatted-excel.html

I am not sure why would you store an EXCEL file in a CLOB too. that is not a text file.


Tom Kyte
May 05, 2011 - 4:05 pm UTC

what???

You make zero sense here.

You started last time with:

Is there a way to write an SP to generate the Excel file behind the scenes using owa_sylk and
store it in a table as BLOB.


what the *heck* does this have to do with Excel running on your PC?????????


To generate a sylk file that excel is happy with, you have all of the code in plsql you need right here. All you'd need to do is..... well, I told you last time.



I am not sure why would you store an EXCEL file in a CLOB too. that is not a
text file.


think about this sam, think - put on your critical thinking hat and think.


You wrote:


Is there a way to write an SP to generate the Excel file behind the scenes using owa_sylk and
store it in a table as BLOB.



Now read that and think "what would a logical sensible person think I was asking"

I thought you were asking

Can I use OWA_SYLK to generate a file for excel and store it in a blob?


Seems reasonable to think that? Doesn't it? In fact, I don't see any other way to really interpret it.


The answer to the question you asked is "yes of course" - "and furthermore, a sylk file is plain text, not binary"



You can generate HTML and excel will read it.
You can generate SYLK and excel will read it.
You can generate CSV and excel will read it.
You can generate XML and exel will read it.

None of them requires excel be installed anywhere.
None of them is binary.
None of them is different from the approach here (your links), they are the same, they just use a different file format - html, csv, xml - I used SYLK (well documented, portable, pre-dated xml implementations and html even from an excel point of view, much more powerful than CSV)

You, Sam, may take all of this stuff and do whatever you need with it.

Only call it Excel when it is Excel

Marcus, May 05, 2011 - 1:26 am UTC

I am not sure why would you store an EXCEL file in a CLOB too. that is not a text file.

Easy: the linked programs don't create (binary) Excel files. They create HTML or XML files, those are text files. The mistake is to name every file Excel can open an "Excel file" instead of saying what it really is.

One of the best alternatives at the moment is as_xlsx from Anton Scheffer http://technology.amis.nl/blog/?p=10995 it writes natively in the new Excel 2007 format, in this case zipped XML files, so you would need to save it in a blob.

Marcus

excel

sam, May 05, 2011 - 7:36 pm UTC

Tom:

I think you misunderstood me, maybe i did not explain it well.

RIght now, I have a URL that I click on which call SP that uses OWA_SYLK to dump the query into Excel,

After that I save the file in EXCEL (running on client) and create myfile.xls.

This is the file iam trying to create in the background and store in a table.

I think your idea is to let OWA_SYLK write to a CLOB instead of streaming it to browser. right? and then when user clicks link you stream the text data to browser.

My idea was creating and storing th native myfile.xls in a table.

If i send you a spreadsheet file, are not you going to store it into a BLOB field?

The link Marcus posted is pretty useful. i got to try that.
i think it save native xls files. not sure if it will work with 9iR2 though.
Tom Kyte
May 06, 2011 - 10:27 am UTC

I did not misunderstand you.

You just never said anything remotely SIMILAR to this at all before. I can only address what I read.


If you want to store XLS in the database, a native XLS file produced by excel, you are going to be doing that on your own somehow, that has nothing to do with the database really.

Why not just store SYLK or HTML or XML? You wouldn't need excel at all, it would be portable to other environments, and it would be .... (best of all) easy and done.


If i send you a spreadsheet file, are not you going to store it into a BLOB
field?


IT DEPENDS. If you send me a sylk file, nope, that is a clob. Html, nope that is a clob. XML, nope that is a clob.


You never ever said it was an XLS file - we were talking SYLK.


Sam, pretend you are not sam for a minute, pretend you are Bob and Sam just asked you:

Bob:

Is there a way to write an SP to generate the Excel file behind the scenes using owa_sylk and
store it in a table as BLOB.

instead of streaming it to user machine and having him save it on client and then uploading it to
DB.


And all Bob knew was you were on a web page that showed how to create a SYLK file that streamed to the end user - and if the end user wanted it in the database, they'd have to upload it again.

all you asked Sam, seriously - all you asked - was "how do I save SYLK (which is an excel file as much as xls, xml, html is) into a blob in the database"



his procedure creates an xlsx file, not xls. It is XML. It was already mentioned. It is a clob bit of data.

spreadsheet

sam, May 09, 2011 - 12:30 pm UTC

Tom:

<<is procedure creates an xlsx file, not xls. It is XML. It was already mentioned. It is a clob bit of data. >>


Are you sure this is XML format and not native excel format? Office 2007 added "x" to the native file format in Office 2003. So doc became docx and xls becuase xlsx

Tom Kyte
May 09, 2011 - 2:27 pm UTC

I see he is utl_compressing it, it would be a blob then if you wanted to use that format.

Excel Spreadsheet

sam, September 08, 2011 - 10:02 am UTC

Tom:


Is there a way to tweak the query somehow so I can get some common values before the table of employees is listed.

What I mean, let us say I wanted to print "Report Date", "Company Name", "DB User Name"
which are values/fields that apply to all employees before the list is displayed.


p_query => 'select empno id, ename employee,
sal Salary, comm commission ' ||
'from scott.emp ' ||
'where job = :JOB ' ||
'and sal > :SAL',


Report Date: 09/01/2011
Company Name: XYZ Inc.
DB User: orcladmin

Empno ename sal commm....
----- ------ --- ------
Tom Kyte
September 08, 2011 - 5:47 pm UTC

well, seeing as how you have the code, I'd just say "add a new parameter and add new code to print that new parameter out in the main code"....

You own the code now, make it yours :)

excel

sam, September 11, 2011 - 12:25 am UTC

Tom:

what do you mean by new parameter?

like a subquery in the main query for those values. it still will be in same table.

You did not mean i can have another p_query2 and one procedure that would run "p_query" and "p_query2" and dump both results to excel?

excel

sam, September 11, 2011 - 12:25 am UTC

Tom:

what do you mean by new parameter?

like a subquery in the main query for those values. it still will be in same table.

You did not mean i can have another p_query2 and one procedure that would run "p_query" and "p_query2" and dump both results to excel?
Tom Kyte
September 11, 2011 - 9:07 am UTC

sam - are you or are you not a programmer?


You have code staring you in the face - you own this code.

You would like this code to do something other than what it does. What you want it to do is rather simple and trivial.

and someone says to you:

well, seeing as how you have the code, I'd just say "add a new parameter and add new code to print that new parameter out in the main code"....

You own the code now, make it yours :)


and you honestly don't know what that means?


You want to print out some headers, just PASS THEM IN AS PARAMETER(S) - that is the "add a new parameter" bit, you are going to pass in more information. And then *modify the existing code* to print out these headers at the right point.


Should take a couple of seconds of your time to accomplish.

Sorry for being harsh sounding here - but this is really rather straightforward - nothing hard, nothing tricky, nothing a programmer hasn't done a million times before.

downloading excel file

A reader, December 15, 2011 - 9:00 pm UTC

Tom:

I am using owa_sylk to download query results to excel spreadsheet.
User needs two options: one to download data directly to Excel and another to save file to PC.

It works fine except I cant somehow control the default filename downloaded.

I want to default the "Save as" option to "invoice-xxx.xls" where xxx is the invoice number but
mod_plsql keeps setting the filename to package name (invoice.xls).


do you have any idea what to do to get the default filename set in the header.





begin


owa_util.mime_header('application/vnd.ms-excel');

htp.p('Content-Type: text/plain');
htp.p('COntent-Disposition: attachment; filename='||'invoice-'||p_invoice_no||'.xls);

owa_util.http_header_close;



l_query := 'select * from T1, T2.....';

dbms_sql/parse(l_cusor, l_query, dbms_sql,native);

owa_sylk.show(
.....



end;

excel

A reader, December 16, 2011 - 10:58 am UTC

Tom:

just ignore the above question. I think you already did.

The default setting in OWA_UTIL.MIME_HEADER is true which means it will close the header after that line.

http://docs.oracle.com/cd/B14099_19/web.1012/b15896/psutil.htm#i1006133

I set it to "false" and the default filename set worked fine.

Error While Generating Excel

Viswanathan S, October 15, 2012 - 4:27 am UTC

Hi Tom,

I am getting this below error while running this Block

declare
output utl_file.file_type;
begin
output := utl_file.fopen( 'LOC_PHASE1_WHOUSE', 'emp1.xls', 'w',32000 );

owa_sylk.show(
p_file => output,
p_query => 'select transaction_type,business_Date ' ||
'from saas.tbl_bi_jobs_obj ' ||
'where status = :STATUS',
p_parm_names =>
owa_sylk.owaSylkArray('STATUS'),
p_parm_values =>
owa_sylk.owaSylkArray('FAILURE'),
p_sum_column =>
owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y'),
p_show_grid => 'YES' );

utl_file.fclose( output );
end;

declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "VISWA.OWA_SYLK", line 183
ORA-06512: at "VISWA.OWA_SYLK", line 247
ORA-06512: at "VISWA.OWA_SYLK", line 266
ORA-06512: at line 6



Tom Kyte
October 15, 2012 - 10:34 am UTC

look in the other place you asked this *same exact question*.

why do people do that? ask the *same exact question* in multiple places??

OWB_SYLK

Viswanathan, February 14, 2013 - 6:17 am UTC

declare
l_cursor integer default dbms_sql.open_cursor;
begin
dbms_sql.parse( l_cursor,
'SELECT B.CHANNEL_CODE, A.ZIP_CODE,NULL CITY,C.STATE_CODE,B.TERRITORY_NAME,NULL WHOLESALER_NAME,NULL XID, NULL SPIKE_ID,NULL ARMS_TERR_ID,
B.TERRITORY_NUMBER ' ||
'FROM salesadmin.FINAL_zip_assign a,' ||'saas.dim_wholesaler_territory b,'||'salesadmin.zipcode_master c'||
'where a.TERRITORY_KEY = b.WHOLESALER_TERRITORY_KEY' ||'AND a.ZIP_CODE = c.ZIPCODE(+)'||
'a.CHANNEL_CODE = ''BANK''',
dbms_sql.native );

owa_util.mime_header( 'application/excel' );
owa_sylk.show(
p_cursor => l_cursor,
p_sum_column =>
owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
p_show_grid => 'NO' );
dbms_sql.close_cursor( l_cursor );
end;

I am getting wrong number of arguments in show procedure. Please correct me.


OWB_SYLK

Viswanathan, February 14, 2013 - 6:28 am UTC

Hi Tom,

declare
l_cursor number := dbms_sql.open_cursor;
output utl_file.file_type;
v_query_stm varchar2(4000);
begin
v_query_stm :='SELECT B.CHANNEL_CODE, A.ZIP_CODE,NULL CITY,C.STATE_CODE,B.TERRITORY_NAME,NULL WHOLESALER_NAME,NULL XID, NULL SPIKE_ID,NULL ARMS_TERR_ID,
B.TERRITORY_NUMBER
FROM salesadmin.FINAL_zip_assign a, saas.dim_wholesaler_territory b, salesadmin.zipcode_master c
WHERE a.TERRITORY_KEY = b.WHOLESALER_TERRITORY_KEY
AND a.ZIP_CODE = c.ZIPCODE(+)
AND a.CHANNEL_CODE = ''BANK''';
output := utl_file.fopen( 'LOC_PHASE1_WHOUSE', 'emp2.xls', 'w',32767 );

dbms_sql.parse( l_cursor,v_query_stm,dbms_sql.native );

owa_sylk.show(
p_file => output ,
p_cursor => l_cursor,
p_sum_column =>
owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
p_show_grid => 'YES' );
dbms_sql.close_cursor( l_cursor );
utl_file.fclose( output );
end;

While running this script I am getting subscript Beyond count. And it is storing data some in Xls file but it throwing error.

And also data stored in Xls file leading zero values or missing. How to get data with leading zero valuse. Column name is Zipcode.

Thanks
Viswanathan.S
Tom Kyte
February 14, 2013 - 7:37 am UTC

numbers don't have leading zero's

strings do.

you'd have to select to_char( number, 'fm00000000' ) to get a number converted into a string. Looks like someone used a number for zip code which is a big mistake - you've never seen postal codes from other countries - the UK for example: http://en.wikipedia.org/wiki/Postcodes_in_the_United_Kingdom

you might want to make that change sooner rather than later.


as for the subscript error, you didn't post any error messages - but, it sure looks like you have more than four columns in your query and your p_sum_column array only has four. I would guess that.

also, owa_sylk uses htp to print, not utl file. we won't by writing anything to a file, you'd have to modify the code to do file IO.

OWA_SYLK utility

Dave Sykes, November 26, 2013 - 4:16 pm UTC

Tom, just wanted to say thanks for this package and your many contributions to the oracle world. I have used owa_sylk in critical internal adminstrative applications many times here at Lincoln Laboratory. Until the folks in the Application Express team can come up with a *real* excel download (not a csv download) it is one of the few ways (the other that comes to mind is the Denes Kubicek export_excel_pkg package) we can download certain specific data in our organization and not have it turn into dates in Excel.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library