Home » SQL & PL/SQL » SQL & PL/SQL » Loading data into excel sheet
Loading data into excel sheet [message #247912] Wed, 27 June 2007 08:44 Go to next message
anupatruni
Messages: 1
Registered: June 2007
Junior Member
Hello,
I would like to generate excel sheet from Oracle database using the following code.

declare
output utl_file.file_type;
begin
output := utl_file.fopen( 'c:/temp', 'empq.slk', 'w',32000 );
owa_sylk.show(
p_file => output,
p_query =>'select a.logon_id,
decode(role_id,'grant','Y','N')) Grant,
from (SELECT LOGON_ID,ROLE_ID FROM privileges GROUP BY LOGON_ID,ROLE_ID) a,s_temp b
where a.logon_id=b.logon_id group by a.logon_id

p_sum_column =>owa_sylk.owaSylkArray( 'N','N','Y','Y'),
p_show_grid => 'NO' );
utl_file.fclose( output );
end;


where owa_sylk is a package,and you can see the code here


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 );
exception
when others then null;
end;

function build_cursor(
q in varchar2,
n in owaSylkArray,
v in owaSylkArray ) return integer is
c integer := dbms_sql.open_cursor;
i number := 1;
begin
dbms_sql.parse (c, q, dbms_sql.native);
loop
dbms_sql.bind_variable( c, n(i), v(i) );
i := i + 1;
end loop;
return c;
exception
when others then
return c;
end build_cursor;
--
--
function str_html ( line in varchar2 ) return varchar2 is
x varchar2(32767) := null;
in_html boolean := FALSE;
s varchar2(1);
begin
if line is null then
return line;
end if;
for i in 1 .. length( line ) loop
s := substr( line, i, 1 );
if in_html then
if s = '>' then
in_html := FALSE;
end if;
else
if s = '<' then
in_html := TRUE;
end if;
end if;
if not in_html and s != '>' then
x := x || s;
end if;
end loop;
return x;
end str_html;
--
function ite( b boolean,
t varchar2,
f varchar2 ) return varchar2 is
begin
if b then
return t;
else
return f;
end if;
end ite;
--
procedure print_comment( p_comment varchar2 ) is
begin
return;
p( ';' || chr(10) || '; ' || p_comment || chr(10) || ';' );
end print_comment;
--
procedure print_heading( font in varchar2,
grid in varchar2,
col_heading in varchar2,
titles in owaSylkArray )
is
l_title varchar2(2000);
begin
p( 'ID;ORACLE' );
print_comment( 'Fonts' );
p( 'P;F' || font || ';M200' );
p( 'P;F' || font || ';M200;SB' );
p( 'P;F' || font || ';M200;SUB' );
--
print_comment( 'Global Formatting' );
p( 'F;C1;FG0R;SM1' ||
ite( upper(grid)='YES', '', ';G' ) ||
ite( upper(col_heading)='YES', '', ';H' ) );
for i in 1 .. g_desc_t.count loop
p( 'F;C' || to_char(i+1) || ';FG0R;SM0' );
end loop;
--
print_comment( 'Title Row' );
p( 'F;R1;FG0C;SM2' );
for i in 1 .. g_desc_t.count loop
g_lengths(i) := g_desc_t(i).col_name_len;
g_sums(i) := 0;
begin
l_title := titles(i);
exception
when others then
l_title := g_desc_t(i).col_name;
end;
if i = 1 then
p( 'C;Y1;X2;K"' || l_title || '"' );
else
p( 'C;X' || to_char(i+1) || ';K"' || l_title || '"' );
end if;
end loop;
end print_heading;
--
function print_rows(
c in integer,
max_rows in number,
sum_columns in owaSylkArray,
show_null_as in varchar2,
strip_html in varchar2 ) return number is
row_cnt number := 0;
line varchar2(32767) := null;
n number;
begin
loop
exit when ( row_cnt >= max_rows or
dbms_sql.fetch_rows( c ) <= 0 );
row_cnt := row_cnt + 1;
print_comment( 'Row ' || row_cnt );
--
p( 'C;Y' || to_char(row_cnt+2) );

