Thanks for the question, Muthuraj.

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

Last updated: February 11, 2013 - 9:48 am UTC

Version: version 8i

Viewed 50K+ times! This question is

You Asked

Hello Sir,
My problem is thru stored procedure or pl/sql annonyms block how to write the records into Excell sheet in Oracle 8i.My collique is doing thru vb.But We want to achieve thru oracle pl/sql.For ex,I am getting all employee records from employee table and instead of spooling into a text file I want to write into excell sheet.
You were asked the working environment
Windows NT Workstation
Oracle 8i
Server Located in same workstation
There have no cartridges and app server or web server even not from forms.

Our Achievement has to be done thru pl/sql procedure.
For extreem case if not possible only in database server...please explain how to do in Oracle Web server 3.

Thank You

and Tom said...

We can do this with a straight plsql procedure. There are more then one format we can use to write an excel file -- from CSV to SYLK. I will demonstrate the SYLK format as I already have the code and it offers the ability to do much fancier stuff like fonts, headings, formulas and such.

We will use UTL_FILE (see the supplied packages guide for setup info on that package. You need an init.ora parameter set for this to work correctly). UTL_FILE allows us to write a file on the server and since your workstation = server, this should work nicely for you.

Here is the code with an example. It should get you going:

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.

declare
output utl_file.file_type;
begin
output := utl_file.fopen( 'c:\temp\', 'emp1.slk', 'w',32000 );

owa_sylk.show(
p_file => output,
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' );

utl_file.fclose( output );
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;
output utl_file.file_type;
begin
output := utl_file.fopen( 'c:\temp\', 'emp2.slk', 'w',32000 );

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

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

*/

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' );
--
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' );
--
end owa_sylk;
/
show error

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

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

g_file utl_file.file_type;

procedure p( p_str in varchar2 )
is
begin
utl_file.put_line( g_file, p_str );
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;
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;
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' ) 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 );
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' ) 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 );
end show;
--
end owa_sylk;
/
show error

Rating

(85 ratings)

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

Comments

Please bear with me i made mistake in the question

Richard, December 12, 2001 - 6:20 am UTC

My question is that I have 3 tables, 2 of which contain many records. I have
to write a code(plsql) that will select all matching records and insert them
into the 3rd table(C) .The real problem is that table A(not table C) must contain records in
either Excel or Access file format which is saved on a diskette. Thus apart
from selecting records from table B to be inserted into table C, I should also
find a way of reading or selecting matching records(in table A ) saved on the floppy
diskette.
Can this be achievable in oracle7.3.2? Can you give me a simple code to do that?
NB. Table A(studnumber,subject,level)
Table B(studnumber,dept,year)
Table C(studnumber,subject,level,dept,year).
Thanks a lot.
Please bear with me.

December 12, 2001 - 8:22 am UTC

not understanding the difference here.

I showed you how to populate table C with an insert into select.

I showed you how to dump any query to a file using UTL_FILE, be it against table A, table C, table foobar.

A reader, January 23, 2002 - 3:18 pm UTC

hi Tom.

I want to give heading to excel report inside spreadsheet?

e.g

REPORT FOR SALES DEPT.

then ...........all data

Can you help me?

January 23, 2002 - 6:22 pm UTC

the SYLK file format is an open, documented standard. You can search www.google.com for references to it, they can show you how to format a sylk file in that fashion. then you just have to modify the code....

Exporting in Lotus 1-2-3

Arvind, January 24, 2002 - 12:47 am UTC

This is very useful for use

Is this method is also applicable for Lotus 1-2-3 spreadsheets. Secondly can we import data from Lotus 1-2-3 sheets into oracle

January 25, 2002 - 6:55 am UTC

if lotus can read sylk files, sure (i think it can, at least the last time I saw it about 8 years ago it could, didn't even know it was still around).

You would EXPORT the data from lotus into a delimited file and use sqlldr to load it.

A reader, January 24, 2002 - 9:12 am UTC

Tom

i search www.googli.com web site for SYLK utility.
and i found sylk FILE FORMAT. I change you program for report heading.

ThnakS a lot

YOU ARE THE "BEST"

Good One But

Parag, January 24, 2002 - 10:04 am UTC

Hi :

I am using Oralce 8.1.7 on SUN. I have Fired the code give by U . After Creating Package , When i try to execute the code (where we used to call package ) it's giving me foll error.

ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 101
ORA-06512: at "SYS.UTL_FILE", line 192
ORA-06512: at line 4

Please help me out.

January 25, 2002 - 8:16 am UTC

you have not setup the necessary INIT.ORA parameters for utl-file. Please refer to the supplied packages guide, you need to setup the utl_file_dir init.ora parameter.

Why you get ORA-06510: PL/SQL: unhandled user-defined exception ...

Ravi, January 24, 2002 - 11:31 am UTC

You get this error if:
1 You have not specified utl_file_dir init parameter (as Tom warned)
2 You have specified it, but as a specific directory c:\temp

Solution to 2 is :
- Specify it as utl_file_dir = *
- Or change the directory to 'c:\temp' from 'c:\temp\' in Tom's code

I tested on Windows.

SYS.UTL_FILE", line 98

Reddy, January 25, 2002 - 5:06 am UTC

Hi tom

I copied and pasted in our database the package and package body has been created. After When I run the follwing code SYS.UTL_FILE line 98 error is comming.

I am comming from the server using Administrator login.
User

Reddy@Oracle815> SELECT STATUS,OBJECT_NAME FROM USER_OBJECTS WHERE
2 OBJECT_NAME LIKE 'OWA_SYLK%' AND
3* OBJECT_TYPE IN ('PACKAGE','PACKAGE BODY')
/
STATUS OBJECT_NAME
-------------------------------------------------------------------
VALID OWA_SYLK
VALID OWA_SYLK

Reddy@Oracle815>

declare
output utl_file.file_type;
begin
output := utl_file.fopen( 'E:\ORADEV\', 'emp1.slk', 'w',32000 );

owa_sylk.show(
p_file => output,
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' );

utl_file.fclose( output );
end;

/
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 192
ORA-06512: at line 4

What could be the reason. Pls clarify with some small example how this error is comming and how to eliminate.

January 25, 2002 - 8:40 am UTC

see the Oracle server reference manual and read about the utl_file_dir init.ora parameter which you NEED to set up

need suggestion

Mohd Ahmed, January 27, 2002 - 6:08 am UTC

What are your suggestions in order to have this working on 7.3.4.4 instead of 8i?

January 27, 2002 - 10:44 am UTC

You'll have to recode it to not use the new collection type syntax available with 8.0 and up -- use plsql index by table types instead. So, thats really the only changes you'll have to make. It's not as easy to initialize plsql index by tables as it is collection types -- so, instead of the caller coding:

declare
output utl_file.file_type;
begin
output := utl_file.fopen( 'c:\temp\', 'emp1.slk', 'w',32000 );

owa_sylk.show(
...
p_parm_names => owa_sylk.owaSylkArray( 'JOB', 'SAL'),
...
p_show_grid => 'NO' );

utl_file.fclose( output );
end;

they'll have to code (after you rewrite the owasylk package)
declare
output utl_file.file_type;
l_parm_names owa_sylk.owaSylkArray;
begin
output := utl_file.fopen( 'c:\temp\', 'emp1.slk', 'w',32000 );

l_parm_names(1) := 'JOB';
l_parm_names(2) := 'SAL';

owa_sylk.show(
...
p_parm_names => l_parm_names,
...
p_show_grid => 'NO' );

utl_file.fclose( output );
end;

Very neat.

Tony Reed, January 28, 2002 - 5:14 am UTC

At present I create reports as txt files which are opened as delimited in Excel.

This is much better!
No need for formatting etc.

Thanks.

Bind variables in Owa_SYLK

Tom, August 09, 2002 - 4:14 pm UTC

Tom,

One question. I notice that Owa_sylk passes in a table of bind name and a table of bind values as varchar2 data, although the columns to be bound may be numbers / dates. Am I misunderstanding how dbms_sql.bind_value works, or will this not cause each variable to be bound as a string, thus meaning that indexes will be ignored on predicates where the datatype is not a string.

August 09, 2002 - 4:42 pm UTC

you should use to_number and to_date where appropriate, eg:

select * from emp where empno = to_number(:x)

select * from emp where hiredate = to_date( :x )

select * from emp where ename = :x

would all be appropriate for example.

for TEXT_IO how to work

A reader, September 12, 2002 - 11:31 am UTC

Hi, Tom,

I want to write the file into client machine by TEXT_IO.
You told us we can change from UTL_FILE to TEXT_IO?
And I create the package "owa_sylk" in the "program unit"
within form, and also change all the UTL_FILE to
TEXT_IO type. But when I compiled it, there is an error:
DBMS_SQL(...., native)<---- error.
How can I solve that?

Thanks

September 12, 2002 - 3:44 pm UTC

in forms, you probably want to use EXEC_SQL, you can use DBMS_SQL but you cannot in some releases of forms reference a packaged variable like that.

Just use the number 1

.....
-- CONSTANTS
--
v6 constant integer := 0;
native constant integer := 1;
v7 constant integer := 2;
--

Ignore that

A reader, September 12, 2002 - 12:12 pm UTC

Hi, Tom

I solved that "dbms_sql.native" problem on the client side.
So, please ignore that question.

Thanks and have a nice day.

"Text" cell format, how?

A reader, September 12, 2002 - 12:26 pm UTC

Hi, Tom,

Thanks for you excellent works.
My question is: we got data write into the excel file, but
this is actually in "general" cell format. What i want to do is generate the data in "TEXT" format. In you "print_row" codes, can we implement that? Could you shed
a light on this lease?

Thanks,

September 12, 2002 - 4:11 pm UTC

You'll have to dig up the SYLK file format on the web -- google it, it is a documented standard.

Dynamic Data type

A reader, September 15, 2002 - 11:43 pm UTC

Hi, Tom,

Appreciate for the solution.
There are basically 3 types of data in the database:
VARCHAR2, NUMBER and Date. I want to dynamically write them
into excel datasheet. So I have to dynamically get the data type of each column and then wirte them into the column(row) based on the derived data type. Could you shed some light on this topic please? Also for the Number, how do I know it is just NUMBER or NUMBER(6,2)? Are there any difference to process that?
At last, we want to use JAVA implement the same function(
write data into excel), for example, SQLJ or JDBC. Could you compare your solution and JAVA solution in the
aspect of performance and easy of use as well as security
please?

Thanks a million

September 16, 2002 - 7:17 am UTC

No you don't.

A number can be fetched into a varchar2.
A date can be fetched into a varchar2.

When you print -- they will all be converted into varchar2 regardless.

Hence, I just fetch them into varchar2. This utility handles dates/numbers "as is".

A comparision of this to java

o this is written, the java one is not
o this is easy to code, performant, and will never throw a Null pointer exception
o you need to setup utl_file_dir to make this plsql version work (in releases before 9iR2, in 9iR2 you can use a directory object instead). You have to use dbms_java.grant_permission to make this work in java -- more or less the same.

Java will not make this any "better", trust me on that one. Why do you care what language something is written in.

Bear with me

A reader, September 16, 2002 - 10:57 am UTC

Hi, Tom,

Please bear with me, I'm kinda insist my opinion.
Because what I want to do is "pre-format" each column when
write them to the excelsheet. The person who receives
the excelsheet probably doesn't know the datatype for
each column(beacuse it is always "GENERAL" format when he/she opens the column(cell) format property). So by this "pre-format", each column will be in the right format when the guy open the column(cell) format and know what the "original" format of the data. That means we are not only write data into excel but also prepare the file(format column) for the person with no/little DB knowledge.
We are aware of we can do this by using PRO/C* or JAVA because we can write them into excel by correct format---
"%s, %i, %d" and it is in the right format and ready to
be checked by opening the format property in the excelsheet.
I remember there are some lines of codes which handle
finding datatypes of the outcome from a query dynamically,
but I can not find it, would you give me that pointer please?

Thanks a million

September 16, 2002 - 7:33 pm UTC

you can modify the code, which uses dbms_sql, to call the describe routine in dbms_sql to discover the datatype, scale and precision and do whatever formatting output you like.

Date format

A reader, September 17, 2002 - 5:04 pm UTC

Hi, Tom,

I already solved the problem(datatype) by myself.
One more problem;
How can I find the DATE value including time?
I try to extract the date value(including time) and
manipulate it with the other times and write the result
to excel.
I can not use "gcvalue" because it don't allow me to
for example:
SELECT to_date(gcvalue, 'DD-MON-YYYY HH24:MI:SS')
- to_date(other_date, 'DD-MON-YYYY HH24:MI:SS')
FROM DUAL;
It can only do this(with YY), but it doesn't give time portion:
SELECT to_date(gcvalue, 'DD-MOC-YY HH24:MI:SS')
- to_date(other_date, 'DD-MON-YY
HH24:MI:SS')
I tried to define another variable(for date):
g_dvalue DATE and use:
dbms_sql.column_value( c, i, g_dvalue ) if it is
a date type, but it gives me error(not match col type).
Again, I tried to do:
dbms_sql.define_column( p_cursor, i, g_dvalue, 32765)
brfore getting date value but it give me another error.
So, I'm running out gas, and could you let me know how
can I get exectly date value(including time) and write
them into excel by correct format(I can do this part)?

Thanks a million

September 17, 2002 - 8:09 pm UTC

looks like you are trying to get the difference between two dates right? if so, see:
</code> http://asktom.oracle.com/Misc/DateDiff.html <code>

Time portion

A reader, September 17, 2002 - 11:41 pm UTC

Nop, SIr,

The problem is that I can not get the time portion out of the variable "g_cvalue"(only date portion with two digits
of year show up no matter what format I specify, e.g even
I use 'DD-MON-YYYY HH24:MI:SS' to explicitly covert it and
display to the output).
I can handle the diff between two dates.
Is VARCHAR2 variable such as "g_cvalue" capable of getting
the value from binded column even the type is date?

Thanks,

September 18, 2002 - 7:22 am UTC

if you select

to_char( g_cvalue, 'dd-mon-yyyy hh24:mi:ss' )

you will most certainly get that string with TIME in it.. You would really really need to provide a complete YET very concise example of your issue in order for me to comment, because I just don't understand what your problem could be. CONCISE being an important word in that sentence.

Explain

A reader, September 18, 2002 - 9:44 am UTC

Hi, TOm,

Thanks for your time.
Ok, here is the problem:
if I call the package by passing a query which includes a column having a date type, then the time portion is not show up and only comes with two digits year format even I specified explicitly the date format that i want.
For example:
dbms_sql.parse( l_cursor, 'select * from emp', DBMS_SQL.NATIVE);
owa_sylk.show(p_file => output , p_cursor => l_cursor,
p_show_grid => 'YES' );
in the above format, there is a "hiredate" date type field
which can not be correctly displayed in excel after generating the file. But if I do:
dbms_sql.parse( l_cursor, 'select empno, ename, mgr...
to_char(credate, 'DD-MM-YYYY HH24:MI:SS') hd from emp', DBMS_SQL.NATIVE);
it can displayed the date correctly in excel but it is in the format of "TEXT" because it belongs to VARCHAR2.
So, my problem is: I still can not extract the time portion for a date type column, could you test the code by just passing query "select * from emp", write hiredate as a time format(col_type=12), and finally you will find the hiredate column is not correctly displayed if you open the excel sheet.
Can the variable g_cvalue be used to get the value of date format(i mean including TIME portion and be any format if i specified the date format explicitly) if we know the type of the column is 12(col_type=12)?

Thanks

September 18, 2002 - 3:03 pm UTC

pass it a query that formats the date as you like (eg: don't use *, use empno, ename, to_char(hiredate, 'dd-mon-yyyy hh24:mi:ss' ), .....

OR issue:

execute immediate 'alter session set nls_date_format = ''dd-mon-yyyy hh24:mi:ss'' ';

before calling owa_sylk to change the default date format.

If you want to do this in owa_sylk you will have to

a) check for the datatype are you are
b) make sure you bind a date datatype -- not a character string
c) format it however you like after getting it

I'll not be of any more use on this one cause

a) you get the time component (you said so -- when (I use to_char......)
b) it is how you need to output it for excel that you need to discover
c) I don't know squat about excel ;)

Is it possible to create multiple sheets in Excel file

John, September 18, 2002 - 10:21 am UTC

Hi Tom,

How can I create multiple worksheets in Excel file? Seems sylk only supports single sheet.

Thanks,
John

September 18, 2002 - 3:11 pm UTC

sylk is sylk. worksheets are MS's thing. At this point, you would have to ask MS -- what is the standard flat file format I can write that lets me use all of your features.

We can

A reader, September 18, 2002 - 1:03 pm UTC

Hi,

We can create separate worksheet by learning from sample files. All we lack is the current standard format file of
SYLK supported by MICROSOFT, the lastest one I can find is still for EXCEL 2.0 in published in 1985-1986. But as I said we CAN create separate worksheet.

Thanks

Thanks

A reader, September 18, 2002 - 3:21 pm UTC

Hi, TOm,

I solved the problem of not showing time portion by g_cvalue, because it is VARHCAR2 type(g_cvalue)--maybe
it is a bug. So I bind the date format by declare another
date variable g_dvalue in order to match them when i
get the column value.

Thanks anyway for your useful codes.

BTW, we can do most of the thing to excel format by using
this code, but you must to modify the code to your own needs.

Number of records in SYLK

A reader, September 18, 2002 - 5:05 pm UTC

Hi, Tom,

What is the largest number of records we can write into
excel by using this code?

I have tried to write about 450,000 records(from a table, pure query) into excel(SYLK) by this method, but I only got 10001 reocrds showup after I open the SYLK file under MS excel.

Please clarify.

THanks

September 18, 2002 - 6:55 pm UTC

the question is HOW MANY RECORDS can excel handle (and that is 64k)

450k -- no way, they don't do that

Speed

Parasheh, October 04, 2002 - 3:26 pm UTC

Hi, Tom,

Appreciate you code.

Ine thing I want to ask is that the speed of code execution
is very slow if the table is fairly large(both # of columns
and # of records are very large, e.g: million records and
100 columns). it will take at least 5~20 minutes to finish
the job). Would mind tell us how to increase the speed
please?

Thanks

October 04, 2002 - 7:17 pm UTC

You could recode to use array processing....

You could use a faster method such as C (search for array_flat on this site, code already exists)

Why not use MS Query?

Paul Dal Bianco, November 15, 2002 - 3:32 pm UTC

I create an MS Query DQY file and open excel.
I have created a menu item that works with the last_query from a block.

PROCEDURE FP_CREATE_DQY (p_qry in varchar2) IS

v_filename VARCHAR2(200);
v_file VARCHAR2(200);
v_qry VARCHAR2(2000);
my_file text_io.file_type;

BEGIN

-- set location of .dll
Win_Api_Preload.Set_Load_Loc('G:\system\sql_libs');

v_filename := Win_Api_Dialog.Save_File('*.dqy','Save File Dialog', 'C:\', 'DQY Files(*.dqy)|*.dqy|', TRUE, WIN_API.OFN_FLAG_DEFAULT, TRUE);

if v_filename is not null then -- "SAVE" button clicked
v_qry := replace( p_qry, 'ROWID,');

-- clear file if already exists, or create new one
my_file := text_io.fopen( v_filename, 'W' );

text_io.put_line (my_file, 'XLODBC');
text_io.put_line (my_file, '1');
text_io.put_line (my_file, 'DRIVER={Microsoft ODBC for Oracle};UID='||user||';PWD='||get_application_property(PASSWORD)||';SERVER=TORPROD;');
text_io.put_line (my_file, v_qry);
text_io.put_line (my_file, ' ');
text_io.put_line (my_file, :control.column_headings );

text_io.fclose( my_file );

Win_API_Shell.startfile ( v_filename, WIN_API.SW_SHOWMAXIMIZED, TRUE);

end if;

END;

November 15, 2002 - 8:16 pm UTC

gotta love stuffing passwords in clear text into files -- fits right into the entire MS architecture.

Why not use MS Query?

Paul Dal Bianco, November 15, 2002 - 3:40 pm UTC

I create an MS Query DQY file and open excel.
I have created a menu item that works with the last_query from a block.

-- oops I left in some extra code from my form referenceing :control.Column_headings.

Very handy

Scott, December 23, 2002 - 3:00 am UTC

G'day

You should add this package to your tidbit section, next to the dump_csv function.

</code> http://asktom.oracle.com/~tkyte/ <code>

December 23, 2002 - 8:54 am UTC

add this to

</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

the "unloader" topic -- added all of the options actually, owa sylk, dump csv, array flat and the sqlplus method.

What about query with join, union and alias?

Dave, March 18, 2003 - 8:41 pm UTC

Hi Tom,

I ran the following code and received errors:

declare
*
ERROR at line 1:
ORA-01003: no statement parsed
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1518
ORA-06512: at "SYS.DBMS_SQL", line 614
ORA-06512: at "SAIYA.OWA_SYLK", line 239
ORA-06512: at "SAIYA.OWA_SYLK", line 275
ORA-06512: at line 7

I ran your example, it worked, but while replaced with the query below errors occurred.
Which part caused these errors? Can't this package accept more complex queries? Please advise, thanks.

--------------------------------------------------------
declare
output utl_file.file_type;
query varchar2;
begin
output := utl_file.fopen( 'f:\utl_file_dir', 'rejectapp.slk', 'w',32000 );

owa_sylk.show(
p_file => output,
p_query => 'select * from ('||
'select c.salegroupname SALESGROUP,e.createdt REJECTDATE,trunc(applydate) APPDATE,trunc(approvdate) APPROVEDATE,
a.appformno APPNUM,CUSTREGNAME CUSTOMER,a.ACTUALTOTAL CONTRACTAMT,a.PREPAY DOWNPAYMENT,a.paidby PAYMETHOD,
b.agentcode SALELOCCODE,b.agentname SALELOC,f.paramdesc REJECTCAUSE,e.REASON'||
'from appforms a, salesagents b, salesgroup c,apprtnlog e,sysparams f'||
'where a.appformno = e.APPFORMNO'||
'and a.agentiid=b.agentiid and b.salegroupid=c.salegroupid'||
'and f.PARAMGROUPID = :PARAMGROUPID and e.RTNCODE =f.PARAMVAL'||
'and trunc(e.createdt) = trunc(sysdate)'||
'union'||
'select c.salegroupname SALESGROUP,e.createdt REJECTDATE,trunc(applydate) APPDATE,trunc(approvdate) APPROVEDATE,
a.appformno APPNUM,CUSTREGNAME CUSTOMER,a.ACTUALTOTAL CONTRACTAMT,a.PREPAY DOWNPAYMENT,a.paidby PAYMETHOD,
b.agentcode SALELOCCODE,b.agentname SALELOC,f.paramdesc REJECTCAUSE,e.REASON'||
'from iypappforms a, salesagents b, salesgroup c,apprtnlog e,sysparams f'||
'where a.appformno = e.APPFORMNO'||
'and a.agentiid=b.agentiid and b.salegroupid=c.salegroupid'||
'and f.PARAMGROUPID = :PARAMGROUPID and e.RTNCODE =f.PARAMVAL'||
'and trunc(e.createdt) = trunc(sysdate))'||
'order by SALESGROUP ,REJECTDATE',
p_parm_names =>
owa_sylk.owaSylkArray('PARAMGROUPID'),
p_parm_values =>
owa_sylk.owaSylkArray('APPRTNCODE'),
p_show_grid => 'YES');

utl_file.fclose( output );
end;

/

March 19, 2003 - 6:19 am UTC

dbms_sql can parse infinitely complex queries -- it is just an interface to the database.

hows about this -- you add a little debug -- or take your query and try:

declare
type rc is ref cursor;
l_cursor rc;
<other variables>
begin
open L_cursor for 'select * from ( ' || .......
using variable, variable, ....;
end;
/

just to see if you have it right. I see immediately:

'and trunc(e.createdt) = trunc(sysdate)'||
'union'||
'select c.s

for example which will result in :

'and trunc(e.createdt) = trunc(sysdate)unionselect c.s

suggestion:

don't concatenate, just let it flow:

p_query => 'select *
from ( select *
from ( select *
from dual )
)
', .....

you don't need to break it up as you have -- but if you do -- watch the whitespace!!!!

peter, May 03, 2003 - 12:34 am UTC

Hello,
Your solutioms are good.
1 .How to add another worksheet in the same file?
2. It seems doesn't work for Big5(tranditional Chiese words)
how to solve?

Thank you.

May 03, 2003 - 11:54 am UTC

1) you would need to research the SYLK file format to see if that is even possible. SYLK is open systems, portable, generic. worksheets -- are not

2) "it doesn't work" is ambigous as "my car won't start -- why". Sure it works, we can write a big5 file -- can excel read it?

Excellent.

Chandra S.Reddy, May 20, 2003 - 3:19 am UTC

Greate solution.

Tamas Szecsy, June 04, 2003 - 4:27 am UTC

Just a small comment on my part: I faced the same problem and decided to the other way: while it is somewhat more difficult and less portable to other spreadsheets, I wrote a small package that writes Excel 2000 specific HTML file with xls extension. This way I can make use of the many features of Excel - SYLK is somewhat limited - and still need no OLE or Excel installed and can use utl_file to write a simple ASCII file.

Here is my easy way to pump html to excel

Yong Ke Wu, August 10, 2003 - 7:28 pm UTC

run this procedure with and without format parameter

create or replace procedure test(format varchar2:='excel') is
begin
if format='excel' then
owa_util.mime_header('application/vnd.ms-excel');
end if;
htp.p('<table><tr><td>ID</td><td>Name</td></tr></table>');
end;

pls read this

suresh, September 12, 2003 - 12:16 pm UTC

hi tom,

i am generated excel report on web page by using "htp" package(htp.p(parameters)), but i am interested on that excel sheet to highlight the particular row border
(not row data(cells)), how to do this ?

pls reply immd ...

September 12, 2003 - 2:01 pm UTC

i will reply immediately that i don't use excel, so -- i don't know how to "highlight" stuff in it :)

however, that said -- SYLK is an open file format -- documented. google it!

pls reply immd web report in excel using htp.p only.

Suresh, September 13, 2003 - 1:56 am UTC

hi tom,

i am generated excel report on web page by using "htp"
package(htp.p(format string)), PL/SQL Cartridge and
Oracle 9iAS but i am interested on that web excel sheet to highlight the particular row border(not row data(cells)), how to do this ?

In PL/SQL Procedure i am using this statements...

OWA_UTIL.MIME_HEADER('application/vnd.ms-excel');
(Invoke excel format application) ok,

next i am using these statements :
(what below statements means pls explain )

htp.p('F;C1;FG0L;SM0');
htp.p('F;C2;FG0R;SM0');

htp.p('P;FArial;M200');
htp.p('P;FArial;M200');

htp.p('P;EArial;M200;L11');
htp.p('P;EArial;M200;SB');

htp.p('F;SDM6;R1'); ( i think this rows bold )
htp.p('F;SDM6;R2');

Imp : and how to hightlight the particular row border in web report of excel sheet ?.

pls reply immd ... tom,

Thanx
suresh

September 13, 2003 - 9:24 am UTC

please, hit page up and read that response.

Quck question.. no 1st column

rd, November 05, 2003 - 6:35 am UTC

Hi,

I need a solution quickly.
So i am asking you if you can quickly point out how to not print the first column.
i.e i dont want the sum column(1st column) at all.

TIA

November 05, 2003 - 9:25 am UTC

the fastest way?

DON'T SELECT IT

hmmm?

Excellant BUT Getting an error :(

Arindam Mukherjee, November 06, 2003 - 3:58 pm UTC

Tom,
I tried to run the proc as shown below but got the following error. Please help.
declare
output utl_file.file_type;
begin
output := utl_file.fopen( '/pdb01/dell/utl_file_dir', 'test.slk', 'w',32000 );

owa_sylk.show(
p_file => output,
p_query => 'select * from rep_1072407PDB0001',
p_parm_names =>
owa_sylk.owaSylkArray( 'SHORT_NAME', 'SOURCE_ID', 'ACTIVE_RIDS', 'INACTIVE_RIDS', 'PRIOR_INACTIVE_RIDS',
'ACTIVE_CIDS', 'PCT_ACTIVE_CIDS', 'TOTAL_INACTIVE_CIDS', 'PRIOR_TOTAL_INACTIVE_CIDS',
'PCTCHG_TOTAL_INACTIVE_CIDS', 'INACTIVE_CIDS_REFR', 'INACTIVE_CIDS_EXPIRE',
'INACTIVE_CIDS_USAGE', 'INACTIVE_CIDS_REFR_16', 'INACTIVE_CIDS_EXPIRE_16',
'INACTIVE_CIDS_USAGE_16'),
p_parm_values =>
NULL,
p_sum_column =>
NULL,
p_show_grid => 'NO' );

utl_file.fclose( output );
end;

The following error has occurred:

ORA-06531: Reference to uninitialized collection
ORA-06512: at "DELL_WARE_OCT.OWA_SYLK", line 181
ORA-06512: at "DELL_WARE_OCT.OWA_SYLK", line 258
ORA-06512: at "DELL_WARE_OCT.OWA_SYLK", line 277
ORA-06512: at line 6

November 06, 2003 - 5:52 pm UTC

well, time to see what line 181, 258, 277 of your version of owa-sylk is :)

use a select again user_source to see -- it might be obvious.

Thnaks I got it

Arindam Mukherjee, November 06, 2003 - 6:47 pm UTC

Tom,
I screwed I admit. The package runs but now I have a different problem. The SYLK file that it makes is invalid, it says that Excell convert some of the cells. Now after further debugging I got the root of the problem. Please refer to your print_heading procedure, there you have a line as : p('ID;ORACLE' ); which basically writes the first record as ID which according to the Standard is for identifying the file as SYLK. If I comment off that then I don't get any formats/fonts etc. But if I keep it and generate the file, although it gives me the above mentioned error , but it keeps the fonts for the report. I am at a total loss here. Could you help me in this regard.

Thanks
Arindam

November 07, 2003 - 8:18 am UTC

go back to the original code -- unedited -- as provided.

can you reproduce with that?

if not, figure out what you changed to make it so.

if yes, then give us a test case -- create table, insert into table just enough data, the call to owa_sylk. then we can look at it.

I have got the crux!!

Arindam Mukherjee, November 07, 2003 - 5:54 pm UTC

Tom,
I could figure out what was the problem. I changed the code which inserted invalid characters in the SYLK. Sorry I troubled you. I have a related question though.
In the package :
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_report_name in varchar2,
p_update_date in varchar2,
p_job_number in varchar2 );

p_sum_column is an Table array. Now when we call it from anonymous block we have to give the array elements as you have shown in your example. My problem is I don't know upfront what will be the elements would be, only at runtime it will be determined whether I want to sum on an column or not. For eg. I'll call onto a table to find out whether there is a particular flag set. If its set to 1 then I'll sum on that column or else not. How can I possibly do this ? Or can it be at all done ? Please help.

Regards
Arindam

November 08, 2003 - 10:01 am UTC

yes, you can do this -- arrays are just variables -- so in the same manner you pass in different queries, you can fill an array variable with anything you want and pass it in.

Help !!

Arindam Mukherjee, November 08, 2003 - 8:36 pm UTC

Tom,
I tried quite a few ways but did not get it working. Could you help by giving one example, I would appreciate any help from you. My goal is to pass an array elements into the show() procedure from the anonymous block. Please help!!

Regrads
Arindam

November 08, 2003 - 9:54 pm UTC

you just declare an array variable and fill it up? i don't see what is "hard" or "obscure" here.

maybe you post an example of what you tried and we'll take a look at it.

declare
l_var owa_sylk.owaSylkArray := owa_sylk.owaSylkArray();
begin
l_var.extend( number_of_columns_in_your_query );
for i in 1 .. l_var.count
loop
l_var(i) := 'N';
end loop;
-- now query your table and set the i'th element in the array to Y
-- then pass it on in.

Thanks Tom BUT

Arindam, November 08, 2003 - 11:45 pm UTC

carete or replace procedure CreateTest(p_table_name VARCHAR2)
is
TYPE VarcharList IS TABLE OF char(1);
l_cursor number := dbms_sql.open_cursor;
output utl_file.file_type;
begin

v_qry_str := 'select sum_flag, '||
'from test_header '||
'where report_number = ' ||''''||report_number ||'''';

OPEN v_column_name FOR
v_qry_str;
FETCH v_column_name BULK COLLECT INTO v_flag_list;
CLOSE v_column_name;

output := utl_file.fopen( '\test\utl_file_dir', 'test.slk', 'w',32000 );

dbms_sql.parse( l_cursor,
'select * from '|| p_table name,
dbms_sql.native );

owa_sylk.show(
p_file => output ,
p_cursor => l_cursor,
p_sum_column =>
owa_sylk.owaSylkArray( v_flag_list),-- here is the problem I have to give something like v_flag_list(1) ,v_flag_list(2) but I don't -- know how many elements will come here ??? p_show_grid => 'NO' );
dbms_sql.close_cursor( l_cursor );
utl_file.fclose( output );
end;

I have to pass the elements into the p_sum_column => owa_sylk.owaSylkArray( v_flag_list) without even knowing how many elements would really come in to the same. If I use the above it gives me an error. Could you help ? I know am doing something wrong here.

Thanks
Arindam

November 09, 2003 - 7:04 am UTC

the problem is YOU DO NOT have to give something like v_flag_list(1), ....

you have to FILL IN a variable v_flag_list (as demoed above) and then pass the single array v_flag_list

(please -- begging you -- use bind variables -- at the very very very least, use alter session set cursor_sharing=force!!!)

do you have a pl/sql programmer around? someone whose used arrays before?

Adding header information to SYLK sheet

Prem Kumaar, November 25, 2003 - 3:52 am UTC

I found the option of generating a excel(SYLK) format
from the below link </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:728625409049 <code>

But additionaly I would like to know if there is any possibility to put additional header information to the sheets.

Eg.

Create User : Scott
Created Date : 27-Aug-2003
Description : Testing

col1 col2 col3 col4 col5
----- ---- ---- ---- ----
A B C D E
F G H I J
.. .. .. .. ..

So in the above format could you please let us know how to add any type of headers as shown in the example( Created Date, Created User, Description etc).

Also is it possible to position the cell position to display contents eg cell(1,3) 1st row, 3rd column.

Set background color for heading, setting allignments etc.

I could not find any information on google.com about the SYLK format, could you please let me now.

Thankyou

November 25, 2003 - 7:51 am UTC

absolutely there is

the cool thing is -- SYLK is an open system, documented, fully accessible to you, me and everyone standard!

You could not find info about sylk on google???????? hmmm, wonder where i got my info from?

i searched for

"sylk file format"

and got some meaningful hits on the very first page.

GFF Format Summary: Microsoft SYLK
The SYLK File Format SYLK format is an ASCII text file designed ...

for example where there, check them out.

A reader, December 29, 2003 - 10:43 am UTC

Opening the file in EXCEL on a new session, not in the browser

Amanda, May 10, 2004 - 2:05 am UTC

I have a Web PL/SQL application. When I save the file it is written to the Unix box and not my work station. How can I open or spool this file to open in Excel on a new session and not just over the brwoser?

May 10, 2004 - 7:57 am UTC

that would be a virus like capability, wouldn't it.

the client must PULL the data -- we cannot just open up a client filesystem and have at it.

Hence you must use something, anything you want, on the client to write to the client. given that pretty much every client has a web browser - that might be a good choice.

Another Option

Riaz Shahhid, May 10, 2004 - 8:33 am UTC

You can Download Ora*XL from

</code> http://www.oraxcel.com/projects/sqlxl/ <code>

it allowa your MS Excel to work as a Oracle Client and execute commands and get the results direcly in the excel.

Report from Forms block records

Dulal, August 18, 2004 - 5:08 am UTC

Hi Tom,

Your advises are always great which I getting last two years.
Now I have a new problem as follows -
How to print a report from forms block records selecting (all/selected) by checkbox through a report (*.rdf) file.

System: Oracle8i, Dev6i & Windows 2000 pro.

August 18, 2004 - 8:25 am UTC

sorry -- no ideas, i don't do windows, forms or reports :)

try otn.oracle.com -> discussion forums, there is one for developer there.

Need help converting owasylk.sql version 8i to 7.3.4

Hugo Francesco Monterroso Rivera, September 10, 2004 - 4:07 pm UTC

Hi Tom:

I read the suggestion you do to Mohd Ahmed on January 27, 2002. He ask you this: "What are your suggestions in order to have this working on 7.3.4.4 instead of 8i?"

You said him that he need to recode it using plsql index by tables types.

My question is: Do you have the package already compiled in 7.3.4 or with the changes you suggest? because i have some problem to compile it with no errors.

Thank you for your help.

owasylk.sql - Dump to Spreadsheet with formatting

September 10, 2004 - 5:03 pm UTC

no i don't but the changes are *very* minor.

very good

naivedya, November 24, 2004 - 5:05 am UTC

This is very good, thankx !

Creating excel format files

Doug Brown, January 20, 2005 - 2:20 pm UTC

Great answers and perfect examples. Thanks a bunch

sql command

raj, March 11, 2005 - 5:17 am UTC

how many type in sqlserver2000 to create table cammand.

A reader, July 20, 2005 - 3:44 pm UTC

"Oracle datas into Excell Sheet using pl/sql procedure in Oracle 8i",

Mousumi, July 26, 2005 - 8:12 am UTC

Hi Tom,
Using the package owa_sylk I can format font,width etc. of an excel file through Oracle.
If possible can you please give some idea or code to change background color,font color in the excel sheet.For Example,
using PL/SQL query I have to print the following infor mation in an excel sheet.

Employee Information

Emp. Name Ram Kishan Desig.
Jr. Software Developer
Emd. Id. 67823

Nor in the above example "Employee Ingormation","Emp. Name","Emp. Id","Desig" should be bold,in a color suppose red and the values should be in different color and not bold.Now the whole information should be in a block and its color should be yellow.

Regards,

July 26, 2005 - 8:17 am UTC

google for sylk

it is an industry standard format, you can do what ever it can do. You cannot do anything in it it does not support....

sylk is a file format, google for it.

jitendra agarwal, November 30, 2005 - 6:06 am UTC

I want to know difference between
1:>commit and commit_form
2:>Call_form and open_form and new_form
3:>up and down

November 30, 2005 - 11:53 am UTC

commit commits any outstanding transactions. commit_form (as I recall, it has been a decade since I did a form), POSTS all outstanding work from the form to the database and then commits.

documentation covers the call/open/new_form builtins - I'll refer you to that.

jITENDRA aGARWAL, December 15, 2005 - 6:15 am UTC

WHAT IS THE DIFFERENCE BETWEEN COUNT(*) AND COUNT(1)

December 15, 2005 - 10:40 am UTC

count(1) is "wrong" and count(*) is correct.

count(*) says "i want to count the number of rows"

count(1) says "I want to count the number of 1's - for some reason".

count(*) is correct and proper.

count(1) is internally rewritten to be count(*) to fix it for those that don't use count(*) as they should...

9i/ 10g method

Praveen, April 22, 2006 - 8:10 am UTC

Hi Tom,

Is there any 9i or 10g features that can be used to improve the performance or simplify the code for generating excel data using pl/sql? Do you have any similar codes to share with us?

Thanks

April 22, 2006 - 3:17 pm UTC

APEX does this automatically.

owa_sylk (search for it on this site) does that.

Thanks

Praveen, April 24, 2006 - 11:52 pm UTC

HELP with Aligning UTL_FILE

Sandra Padron, May 16, 2006 - 1:49 pm UTC

I have a 'Procedure' that writes to a file. I need to place it columns with column headers and a title. I need to do this using a file path and not using DBMS. Can you help me or send me an example.

I would really appreciate it.

May 16, 2006 - 3:09 pm UTC

well, this is just sort of "padding with spaces" isn't it?

It is not very clear what you mean - the reference to "use a file path" and "not using DBMS" is throwing me off the track here.

I believe you probably just want to use "lpad" to left pad a string to make it be "centered"?

Please help me

Indranil, June 07, 2006 - 3:25 am UTC

Hi Tom,

Can we set the font or color (or bold text)
by using Text_IO pack for excel sheet generation/word doc
generation from sqlplus or from Form 6i ?
If yes,how ?
Please give an small example.

Thanks
Indranil

June 07, 2006 - 7:10 am UTC

question for "askbill.microsoft.com" as you are asking "what file format should I generate for these windows programs"

I looking for this

Indranil, June 08, 2006 - 1:37 am UTC

Hi Tom,
I just want to use TEXT_IO.putf or something like that
from FORMS 6i or PL/SQL so that when we create the txt
file or sylk file,we can set the font / color for
exporting data to excel .

would this be like this ?

-------
If not Text_IO.IS_OPEN(out_file) then
Out_File:=Text_IO.fopen('/u03/appl/test.slk','w');
End If;
-------Construct the heading of the excel sheet---
sv:='VOYNO,BOOKNO,SEQNO,GUEST_NAME,Rank';
TEXT_IO.PUTF(Out_File,'ID'||chr(10));
TEXT_IO.PUTF(Out_File,'F;R1;FG0C;SM2'||chr(10));
TEXT_IO.PUTF(Out_File,sv||CHR(10));
.......so on ....
TEXT_IO.PUTF(Out_File,'E'||CHR(10));
.......
If Text_IO.IS_OPEN(out_file) then
Text_IO.fclose(out_file);
End If;

Thanks and Regards
Indranil

June 08, 2006 - 8:39 am UTC

Umm, clearly a case of not understanding me.

...
question for "askbill.microsoft.com" as you are asking "what file format should
I generate for these windows programs"
......

I do not own the sylk file format.
I do not know how to bold things in excel.

I do know how to use google however - getting the sylk file format options would be something to consider?

I've actually said that a couple of times on this very thread?

<quotes from above when asked the same question you are asking....>

...
he SYLK file format is an open, documented standard. You can search
www.google.com for references to it, they can show you how to format a sylk file
in that fashion. then you just have to modify the code....
.....
You'll have to dig up the SYLK file format on the web -- google it, it is a
documented standard.
....
sylk is sylk. worksheets are MS's thing. At this point, you would have to ask
MS -- what is the standard flat file format I can write that lets me use all of
your features.
.....
1) you would need to research the SYLK file format to see if that is even
possible. SYLK is open systems, portable, generic. worksheets -- are not
.......
i will reply immediately that i don't use excel, so -- i don't know how to
"highlight" stuff in it :)

however, that said -- SYLK is an open file format -- documented. google it!
.......
the cool thing is -- SYLK is an open system, documented, fully accessible to
you, me and everyone standard!

You could not find info about sylk on google???????? hmmm, wonder where i got
my info from?

i searched for

"sylk file format"

and got some meaningful hits on the very first page.

GFF Format Summary: Microsoft SYLK
The SYLK File Format SYLK format is an ASCII text file designed ...
.......
google for sylk

it is an industry standard format, you can do what ever it can do. You cannot
do anything in it it does not support....

sylk is a file format, google for it.
.....

</quotes>

Excel Files from PLSQL

SVS, August 26, 2006 - 12:00 pm UTC

Tom,

It is nice solution. But still i need a solution to make it more useful. We are using Sun Solaris 5.8 and Oracle 8i. I need to create multiple sheets. What I Have to do ? Pls help me Since It it too urgent.

Thanks In Advance

August 27, 2006 - 9:06 pm UTC

guess you'd have to ask microsoft for details on their implementation of multiple sheets and how to create an xls file that would work?

a student, October 16, 2006 - 10:27 pm UTC

I've read some of the questions, they are helpful for me.
I want ask some other questions, but I do'nt not where and how to ask?

October 17, 2006 - 4:20 am UTC

see home page....

but it'll probably have a box that says "sorry..."

Found a little more details on the SYLK format

Mette, October 22, 2006 - 5:09 am UTC

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

Once again - this time (more) correct

Mette, October 22, 2006 - 5:17 am UTC

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


(You need to add an ) at the end of the above line - since review "eats it" for some reason.)

