appending to the end of excel file
August 16, 2002 - 12pm Central time zone
Reviewer: A reader
this is great! i have a view file in sql script that i save as text file. in excel i did visual
basic to run this script and this works. my problem is i have to run the complete month over aqain
to get all data. i can't seem to append to the bottom of file.
any sugestions?
THANKS,
Lorenzo
Followup August 16, 2002 - 12pm Central time zone:
l_output := utl_file.fopen( p_dir, p_filename, 'a' );
instead of
l_output := utl_file.fopen( p_dir, p_filename, 'w' );
Performance
October 4, 2002 - 12pm Central time zone
Reviewer: A reader
Hi, Tom,
Did you ever test the code with large number of reocrds
in a table?
I have a table with columns > 80 and number of records
> 500,000. When I exported table data to the file with this procedure, seems take forever. I think
it is because the "fetch" operation-- one at a time, slow everything down. Do we have a workaroud
to that please? Or do we have another Package/method to run them as batch process to save time?
Thanks
Followup October 4, 2002 - 6pm Central time zone:
search this site for array_flat (pro*c)
or goto asktom.oracle.com/~tkyte and get the unloader
or rewrite the code to use dbms_sql array processing (probably more work then you want to do)
please check this
October 5, 2002 - 1pm Central time zone
Reviewer: aamir from K.S.A.
i'm just a little programmer... i just want to know to how can i lock a table so that no can fetch
the records except me?...
Followup October 5, 2002 - 1pm Central time zone:
You would have to buy a database with extremely poor concurrency controls such as Access, SQLServer
or DB2.
In Oracle -- we don't block readers of information.
What is your goal, what is your requirement. I might be able to help you achieve your goal -- but
not using the solution you are trying to use.
(oh and hey -- what the heck does this have to do with "create a file for excel"???? especially
since during the time you put this here -- I was actually accepting questions?????? use the right
place for asking questions -- the ask question link on the home page.
Creating an Excel file in client machine
October 6, 2002 - 8am Central time zone
Reviewer: Srikant from India
That was great. But Tom, is there any way in which I can save it in the client's machine itself.
Actually my requirement will be like this. When we run a report, as they can convert it into a pdf
or rtf file, the user should be able to save the report in Excel format. Any way in achieving
this?
Thanks a lot for your priceless service.
Sri
Followup October 6, 2002 - 9am Central time zone:
If you are using oracle reports, output type can be set to CSV for excel.
If you are using forms, rewrite the routine to use text_io instead of utl_file
If you have sqlplus available, see
http://asktom.oracle.com/~tkyte/flat/index.html
Creating an Excel file from Oracle Reports
October 13, 2002 - 1am Central time zone
Reviewer: Srikant from India
Thanks for you reponse Tom. But I couldn't achieve it.
I used the following settings in System Parameters for exporting my report in Oracle Reports
(Report Builder 6.0.8.11.3).
DesFormat: Data Type - Character, Width - 1024, Initial Value - csv
DesName: Data Type - Character, Width - 1024, Initial Value - c:\mycsv.csv
DesType: Data Type - Character, Width - 80, Initial Value - File
I am getting the following error: - REP-0826: Invalid Printer driver 'csv' specified by parameter
DESFORMAT
I have Office 2000 installed in my system and Excel is working fine. Kindly advice.
Followup October 14, 2002 - 7am Central time zone:
sigh, did you pull up the documentation to see what the values for these parameters might be?
I did, look up DESFORMAT in the documentation for reports. You'll see you can get pdf, html, html
with style sheets, rtf, DELIMITED <<<====, and XML.
create a file for excel using utl_file
March 5, 2003 - 5am Central time zone
Reviewer: S.Srinivasan from Singapore
Hi Tom!
I am getting the following error when I use dump_csv funtion with test_dump_csv procedure.
Could you please help me.
Thanks.
S.Srinivasan
sql>exec test_dump_csv;
BEGIN test_dump_csv; END;
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 120
ORA-06512: at "SYS.UTL_FILE", line 204
ORA-06512: at "SIDAS.DUMP_CSV", line 15
ORA-06512: at "SIDAS.TEST_DUMP_CSV", line 5
ORA-06512: at line 1
Followup March 5, 2003 - 8am Central time zone:
set your utl_file_dir init.ora parameter do you can actually write files.
create a file for excel using utl_file_dir parameter in init.ora
March 5, 2003 - 8pm Central time zone
Reviewer: S.Srinivasan from Singapore
Hi Tom!
Thanks for your quick response. I have already set up the utl_file_dir in the init.ora file. I
would like to have a CSV output for a query.
Need your help.
Thanks.
S.Srinivasan
Followup March 5, 2003 - 8pm Central time zone:
14 begin
15 l_output := utl_file.fopen( p_dir, p_filename, 'w' );
16
see -- line 15 is the fopen.
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 120
ORA-06512: at "SYS.UTL_FILE", line 204
ORA-06512: at "SIDAS.DUMP_CSV", line 15
ORA-06512: at "SIDAS.TEST_DUMP_CSV", line 5
ORA-06512: at line 1
your error -- coming from line 15
you either
o did not restart after putting the utl_file_dir in
o or the utl_file_dir is NOT set to include the directory you are using
the open of the file is failing -- make it so that you can actually open a file in that directory.
Just code a small block
declare
l_output utl_file.file_type;
begin
l_output := utl_file.fopen( 'directory', 'filename', 'w' );
end;
and put in the directory and file name you are trying to use.
When that fails -- fix it so it doesn't.
Then you'll be able to run dump_csv.
create a file for excel using utl_file_dir
March 6, 2003 - 3am Central time zone
Reviewer: S.Srinivasan from Singapore
Hi Tom!
Thank you very much for the response & Code. Wonderful!I got it.
The code I tried with invalid path. Now I specify the utl_file_dir path within quotes(') as a file
path, it works.
Once again Thank you very much.
if there is a ' in p_query ....
March 6, 2003 - 9am Central time zone
Reviewer: Joe
hi,Tom, the dump_csv works great with the sql like :
select * from employee.
but it fail with the query with ' , like
select * from employee where name='joe',
I tried to use this query instead, but fail either.
...where name=\'joe\'
any suggesttion? thx.
Followup March 6, 2003 - 9am Central time zone:
to get a quote in a string:
'select * from employee where name = ''joe'' '
you use two quotes. BUT, if possible -- do this:
dbms_application_info.set_client_info( 'joe' );
...
dump_csv( ...., 'select * from employee where name = userenv(''client_info'')', ..
for that will work like a BIND VARIABLE.
Struck with Reporting...
March 17, 2003 - 9am Central time zone
Reviewer: Vj from india
Tom,
We have an application running on java, oracle 8.1.7...we generally have been using crystal reports
as a reporting engine...beleive me, we faced enough problems..
also considering that the client want to print the reports in the line printer, which we couldnt do
using crystal.. the next best thing came to my mind was PL/SQL..(i couldnt think of anything)
In the sense, pump the RECORDS required for report formatted properly using PL/SQL procedures and
dump it into the CLOB col in the table with session id etc..
the front-end guys would take the report and start printing it to the line printer...
1. I want to know, is this approach is right ?
2. Do you see an performance constraint on this ?
there might be some reports which can run upto 2000 pages..
3. Is there is any other alternate for this ?
Need your suggestion..
Followup March 17, 2003 - 10am Central time zone:
that is one approach. another would be to have plsql use utl_file to create a file on the server
which you then send to the printer.
Struck with Reporting...
March 17, 2003 - 9am Central time zone
Reviewer: Vj from india
Tom,
We have an application running on java, oracle 8.1.7...we generally have been using crystal reports
as a reporting engine...beleive me, we faced enough problems..
also considering that the client want to print the reports in the line printer, which we couldnt do
using crystal.. the next best thing came to my mind was PL/SQL..(i couldnt think of anything)
In the sense, pump the RECORDS required for report formatted properly using PL/SQL procedures and
dump it into the CLOB col in the table with session id etc..
the front-end guys would take the report and start printing it to the line printer...
1. I want to know, is this approach is right ?
2. Do you see an performance constraint on this ?
there might be some reports which can run upto 2000 pages..
3. Is there is any other alternate for this ?
Need your suggestion..
Excel output from a report
March 23, 2003 - 10pm Central time zone
Reviewer: S.L
hi tom,
first sorry for asking a question in this way. when ever i try to ask a question always i am
getting that now you are not accepting any questions. so, i am asking you this way.
I read all the information given by you for the delimited output. But what is my problem is i am
able to convert my report output into delimited output and able to view the file in excel. but the
record of my output is displaying along with the headers. I tried all the measures which i can but
unable to find out why the output is coming like this. can you help me and tell me the solution.
thank you very much
Followup March 24, 2003 - 7am Central time zone:
try otn.oracle.com -> discussion forums -> developer
I've never written a report in my life.
Outputting Data from Reports To Excel
March 24, 2003 - 1am Central time zone
Reviewer: Nivas
hi tom,
I have some tabular reports. When i used Desformat as Delimited and run the report the first
record of the report is displaying in the header line after the headers like
Empno EName Salary Deptno 7796 Scott 10000 101.
How can i avoid this situation.
Need your help.....
Thanks
nivas.
got error with to_char function
March 27, 2003 - 4pm Central time zone
Reviewer: A reader from de, usa
Hi Tom:
when I ran:
declare
l_rows number;
begin
l_rows := dump_csv('select to_char(hiredate, 'yyyymmdd hh24:mi:ss') from scott.emp',
',',
'/tmp',
'test.dat' );
end;
/
got: SP2-0552: Bind variable "MI" not declared.
why is it?
thank you very much
Followup March 27, 2003 - 7pm Central time zone:
'' to get a ' in a string of course....
l_rows := dump_csv('select to_char(hiredate, ''yyyymmdd hh24:mi:ss'') from
scott.emp'
create a file for excel
March 28, 2003 - 9am Central time zone
Reviewer: Tarek from Italy
Hi Tom,
it's the first time i'm writing in your form, hope this is
the correct way. It's a few days i started reading your form,
it's very helpful (although a little bit difficult.. i'm a newbie).
I tried this procedure and function and it works great.
One thing. When executing the procedure test_dump_csv is there a way to run the query dinamically?
I mean is it possible to put instead of the query a variable and then when running the procedure
supply the query i want to run?
Thanks a lot.
I know almost nothing about PL/SQL. Where can i find a simple book that teaches it? (very simple,
not for gurus!!)
Followup March 28, 2003 - 9am Central time zone:
the query by definition is "dynamic"....
you can:
exec dump_csv( '&Query', ',', '/tmp', 'foo.txt' );
and that'll ask you for a query at runtime in sqlplus.
I suggest:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/toc.htm
to learn PLSQL -- it is just a language like anyother, with control structures and so on. It is
not a terribly hard language. Move onto the books from bookstores only after reading that one.
You need to have the foundation before being told how to do stuff in the language.
create a file or excel
March 28, 2003 - 12pm Central time zone
Reviewer: Tarek
I tried your tip but get this error:
SQL> exec dump_csv('select * from all_users',',','/tmp','test.csv');
BEGIN dump_csv('select * from all_users',',','/tmp','test.csv'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'DUMP_CSV' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> get test_dump_csv.sql
1 create or replace procedure test_dump_csv
2 as
3 l_rows number;
4 begin
5 l_rows := dump_csv( 'select *
6 from all_users
7 where rownum < 25',
8 ',', '/tmp', 'test.csv' );
9* end;
SQL> exec test_dump_csv;
PL/SQL procedure successfully completed.
When i run the procedure it works.
The only difference i have seen from procedure to directly running the function is that variable
l_rows. What it is for?
Thanks for the pl/sql link.
Followup March 28, 2003 - 4pm Central time zone:
dump_csv is a function use
exec dbms_output.put_line( dump_csv( ..... ) )
Column Header
November 7, 2003 - 9am Central time zone
Reviewer: John from USA
Hi, TOm,
This is a very nice scripts, but what if I want to add
header to each data column(name of the columns, like select stmt result in SALPLUS), how to do
that?
I check the white paper for DBMS_SQL, it seems to me that a bit awkward.
Help please,
Thanks
Can I load the CSV data output in my db without using sqlldr
March 26, 2004 - 4am Central time zone
Reviewer: Subodh Deshpande from India
Hi Tom,
1) How to load this data generated in CSV file to the Oracle Database, I have heard about External
Tables in 9i (and if i mistake not it also uses sqlldr..) how do it in 8.1.7,
2)I want to develop an inerface like form etc..
I want to provide two menu items one to generate and one to load the data which is CSV format.
thanx in advance...subodh
Followup March 26, 2004 - 9am Central time zone:
1) sqlldr using a control file like this (change | to , or whatever)
LOAD DATA
INFILE *
INTO TABLE emp
REPLACE
FIELDS TERMINATED BY '|'
(
empno
,ename
,job
,mgr
,hiredate
,sal
,comm
,deptno
)
BEGINDATA
7369|Smith|CLERK|7902|17-DEC-80|800||20
7499|ALLEN|SALESMAN|7698|20-FEB-81|1600|300|30
7521|WARD|SALESMAN|7698|22-FEB-81|1250|500|30
7566|JONES|MANAGER|7839|02-APR-81|2975||20
2) ok, go for it?
Can I load the CSV data output in my db without using sqlldr
March 27, 2004 - 1am Central time zone
Reviewer: Subodh Deshpande from India
Hi Tom,
Sorry to bother you.. probably this may be one of the leangiest question you have ever seen, and I
think I couldn't place my doubt properly, as I am confused in usuing the proper tool of oracle in
my following case..
1) we are constrainted to use replication so at different locations our databases are different
without any logical structural change, and so we are forced to use of csv files whenever we are
required to send the data urgently to any of the locations..
2) In Client Server Environment using 8.1.7 as db, A db Client needs to be installed if we want to
load the data using sqlldr from client end, what we should do in web based architecture ?
3)Whether its Client Server or Web based, if am supposed to use sqlldr, it means
3.1) the user will directly interact with database, defeating the security of database as end users
have tendency to inform the password to others, when some on is going on leave..etc..
3.2) a IT/system person will have to do the this job, i will have to dedicate a person for this,
from my team, as every dept will need to send urgent data to other loaction.
3.3) what we are thinking is when our end user wishes to send certain transaction among of his/her(
say out of 50
custmer orders booked, he want to sent selected 5 of which say despatches are held), an interface
will generate the csvs for those, the user will mail from his desktop at the desired location, at
the desired location, another end user will save his csv at his/her end, and will use recevied cvs
file as input in interface provided, the interface will load the data in db of that locations
4) so am looking for a suitable solution which will avoid use of sqlldr and sending and receiving
of data to certain location will be available to the end users just as one of the attribute of that
transaction, and so i require your gudence,on whether can it be a workable, feasable solution or
how oracle technology can help me in such complex matters
5) please point out my each and every mistake freely and sorry for such a long question..
have a nice weekend and excellent start of new week -:)
thanx in advance...Subodh
Followup March 27, 2004 - 10am Central time zone:
why would not an administrator on the DB server itself be responsible for loading up this important
stuff?
3.1 is sort of laughable -- why does the "web" remove the need for passwords?
You can load data using ANY 3gl you want in your middle tier. C can do it, PLSQL in the server can
do it, VB can even do it, java can do it. You can write code to do virtually *anything* you want.
Can I load the CSV data output in my db without using sqlldr
March 28, 2004 - 4am Central time zone
Reviewer: Subodh Deshpande from India
Hi Tom,
We want to this just to reduce the dependability on systems dept, yes what you are saying in 3.1 ,i
got it, web is not going to remove the need of passwords.
thanx a lot -:)..subodh
Loading comma delimited CVS file
April 22, 2004 - 12pm Central time zone
Reviewer: Nirmal from USA
Tom,
Could you give me an example to load a comma delimited cvs file using utl_file utility.
Thanks in advance..
Is it possible
April 26, 2004 - 5pm Central time zone
Reviewer: A reader
Is it possible to write to a Excel file directly, instead of a csv file?
opening excel/word from forms
May 21, 2004 - 7am Central time zone
Reviewer: anurag from INDIA
Hi!,
How do we call excel/word application thru forms. Pl. explain with example.
thanks
Followup May 21, 2004 - 10am Central time zone:
host( "winword" )
is one way. OLE would do it as well, it is documented.
Proprietary XLS format
May 21, 2004 - 11am Central time zone
Reviewer: Yogesh from Peterborough, UK
Any resource for knowing the XLS proprietary format?
Followup May 21, 2004 - 11am Central time zone:
probably -- google it.
Just use XSQL
May 21, 2004 - 1pm Central time zone
Reviewer: Steve Pilgrim from England
With the later releases of Excel (2000 and beyond) you can read a URL directly into the
spreadsheet.
So my solution to this was just to XSQL coupled with a simple XSLT to translate into a simple
table.
It's simple. No files to load, no user names or passwords (as these are in iAS) and use can supply
parameters to the XSQL query.
Hope this helps - once I got going it's the only thing I use.
XLS file
August 16, 2004 - 12pm Central time zone
Reviewer: Yogesh from Pune, India
I want to format some of the rows, for example make it bold. Can we do something for this ?
Followup August 16, 2004 - 7pm Central time zone:
sylk file formats support that. google sylk, you can even do formulas and so on.
Excel columns
August 18, 2004 - 5am Central time zone
Reviewer: Yogesh from Pune, India
I've executed this script and created a CSV file on UNIX box. I transferred that file using FTP
(binary mode / normal mode) to windows machine. At this point I'm facing the problem. I can open
this CSV file with excel but every row is a part of single cell. Ideally CSV values should go in
different columns, but here they become part of first cell i.e. A1, B1 etc. Am I doing something
wrong?
Followup August 18, 2004 - 8am Central time zone:
well, you really wanted "text mode" to make windows happy with your file (they want two things to
end a line)
but when I open a file "emp.csv", it naturally parsed it.
if I open "emp.dat", i get a text import wizard and it let me tell it how to parse it, perhaps you
just hit "next" and didn't select comma as your delimiter.
To Yogesh
August 18, 2004 - 10am Central time zone
Reviewer: denni50 from na
open a blank Excel Workbook(worksheet)
go to:
Data>Import External Data>Import Data> (browse for directory where .csv file is stored) and open.
A Text Import Wizard should now appear:
a) make sure delimited and 437 OEM U.S. is selected
b) click next - uncheck TAB, check COMMA(you should now
see all the data broken out into columns)
c) click NEXT , then click NEXT again...
(you should now see a screen with =$A$1)
click OK and all your data should now import into
columns
End user
August 18, 2004 - 11am Central time zone
Reviewer: Yogesh from Pune, India
I don't want any manual intervention. Secondly, can't ask end users to parse the file!! Cant we
automate this?
Followup August 18, 2004 - 11am Central time zone:
I used excel 2000
i did file open
bam, done, data right there. nothing fancy. no wizards.
so, is the file named something.csv?
if that is not working for you -- time to askbill@microsoft.com?
Extenstion
August 18, 2004 - 12pm Central time zone
Reviewer: Yogesh from Pune, India
Its all about extensions. Extension of my file was .XLS, which was creating problem. I changed it
to CSV and it is working fine now. Thanks, CSV clicked.
Tom , UTL FILE is giving run time error when I try to run this
August 18, 2004 - 12pm Central time zone
Reviewer: Jai Vrat Singh from Singapore
Hi Tom , it is gving error in this case.. when I try to run the anonymous block .. I tried this at
two three places.
SQL> declare
2 l_rows number;
3 begin
4 l_rows := dump_csv( 'select table_name from all_tables where rownum < 5',
5 ',',
6 'C:\',
7 'test.dat' );
8 dbms_output.put_line( to_char(l_rows) ||' rows extracted to ascii file' );
9 end;
10 /
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 120
ORA-06512: at "SYS.UTL_FILE", line 204
ORA-06512: at "JAIVRAT.DUMP_CSV", line 18
ORA-06512: at line 4
The version is
_____________________________________
SQL> select banner from v$VERSION;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE 9.0.1.1.1 Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production
_________________________________________________________
Followup August 18, 2004 - 12pm Central time zone:
reread this page -- asked and answered and asked and answered ......
just ctl-f for unhandled.
Can I create a file in Excel format
August 18, 2004 - 5pm Central time zone
Reviewer: Alex from Houston
Tom,
I was able to put the data by "select * from temp" into an excel file but all data was put in
single one column in the file.
How do I put the data in the seperated columns like the table?
Thanks a lot!
Followup August 18, 2004 - 11pm Central time zone:
ops$tkyte@ORA9IR2> set colsep ,
ops$tkyte@ORA9IR2> select * from scott.dept;
DEPTNO,DNAME ,LOC
----------,--------------,-------------
10,ACCOUNTING ,NEW YORK
20,RESEARCH ,DALLAS
30,SALES ,CHICAGO
40,OPERATIONS ,BOSTON
might be the "easiest" but you can also
select c1 || ',' || c2 || ',' || c3 ....
and even "quote" things if you need
Can I create a file in Excel format
August 19, 2004 - 11am Central time zone
Reviewer: Alex from Houston
Tom,
Thanks for your quick response but I didn't get what I wanted.
What I wanted was in the excel file, c1 is put in column A, c2 is put in column B, c3 is put in
column C, etc. But I ran the script and found c1, c2 and c3 ALL were put in column A. How do I
split them?
Thank you very much!
Followup August 19, 2004 - 4pm Central time zone:
make sure the file is named .csv and excel will do the rest as long as you have them separated by
columns (this is really an "askexcel" question... one that we just asked and answered right above!)
open excel from oracle reports
October 13, 2004 - 12pm Central time zone
Reviewer: Shilei from Buffalo, NY USA
Do you happen to know how to open excel from Oracle Reports 6i?
I can use "host ('file name')", In Forms; what can I use in reports?
Followup October 13, 2004 - 2pm Central time zone:
try otn.oracle.com => discussion forums
i've never actually run reports developer in my life.
Another version of Dump_Csv
October 28, 2004 - 2pm Central time zone
Reviewer: Bill from Liverpool, NY USA
I took your original code and modified it to make it friendler for EXCEL
import and thought that your readers might like to use it. I also added error
handling into the procedure. The following code fragment would run the
function on a unix server and produce a tab delimited output file for a
windows machine.
Please note that if you are not using commas as the column delimited and
are going to import into Excel, do NOT name the file with the CSV extension.
Excel assumes that any file with a csv extension will be delimited by commas.
If you name it something like output.txt, it will use its import wizard,
where tab is the default methoid to delimit the fields.
------------------------------
declare
cnt number;
begin
cnt := dump_csv('select object_name,owner,object_type from
all_objects order by owner,object_name',chr(9),null,'/tmp/test.txt',
'OBJECT_NAME,OWNER,OBJECT_TYPE',CHR(13));
END;
-----------------------------
create or replace function dump_csv( p_query in varchar2,
p_separator in varchar2 default ',',
p_dir in varchar2 ,
p_filename in varchar2,
p_header IN VARCHAR2,
p_endline IN VARCHAR2 DEFAULT chr(13))
-- This routine makes certain assumptions.
-- 1) There must be a query and it can't be greater then 32K.
-- 2) The separator must only be one character in length and can't be
-- a CR, LF, binary 0, or null (easy to change).
-- 3) If the p_dir parameter is null, the p_filename must contain the
-- path and filename (/tmp/output.txt)
-- 4) If the p_header parameter is not null, then insert it into the first
-- row of the output file. If the p_separator parameter is not a comma,
-- the comma's in the header string will be replaced with the new
-- separator. so to add a header use 'NAME,FIRST_NAME,LAST_NAME' and if
-- the separator is a tab, what is put into the file would be
-- 'NAME<tab>FIRST_NAME<tab>LAST_NAME'
-- 5) The value of p_endline will be appended to the end of each line of the
-- output file. It can be used to add a carriage return before the
-- Line Feed is inserted by the NEW_LINE (unix). If the server is running
-- on a windows machine, set this to null since the NEW_LINE will save
-- a CR,LF pair anyway. This can also be used if you needed to put
-- something at the end. For exanple "'|'||CHR(13)" which would put a
-- vertical bar and CR,LF on each line on a unix machine.
--
-- The following are the returned error codes
-- -1 The query is empty
-- -2 The output filename is empty
-- -3 The separator is invalid.
-- -4 The filename only contains the path, no filename specified.
-- -5 The output file can not be opened.
-- -6 The query could not be parsed. It was illegal.
-- 0 The query returned NO records.
-- >0 The number of records returned.
return number
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(2000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
l_dir VARCHAR2(500);
l_filename VARCHAR2(32);
x_pnt NUMBER(4);
l_header VARCHAR2(2000);
begin
-- sanity check the input
IF p_query IS NULL THEN
RETURN(-1);
END IF;
IF p_filename IS NULL THEN
RETURN(-2);
END IF;
-- Do not allow CR, LF,binary 0, or null to be used as a separator.
-- The length of the separator must be 1 if it exists.
IF p_separator IS NULL OR
p_separator IN (chr(13),chr(10),chr(0)) OR
length(p_separator) > 1 THEN
RETURN(-3);
END IF;
-- If the directory parameter is blank, assume that the directory
-- is included in the filename.
IF p_dir IS NOT NULL THEN
l_dir := p_dir;
l_filename := p_filename;
ELSE
x_pnt := instr(p_filename,'/',-1,1);
-- If no path is specified or no filename is specified,
-- the procedure will not work... get out.
IF x_pnt = 0 OR x_pnt = length(p_filename) THEN
RETURN(-4);
END IF;
l_dir := substr(p_filename,1,x_pnt-1);
l_filename := substr(p_filename,x_pnt+1);
END IF;
-- Check to see if the file can be opened. If ANY error is
-- encountered, exit with a count of -1;
BEGIN
l_output := utl_file.fopen( l_dir, l_filename, 'w', 32767 );
EXCEPTION
WHEN OTHERS THEN
RETURN(-5);
END;
-- Check to see if the query can be processed. if ANY error is
-- encountered, close the output file and exit.
BEGIN
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
EXCEPTION
WHEN OTHERS THEN
utl_file.fclose( l_output );
RETURN(-6);
END;
-- If the p_header parameter is not null, then insert the line as
-- the first line in the output file. This is used if the user wants
-- to insert column headings. Make sure to use a comma in your header
-- line and the routine will replace all comma;s with the specified
-- separator.
l_header := NULL;
IF p_header IS NOT NULL THEN
l_header := p_header;
IF p_separator <> ',' THEN
l_header := REPLACE(l_header,',',p_separator);
END IF;
END IF;
-- Loop through all the parameters for the select. To support
-- unknown querys, the assumption is that the query will return
-- all columns as varchar2 columns where the data is correctly
-- formatted for inport. A maximum of 255 columns are supported
-- in the query. Each column can't be greater then 2000
-- characters in length.
for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;
-- This define_column insures that at least one column is defined for the
-- routine.
dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
-- Fire the query.
l_status := dbms_sql.execute(l_theCursor);
-- Loop through all the rows returned by the query. Build up the output file
-- by looping through the defined columns.
loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
IF l_cnt = 0 AND l_header IS NOT NULL THEN
utl_file.put(l_output, l_header);
utl_file.put(l_output, p_endline);
UTL_FILE.NEW_LINE (l_output,1);
l_cnt := 1;
END IF;
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := p_separator;
end loop;
utl_file.put(l_output, p_endline);
UTL_FILE.NEW_LINE (l_output,1);
l_cnt := l_cnt+1;
end loop;
-- Processing done. close the cursor and output file.
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
-- Return the number of rows built in the csv file.
return l_cnt;
-- If Any error occures outside of the errors checked above, then raise
-- and error and blow out the procedure.
EXCEPTION
WHEN OTHERS THEN
RAISE;
end dump_csv;
/
Nothing in here about Excel formatted file loaded into Oracle
January 5, 2005 - 1pm Central time zone
Reviewer: Don D. from Sacramento, CA USA
Each 'solution' required first saving the Excel file to a .csv formatted file. How to deal with
loading data from an Excel formatted file into Oracle (without first saving the Excel spreadsheet
to a .csv file)?
How to take care of the single quotes
January 12, 2005 - 7pm Central time zone
Reviewer: Esg
I need a routine which will pluck all the quotes and change them into chr(39)
Sqls like the above will be passed from one of our forms to a report which then writes the csv
output to network. Each time the sql will be different, and will contain many single quotes as seen
below. But oracle report will error out, if I try to run the sql as it is. I need to convert all
the single quotes into chr(39) and then pass it on to the dump_csv program which dumps it to
network.
Can you please suggest a solution for the same.
when the oracle report receives the sql it will be in a parameter calle pqry(
n:= dump_csv(:pqry, :pqry_name) is how I will execute the dump_csv)
select
a, b, c, '200406'
from x,y,z
where x.q = y.q and y.n=z.n
and x.q = '200406' and
y.name='Enron' and
n.party='Pipeline'
Thanks in advance.
Followup January 12, 2005 - 8pm Central time zone:
no -- if you just changed them into chr(39) what you have?
invalid sql :)
and then you have a bind variable problem (eg: you AIN'T USING THEM).
but if you are calling a procedure like you describe, what you GOT works.
if you bind to :pqry the literal you have, it would work. so must be a different problem (and
please add "alter session set cursor_sharing=force" to dump_csv!)
Please be more elaborate
January 13, 2005 - 12am Central time zone
Reviewer: Esg
Tom, please be more elaborate about the below
o.k. I got that when I pass the parameter :pquery directly to dump_csv , then I dont have to worry
about the single quotes. Fine.
Now tell me about the below 2
1.Why do I need to use cursor_sharing=force
2.Well, in a scenario where I have to get rid of the single quotes and should not have invalid
sql, then how should I make it happen. Pleas help.
------------------------------------------------------------
no -- if you just changed them into chr(39) what you have?
invalid sql :)
and then you have a bind variable problem (eg: you AIN'T USING THEM).
but if you are calling a procedure like you describe, what you GOT works.
if you bind to :pqry the literal you have, it would work. so must be a
different problem (and please add "alter session set cursor_sharing=force" to
Followup January 13, 2005 - 8am Central time zone:
I'm always elaborate :)
But, I suppose you want me to elaborate on the response....
1) because you want to use BIND VARIABLES (where have I heard that before). And if you pass a
string with hard coded literals into a routine that will be executed frequently, you wouldn't be
using bind variables.
2) huh?
Replace
January 13, 2005 - 7pm Central time zone
Reviewer: Esg
Tom in the above query I want to replace all single quotes with ||chr(39)||. How can I do it.
Please help.
Followup January 13, 2005 - 8pm Central time zone:
why, it would only but result in SQL that cannot possible execute.
but, if you really want to -- replace(), it is a function.
Query
June 30, 2005 - 5am Central time zone
Reviewer: sindhu from INDIA
hi tom
i need a help.i need to know how do i open an xl file fom vb only n not vba.when i open it asks me
"the xl file is ready for editing .do u want to "read or write?"
i dont want this.the xl file should open directly.
sending u my code.
set a= fso.createTextFile("pathname of thefile.xls")
sExcelfile=Store the above in this string
set xlapp = new excel.application
set xl book = xlapp.workbooks.open(sexcelfile)
xlapp.visible = true
Followup June 30, 2005 - 9am Central time zone:
"u" might answer you when "u" gets it...
I (who is not "u", but me) have never written a line of Visual Basic code in my life -- and if my
luck holds out, I'll be able to say that till I die :)
Tom
June 30, 2005 - 11am Central time zone
Reviewer: denni50 from na
don't knock it until you've tried it.
VB is a cool, fun language and very powerful.
Although it has origins to the original BASIC
language developed in the '60's it's a far
different animal today. I love the GUI aspect
of VB and working with objects.
We are heavy users of Access and Excel and VBA
has enabled us to develop far more sophisticated
financial spreadsheets and data manipulation in Access
that Access(SQL) just doesn't provide with it's limitations.
VB rocks!
:-)
Followup June 30, 2005 - 12pm Central time zone:
I don't program *windows*
VB doesn't run on my platform of choice :)
PLSQL rocks, Analytics roll...
PLSQL, Analytics Rocks!....
June 30, 2005 - 1pm Central time zone
Reviewer: denni50 from na
...can't argue with that!
;~)
getting sql query in report
March 23, 2006 - 4am Central time zone
Reviewer: Vinay Krishna Ravi from India
how can i know the actual query created by reports
in reports 6i after running report?
Followup March 23, 2006 - 10am Central time zone:
by using sql_trace=true. It'll be written to a trace file.
Sending query output on printer
February 12, 2007 - 6am Central time zone
Reviewer: Dulal from Bangladesh
Hi Tom,
I want to send query output on printer from sqlplus.
I write codes but does not print but shows on screen.
Where the problems? Please help.
Thanks in advance.
My system : Windows 2000 Pro
Oracle : Oracle 10g R2
Printer : HP Laser Jet 1200 series working on LAN
-----My Script---------
SPOOL TEMP
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN DEPTNO HEADING 'DEPT NO'
COLUMN ENAME HEADING 'EMP NAME'
COLUMN SAL HEADING 'SALARY' FORMAT 99,999
BREAK ON DEPTNO SKIP 1 ON REPORT
COMPUTE SUM OF SAL ON DEPTNO
COMPUTE SUM OF SAL ON REPORT
SET PAGESIZE 24
SET NEWPAGE 0
SET LINESIZE 70
TTITLE CENTER 'A C M E W I D G E T' SKIP 2 -
LEFT 'EMPLOYEE REPORT' RIGHT 'PAGE:' -
FORMAT 999 SQL.PNO SKIP 2
BTITLE CENTER 'COMPANY CONFIDENTIAL...'
SELECT DEPtno, ename, SAL FROM EMP WHERE SAL>1200 ORDER BY DEPTno;
SPOOL OUT
------End Script--------
Followup February 12, 2007 - 10am Central time zone:
print your file now, open in notepad or whatever and print it, all you did was create a file.
Sending query result to printer
February 12, 2007 - 11pm Central time zone
Reviewer: Dulal from Bangladesh
Hi Tom,
Would you kindly give me a sample code to print the query
result directly from sqlplus without creating any file or others.
Thanks in advance.
My system : Windows 2000pro
Oracle : Oracle10gR2
Printer : HP Laser Jet 1200 series working on LAN
Followup February 13, 2007 - 9am Central time zone:
nope - there is no such "code", you do things in windows like "file/print"
How to used ole2.obj_type in oracle
September 20, 2007 - 10pm Central time zone
Reviewer: toanphamkhanh from vietnam
Hi all!
Now, i created a file excel from oracle database query. But i want to format cell in spreadsheet!
I used the following PL/SQL code to convert table data to excel. Sample code as follows.
PROCEDURE EXCEL_FILE IS
cursor dtl is select * from accounts_icd order by auth_dt,ich_ven_cd;
row_num number;
col_num number;
cell_val number;
app_acc OLE2.OBJ_TYPE;
dbs OLE2.OBJ_TYPE;
dc OLE2.OBJ_TYPE;
args_acc OLE2.OBJ_TYPE;
app OLE2.OBJ_TYPE;
args OLE2.LIST_TYPE;
args2 OLE2.LIST_TYPE;
ws OLE2.OBJ_TYPE;
wb OLE2.OBJ_TYPE;
wss OLE2.OBJ_TYPE;
wbs OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
ctr1 NUMBER := 0;
ctr2 NUMBER := 0;
ctr3 PLS_INTEGER := 0; --NUMBER := 0;
m_row number:=1;
m_len number:=0;
m_flag number:=0;
Begin
---------------- Initialise Excel
app := OLE2.CREATE_OBJ('Excel.Application');
OLE2.SET_PROPERTY(app, 'Visible','True');
wbs := OLE2.GET_OBJ_PROPERTY(app,'Workbooks');
wb := OLE2.INVOKE_OBJ(wbs,'Add');
wss := OLE2.GET_OBJ_PROPERTY(app,'Worksheets');
ws := OLE2.INVOKE_OBJ(wss,'Add');
---
For I in 1..2 loop
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, m_row); --- Row
OLE2.ADD_ARG(args, I); --- Column
cell := OLE2.GET_OBJ_PROPERTY(ws,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
if I=1 then
OLE2.SET_PROPERTY(cell, 'Value','Auth. Date');
elsif I=2 then
OLE2.SET_PROPERTY(cell, 'Value','ICD Doc.No');
end if;
OLE2.RELEASE_OBJ(cell);
end loop;
--- Heading
-- Data
For J in Dtl loop
For I in 1..2 loop
-- Repeat Row
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, m_row); --- Row
OLE2.ADD_ARG(args, I); --- Column
cell := OLE2.GET_OBJ_PROPERTY(ws,'Cells', args);
OLE2.DESTROY_ARGLIST(args);
if I=1 then
OLE2.SET_PROPERTY(cell, 'Value',J.AUTH_DT);
elsif I=2 then
OLE2.SET_PROPERTY(cell, 'Value',J.ICD_DOC_NO);
end if
end loop
end loop
---- End
OLE2.RELEASE_OBJ(wbs);
OLE2.RELEASE_OBJ(ws);
OLE2.RELEASE_OBJ(wbs);
OLE2.RELEASE_OBJ(wb);
OLE2.RELEASE_OBJ(app);
END;
But the program is error (identifier 'ole2.obj_type' must be declare). I don't understanded!
please tell me why?
How to use ole2.obj_type?
Thanks all!
Header
November 24, 2008 - 8am Central time zone
Reviewer: Marius from Norway
Your function is great, I just hope you can help me add one thing to it? I wish to print the column
header of the query as the first line of the output file with the appropriate seperator so that
when you open it in excel each column gets an appropriate header? For instance if the query is
select * from dba_data_files then the first line of the output file should be:
FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES, BLOCKS, STATUS, RELATIVE_FNO, AUTOEXTENSIBLE, MAXBYTES,
MAXBLOCKS, INCREMENT_BY, USER_BYTES, USER_BLOCKS
December 20, 2009 - 7pm Central time zone
Reviewer: A reader
Hi tom;
I am trying to automate a db capacity report with your function.
For example:
exec test_dump_csv;
this writes to temp.csv following:
"date" ,"total", "free","used"
When I execute this again
It should append the new result to this file, not overwrite.. How can I do that?
|