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
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
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
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?
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
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
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
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
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
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.
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.
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.
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');
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
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
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?
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.
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==
Link for SYLK formatting help
Srivatsa, March 27, 2006 - 5:24 pm UTC
Dave Thompson, March 28, 2006 - 5:52 am UTC
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.
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
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.
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.
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.
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,
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.
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
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.
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
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....
----- ------ --- ------
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?
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
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
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
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.