and

http://netghost.narod.ru/gff/graphics/summary/micsylk.htm <code>

regards
Mette

October 22, 2006 - 7:49 am UTC

You can easily find tons of information on SYLK, just type it into google.com and hit enter.

Create Excel Spreadsheets with PL/SQL: ExcelDocumentType

Jason Bennett, October 29, 2006 - 6:57 pm UTC

</code> http://radio.weblogs.com/0137094/2006/10/26.html <code>

The ExcelDocumentType generates an Excel XML document as its end product. Excel XML documents are automatically recognized by IE and the Windows OS as Excel documents, and are treated as such when opened (double click ...). The documents generated by the object work very well with Office 2003 (not with Open Office ...). The object gives the user the ability to create documents with the following features:

-Creation of multiple Worksheets
-Create and apply user defined styles
-Apply formulas to cells
-Create custom print headers
-Define rows, columns, and cells

The object provides two methods of document retrieval:

The document can be retrieved as a CLOB.
The document can be delivered through mod_plsql to a web browser.

October 29, 2006 - 6:59 pm UTC

thanks! appreciate the updates.

How do u use ur code in TOAD?

A reader, February 02, 2007 - 4:00 pm UTC

can i use dump_csv in TOAD?

February 03, 2007 - 7:25 pm UTC