for i in 1 .. g_desc_t.count loop
dbms_sql.column_value( c, i, g_cvalue );
g_cvalue := translate( g_cvalue,
chr(10)||chr(9)||';', ' ' );
g_cvalue := ite( upper( strip_html ) = 'YES',
str_html( g_cvalue ),
g_cvalue );
g_lengths(i) := greatest( nvl(length(g_cvalue),
nvl(length(show_null_as),0)),
g_lengths(i) );
line := 'C;X' || to_char(i+1);
line := line || ';K';
begin
n := to_number( g_cvalue );
if upper( sum_columns(i)) = 'Y' then
g_sums(i) := g_sums(i) + nvl(n,0);
end if;
exception
when others then
n := null;
end;
line := line ||
ite( n is null,
ite( g_cvalue is null,
'"'||show_null_as||
'"', '"'||g_cvalue||'"' ),
n );
p( line );
end loop;
--
end loop;
return row_cnt;
end print_rows;
--
procedure print_sums(
sum_columns in owaSylkArray,
row_cnt in number ) is
begin
if sum_columns.count = 0 then
return;
end if;
--
print_comment( 'Totals Row' );
p( 'C;Y' || to_char(row_cnt + 4) );
p( 'C;X1;K"Totals:"' );
--
for i in 1 .. g_desc_t.count loop
begin
if upper(sum_columns(i)) = 'Y' then
p( 'C;X' || to_char(i+1) || ';ESUM(R3C:R' ||
to_char(row_cnt+2) || 'C)' );
end if;
exception
when others then
null;
end;
end loop;
end print_sums;
--
procedure print_widths( widths owaSylkArray ) is
begin
print_comment( 'Format Column Widths' );
p( 'F;W1 1 7' );
for i in 1 .. g_desc_t.count loop
begin
p( 'F;W' || to_char(i+1) || ' ' ||
to_char(i+1) || ' ' ||
to_char(to_number(widths(i))) );
exception
when others then
p( 'F;W' || to_char(i+1) || ' ' ||
to_char(i+1) || ' ' ||
greatest( g_lengths(i), length( g_sums(i) )));
end;
end loop;
p( 'E' );
end print_widths;
--
procedure show(
p_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;
/



Here if I say a simple query like 'select * from grants',it's working fine,where as I am using decode function I am getting the below err

ERROR at line 9:
ORA-06550: line 9, column 38:
PLS-00103: Encountered the symbol "COP_USR" when expecting one of the
following:
. ( ) , * @ % & = - + < / > at in is mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||

Can anybody suggest about this,as it is very urgent .

Tahnks in advance.
Re: Loading data into excel sheet [message #247918 is a reply to message #247912] Wed, 27 June 2007 09:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and repost.
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
icon2.gif  Re: Loading data into excel sheet [message #250072 is a reply to message #247912] Sat, 07 July 2007 10:33 Go to previous messageGo to next message
sanjeevsapre
Messages: 1
Registered: July 2007
Junior Member
Hi

I have been working on the similar requirement. I have developed a package in pl/sql that can generate excel workbook with multiple worksheets, use styles etc.

See if that helps. It is available here



Regards
Sanjeev
Re: Loading data into excel sheet [message #301912 is a reply to message #250072] Fri, 22 February 2008 04:31 Go to previous messageGo to next message
sunil.madnani
Messages: 36
Registered: March 2007
Location: BHILAI
Member
hello all Gurus,
When i am using the above procedure on this topic i am getting an error that
identifier 'OWA_SYLK.OWASYLKARRAY' must be declared
why is it so;

Can any of you tell me the solution;

Sunil Madnani
Re: Loading data into excel sheet [message #301914 is a reply to message #301912] Fri, 22 February 2008 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either it does not exist or you don't have the privilege to use it.

Regards
Michel
Re: Loading data into excel sheet [message #302040 is a reply to message #247912] Fri, 22 February 2008 14:44 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
You query makes no sense, you have single quotes within a quoted string, you must use two single quotes to let oracle know that they are embedded. Do the following.

'select a.logon_id,
decode(role_id,''grant'',''Y'',''N'')) Grant,
from (SELECT LOGON_ID,ROLE_ID FROM privileges GROUP BY LOGON_ID,ROLE_ID) a,s_temp b
where a.logon_id=b.logon_id group by a.logon_id'

[Updated on: Fri, 22 February 2008 14:44]

Report message to a moderator

Re: Loading data into excel sheet [message #317815 is a reply to message #247912] Sat, 03 May 2008 21:12 Go to previous messageGo to next message
preity
Messages: 3
Registered: May 2008
Junior Member
Hi where you got this code(owa_sylk) from and is it free source code.

Thanks

[Updated on: Sat, 03 May 2008 21:12]

Report message to a moderator

Re: Loading data into excel sheet [message #317835 is a reply to message #317815] Sun, 04 May 2008 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:728625409049
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:828426949078
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:769425837805

Check which version is the latest one.
You can use in production it if you paid your Oracle license.

Regards
Michel

[Updated on: Sun, 04 May 2008 01:56]

Report message to a moderator

Re: Loading data into excel sheet [message #318050 is a reply to message #247912] Mon, 05 May 2008 07:57 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
My understanding is that it was written by Tom, but is only copyrighted so that no one can take the code and sell it. Tom has always offered the code AS IS and have told many people to use it without asking about license. I use it with heavy modifications at my site, have told tom that I did and he didn't care.
Re: Loading data into excel sheet [message #318113 is a reply to message #247912] Mon, 05 May 2008 14:04 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
As long as your organisation supports Excel 2003 or greater, create xml spreadsheets. There is a perfectly good link above from sanjeevsapre which shows a sample of what can be done.

owa sylk was good in its day as there was no good alternative, but compared to the power of xml, its a bit old and cranky now (although toms package automates some tasks that still give it a bit of appeal).

Plus, if you create your code well (ie separate metatdata from actual data), then you can easily move with the times to support all the new open standards for spreadsheets going forwards.

Re: Loading data into excel sheet [message #318754 is a reply to message #318113] Wed, 07 May 2008 19:14 Go to previous message
preity
Messages: 3
Registered: May 2008
Junior Member
I used the owa_sylk utility and its just perfect.
However i am definately interested in XML version. I searched for Sanjeevsapre solution for XML spreadsheet but couldnt find it.Can you please help me with this.
Thanks
Previous Topic: installer fail when install oracle client 11g
Next Topic: Materialized view
Goto Forum:
  


Current Time: Sat Dec 03 18:31:59 CST 2016

Total time taken to generate the page: 0.04288 seconds