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
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
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
Once again - this time (more) correct
Mette, October 22, 2006 - 5:17 am UTC
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
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
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
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