you'll have to wait for "U" to come back to ask them.

But, they've never actually appeared here, so it might be a long time.

Toad can call stored procedures.

dump_csv is a stored procedure.

so call it.

owa_sylk Package

Nelson, March 22, 2007 - 9:17 am UTC

Greate job Tom,

The package is working pefect for me. How can i remove the sum column and the one blank row immediately after the title??

Regards
Nelson

March 22, 2007 - 10:22 am UTC

modify the code? you have access to all of it.

owa_sylk Package

Nelson, March 22, 2007 - 10:10 am UTC

Greate job Tom,

The package is working pefect for me. How can i remove the sum column and the one blank row immediately after the title??

Regards
Nelson

Jakarta POI

_bag_, March 23, 2007 - 1:46 am UTC

IMHO, the best way to read/write Excel sheets in Oracle is to use Jakarta POI - Java API To Access Microsoft Format Files.
http://jakarta.apache.org/poi/index.html

thanks for the code

IanC, September 18, 2007 - 8:50 am UTC

Whew! This code is exactly what our client was looking for. It was great that I stumbled to this thread or else we might have to do manual extraction for over 1500 tables.

Thanks again.

excel

A reader, January 22, 2008 - 5:01 pm UTC

Tom:

Can you provide an example of dumping the EMP table to your local PC from the database unix server.

