Muthuraj -- Thanks for the question regarding "Oracle datas into Excell Sheet using pl/sql procedure in Oracle 8i", version version 8i

Submitted on 10-Sep-2000 2:30 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 29-Dec-2008 15:25

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 we said...

<code>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 var

Reviews

Please bear with me i made mistake in the question December 12, 2001 - 6am Central time zone