I compile the owa_sylk fine. However when I tried this i got an error


SQL> 
SQL> declare
  2      output utl_file.file_type;
  3  begin
  4      output := utl_file.fopen( 'c:\temp\', 'emp1.slk', 'w',32000 );
  5  
  6      owa_sylk.show(
  7          p_file => output,
  8          p_query => 'select empno id, ename employee,
  9                             sal Salary, comm commission ' ||
 10                     'from scott.emp ' ||
 11                     'where job = :JOB ' ||
 12                     'and sal > :SAL',
 13          p_parm_names =>
 14                 owa_sylk.owaSylkArray( 'JOB', 'SAL'),
 15          p_parm_values =>
 16                   owa_sylk.owaSylkArray( 'MANAGER', '2000' ),
 17         p_sum_column =>
 18                   owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y'),
 19          p_show_grid => 'NO' );
 20  
 21      utl_file.fclose( output );
 22  end;
 23  /

excel

A reader, January 22, 2008 - 5:02 pm UTC

here is the error

declare
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 424
ORA-06512: at line 4

January 22, 2008 - 6:46 pm UTC

yeah, because utl_file is running on the SERVER

therefore, utl_file cannot dump to your PC, you would need a client program on the client to dump to the local file system

instead of utl_file.putline, use dbms_output.put_line and then you can use SPOOL from sqlplus to run the procedure and capture the output locally.

A reader, January 22, 2008 - 7:27 pm UTC

I did replace "utl_file.put_line" with "dbms_output.put_line

i got an error on here

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

(1): PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'

2. Can you also tell me how you call it to test output of emp file.

thanks

January 22, 2008 - 7:58 pm UTC

dbms_output takes one parameter - are you familiar with the API?

oh and please fix that when others, we need to delete that. I've fixed it above.

excel

sam, January 22, 2008 - 9:25 pm UTC

Tom:

OK i will take one prameter out. but to test it print all
the emp table do you do this.

owa_sylk.show(
p_file => output,
p_query => 'select * from emp',
p_parm_names => owa_sylk.owaSylkArray(),
p_parm_values =>
owa_sylk.owaSylkArray( ),
p_sum_column =>
owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y'),
p_show_grid => 'NO' );

January 23, 2008 - 7:20 am UTC

there are examples of invoking this on this very page, page up.

PL/SQL Package to Write Spreadsheets

Marcus, April 03, 2008 - 5:56 am UTC

I wrote a package similar to that of Jason Bennett, including the features of owa_sylk to pass a query or cursor.

http://matzberger.de/oracle/spreadsheet-en.html

Free for everyone to use

Marcus

Code Size Changed

Srividya, April 04, 2008 - 11:22 pm UTC

Instead of that you can use just the following simple code for the data generation in excel.

CREATE OR REPLACE PROCEDURE TEST_FILE
AS
FILENAME UTL_FILE.FILE_TYPE;
FILENAME1 VARCHAR2(1000);

cursor c1 is
select OPTION , NOUN_NAME ,
CONFIG
from fp_cha
ORDER BY ORDER_BY;

BEGIN
FILENAME1:='SIRI_FP.slk';
FILENAME:=UTL_FILE.FOPEN('/tmp',FILENAME1,'W');
FOR I IN C1 LOOP
UTL_FILE.PUT_LINE(FILENAME,I.OPTION||' '||I.NOUN_NAME||' '||i.config);
EXIT WHEN C1%NOTFOUND;
END LOOP;
UTL_FILE.FCLOSE(FILENAME);
END;

The file will be created in the server where your database is installed.

Error when running pkg

A reader, May 22, 2008 - 12:17 am UTC

Hi sir,

When i tried to run the sample procedure it gives error..

ORA-06533: Subscript beyond count
ORA-06512: at "SCOTT.OWA_SYLK", line 28
ORA-06512: at "SCOTT.OWA_SYLK", line 270
ORA-06512: at "SCOTT.TEST1", line 7
ORA-06512:at line 2

Please Advice ...
TIA,

May 22, 2008 - 7:07 am UTC

use your powers of debugging to figure it out?

come on, you have the code (so do I - here we are equal)
you have the tables you are querying (you have them, I do not)
you have the query you are using (you have it, I do not)
you have the block of code that called this, you know the inputs (you do, not me)

so, it would seem you might be in a much better position to figure this out then I.

excel

A reader, May 22, 2008 - 7:46 am UTC

Loop in build_cursor

Gary, May 22, 2008 - 9:53 pm UTC

The 'subscript beyond count' issue is a problem in the BUILD_CURSOR function in the code in the original response. It has an uncontrolled loop (ie it has no exit condition, so either errors or continues until killed).

loop
  dbms_sql.bind_variable( c, n(i), v(i) );
  i := i + 1;
end loop;

can be replaced with :

WHILE i <= n.count LOOP
  dbms_sql.bind_variable( c, n(i), v(i) );
  i := i + 1;
end loop;

Also make sure you are giving values for all your bind variables.

sys.owa_sylk not found

naveen wason, May 29, 2008 - 8:46 am UTC

I want to use this package but in my existing oracle it is not found. how can i install this package and used to create reports in excel files.

May 29, 2008 - 9:38 am UTC

well, did you read the original answer??!?

the code is above, owa_sylk is something we threw together, it is not an Oracle supplied package.

Alignment of columns

Mike Yurche, August 08, 2008 - 10:11 am UTC

I just discovered the OWA_SYLK package and think it's great! My only concern is that when Excel opens the file the columns are defaultly aligned to the right, which is great for number fields but not so great for text fields. Is there any way that there could be options on column alignment?

August 08, 2008 - 1:26 pm UTC

google for sylk, learn what the standard provides and make it do whatever you like.

Can we use arrays in Stored Procedure

DNLKC, August 29, 2008 - 8:40 am UTC

hi tom, look into this Query And Answer.
Create table station(station_name varchar2(20),station_code varchar2(10));
insert into station('hyd','10');
insert into station('Sec','15');
Create table Weather(station_code varchar2(10),TEMPERATURE_min number,TEMPERATURE_max number,rain_min number,rain_max number);

PROCEDURE WEATHER1 ( P_STATION_NAME VARCHAR2,
P_TEMPERATURE_min number default 0,
p_TEMPERATURE_max number default 0,
p_rain_min number default 0,
p_rain_max number default 0)

IS
My_Exception EXCEPTION;
V_STATION_CODE VARCHAR2(10);
cursor C1
is
SELECT STATION_CODE FROM STATION
WHERE STATION_NAME = P_STATION_NAME;
Begin
open C1;
Fetch C1 into V_STATION_CODE;

IF TEMPERATURE_min :=0 or
TEMPERATURE_max :=0 or
rain_min :=0 or
rain_max :=0

Then
Raise MY_Exception;

else
IF C1%FOUND THEN
INSERT INTO WEATHER (V_STATION_CODE,
P_TEMPERATURE_min,
p_TEMPERATURE_max,
p_rain_min,
p_rain_max);
dbms_output.put_line('DATA SUBMITTED SUCCESSSFULLY!!');
elsif C1%notfound THEN
dbms_output.put_line('STATION NAME NOT FOUND TRY AGAIN!!');
end if;
end if;
Exception
When My_exception then
dbms_output.put_line('Some fields are missng please check it and enter again!!');
END;

This Prodcedure is working .But my expected out put is different.
1.Can't we give numbers as null.?
2.I have so many parameters in Procedure How can I use Varry in Procedure (Oracle Jdeveloper 10g)?
3.If I miss a field in procedure The exception should raise and How can we miss a field in Procedure?
4.begin
weather1('1','',3,5,'');
end;
is it correct way to miss fields?

Thanks in Advance,
DNLKC

August 30, 2008 - 9:27 am UTC

.... look into this Query And Answer. ....

I had a really hard time getting past that sentence.

and then I hit this one:

... This Prodcedure is working .But my expected out put is different. ...

Here is my question to you:

I got in my car and it started, I expected it to work differently.
1) can't we use a missing key to start it?
2) I have lots of keys on my key chain
3) If I am missing a key it should beep
4) I cannot compete with that one. You win on #4 :)

Seriously - what sort of answer could I give

1) can a number variable contain null - absolutely, pretty much any variable could be NULL as long as it is not nullable - sure.

2) You can do it yourself
http://asktom.oracle.com/pls/ask/search?p_string=collection+arrays+java+store+procedure
or use jpublisher to publish an interface class
http://docs.oracle.com/docs/cd/B19306_01/java.102/b14188/toc.htm

3) define "miss a field in a procedure". If you mean "the variable MUST be passed and cannot be NULL

ops$tkyte%ORA10GR2> create or replace package types_pkg
  2  as
  3          subtype mynumber is number NOT NULL;
  4  end;
  5  /

Package created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p( x in types_pkg.mynumber, y in types_pkg.mynumber)
  2  as
  3  begin
  4          null;
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA10GR2> exec p( 1, 1 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec p( null, 1 );
BEGIN p( null, 1 ); END;

         *
ERROR at line 1:
ORA-06550: line 1, column 10:
PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

if you want to catch the error in your OWN procedure, you would have to validate the inputs yourself (else your scope never comes into play)

probably easiest to do with a wrapper procedure:

ops$tkyte%ORA10GR2> create or replace procedure p_wrapper( x in number, y in number )
  2  as
  3          invalid_input exception;
  4          pragma exception_init( invalid_input, -6502 );
  5  begin
  6          p(x,y);
  7  exception
  8          when invalid_input
  9          then
 10                  dbms_output.put_line( 'you lose ' || sqlcode );
 11  end;
 12  /

Procedure created.

ops$tkyte%ORA10GR2> exec p_wrapper( 1, 1 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec p_wrapper( null, 1 );
you lose -6502

PL/SQL procedure successfully completed.

4) if you mean "to pass null" - which is not really 'missing a field' but rather 'passing null', no, do not use '' for null, use the keyword null for null

generate text files locally thru PL/SQL

Rahul, October 17, 2008 - 3:46 am UTC

Hi Tom,

What if we need to generate TEXT file on local/client machine not on the server.

Here after defining path for "utl_file_dir" parameter all file will be written on the server & some time everybody doesn't have access to server to fetch the generated file.

Can we generate text file locally instead of writing it on server thru PL/SQL

Look forward to hear from you.

October 17, 2008 - 9:30 pm UTC

you will not be using plsql. Plsql can only, will only, should only write to file systems available on the server.

PLSQL would be very "virus like" otherwise.

Think about it this way - would you expect a java routine running on an application server to be able to read and write YOUR pc's file system???

You need a client routine that can cannot to the database, run something on database, retrieve output and write it locally

(eg: a URL could do this, the client would 'execute' the URL locally, the browser being the client can use mod_plsql in the middle tier to run a stored procedure that generates output and send it back to the browser which can save it to disk (or open it, or display it, whatever)

Dynamic column

A reader, October 30, 2008 - 6:58 am UTC

hi,
I am dynamically trying to pass a particular column name to the select list of a select statement.

that is

@var = 'Col1'

so I need the following query

select col1 from tbl1

I will not know the column name is 'Col1' but the value will be in @var. How can I use it?

By the way I am using Oracle10g.
Thanks in advance.

Exporting Oracle Data inot Excel sheet using PL/sql

Kumar, December 23, 2008 - 11:40 pm UTC

Hi,

I have an assignment to export Oracle Data into Excel sheet using Pl/SQL procedure.

here is my environment which am using

Product : Oracle
Version : 10.2.0.1.0
OS : Windows XP Professional

I saw previous postings,bit confused do we need to install any owa_sylk utility package.

It would be greatful,if anybody provides sample package or procedure for exporting oracle data into excel.

Thanks in advance
-Kumar

December 29, 2008 - 3:25 pm UTC

owa_sylk is....

in fact....

that example

Problem while saving the output into .xls from concurrent program

Alaka, July 10, 2009 - 7:32 am UTC

Hi,

As mentioned in the following link
http://garethroberts.blogspot.com/2007/10/excel-file-output-from-oracle.html

we have created concurrent program with output type as PCL and all the other steps including UPDATE MIME TYPE to get the output in .XLS.

But we have a problem here,
We are able to open the output as .pcl.xls format, but when we save the file the format is in .PCL only.
What could be the reason for this.?
How to resolve this issue? We need the output should be saved in .XLS instead of .PCL format.

Thanks
Alaka

loading of excel file

Muhammad Adeel, April 23, 2010 - 1:51 pm UTC

How to retrieve the data from the Excel sheet using SQL SELECT statement within Oracle database.

I will be very thankful if anyone reply me.

April 23, 2010 - 2:40 pm UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206#18830681837358

K, February 08, 2013 - 2:18 pm UTC

Tom,
I granted execute rights for SYS.DBMS_SQL to "Sam" and ran the below sql but still getting an error. Can you please tell me what I am doing wrong? I replaced the directory location with an Oracle Directory name "DATADIR". I am using
Oracle 11.2.0.2.0 on Windows XP.

1 declare
2 output utl_file.file_type;
3 begin
4 output := utl_file.fopen( 'DATADIR', 'emp1.slk', 'w',32000 );

5 owa_sylk.show(
6 p_file => output,
7 p_query => 'select empno id, ename employee,
8 sal Salary, comm commission ' ||
9 'from scott.emp ' ||
10 'where job = :JOB ' ||
11 'and sal > :SAL',
12 p_parm_names =>
13 owa_sylk.owaSylkArray( 'JOB', 'SAL'),
14 p_parm_values =>
15 owa_sylk.owaSylkArray( 'MANAGER', '2000' ),
16 p_sum_column =>
17 owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y'),
18 p_show_grid => 'NO' );
19 utl_file.fclose( output );
20* end;
21 /
declare
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at "SAM.OWA_SYLK", line 28
ORA-06512: at "SAM.OWA_SYLK", line 267
ORA-06512: at line 5

Thanks,
K

February 11, 2013 - 9:48 am UTC

if you type

SQL> set role none;
SQL> select * from scott.emp;

does it work - if not:

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551289900368934430

it has nothing to do with dbms_sql, it has everything to do with the inability of your account to access scott.emp in a stored procedure.

Oracle datas into Excell Sheet using pl/sql procedure in Oracle 8i

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Please bear with me i made mistake in the question

Exporting in Lotus 1-2-3

Good One But

Why you get ORA-06510: PL/SQL: unhandled user-defined exception ...

SYS.UTL_FILE", line 98

need suggestion

Very neat.

Bind variables in Owa_SYLK

for TEXT_IO how to work

Ignore that

"Text" cell format, how?

Dynamic Data type

Bear with me

Date format

Time portion

Explain

Is it possible to create multiple sheets in Excel file

We can

Thanks

Number of records in SYLK

Speed

Why not use MS Query?

Why not use MS Query?

Very handy

What about query with join, union and alias?

Excellent.

Here is my easy way to pump html to excel

pls read this

pls reply immd web report in excel using htp.p only.

Quck question.. no 1st column

Excellant BUT Getting an error :(

Thnaks I got it

I have got the crux!!

Help !!

Thanks Tom BUT

Adding header information to SYLK sheet

Opening the file in EXCEL on a new session, not in the browser

Another Option

Report from Forms block records

Need help converting owasylk.sql version 8i to 7.3.4

very good

Creating excel format files

sql command

"Oracle datas into Excell Sheet using pl/sql procedure in Oracle 8i",

9i/ 10g method

Thanks

HELP with Aligning UTL_FILE

Please help me

I looking for this

Excel Files from PLSQL

Found a little more details on the SYLK format

Once again - this time (more) correct

Create Excel Spreadsheets with PL/SQL: ExcelDocumentType

How do u use ur code in TOAD?

owa_sylk Package

owa_sylk Package

Jakarta POI

thanks for the code

excel

excel

excel

PL/SQL Package to Write Spreadsheets

Code Size Changed

Error when running pkg

excel

Loop in build_cursor

sys.owa_sylk not found

Alignment of columns

Can we use arrays in Stored Procedure

generate text files locally thru PL/SQL

Dynamic column

Exporting Oracle Data inot Excel sheet using PL/sql

Problem while saving the output into .xls from concurrent program