Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, KK .

Asked: May 02, 2000 - 1:20 pm UTC

Last updated: April 02, 2014 - 6:35 am UTC

Version:

Viewed 10K+ times! This question is

You Asked

Can I create a file in Excel format
by using procedure(utl_file)
or SQL*Plus functions?



and Tom said...



Yes, excel is quite happy with CSV files (comma separated values). Here is a simple stored procedure that shows this at work:

create or replace function dump_csv( p_query in varchar2,
p_separator in varchar2 default ',',
p_dir in varchar2 ,
p_filename in varchar2 )
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;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' );

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

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;

dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );

l_status := dbms_sql.execute(l_theCursor);

loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
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.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );
return l_cnt;
end dump_csv;
/



That takes in any query and creates a CSV file from it on the server. You can use this in sqlplus as well by removing the calls to UTL_FILE and replacing them with dbms_output.put calls instead. Then, spool the results of the sqlplus session on the client.

Also, see </code> http://asktom.oracle.com/~tkyte/flat/index.html <code>for another sqlplus way to do this



Rating

  (58 ratings)

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

Comments

appending to the end of excel file

A reader, August 16, 2002 - 12:21 pm UTC

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

Tom Kyte
August 16, 2002 - 12:41 pm UTC



l_output := utl_file.fopen( p_dir, p_filename, 'a' );

instead of

l_output := utl_file.fopen( p_dir, p_filename, 'w' );

Performance

A reader, October 04, 2002 - 12:58 pm UTC

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


Tom Kyte
October 04, 2002 - 6:49 pm UTC

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

aamir, October 05, 2002 - 1:27 pm UTC

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?...

Tom Kyte
October 05, 2002 - 1:34 pm UTC

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

Srikant, October 06, 2002 - 8:49 am UTC

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

Tom Kyte
October 06, 2002 - 9:52 am UTC

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
</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>




Creating an Excel file from Oracle Reports

Srikant, October 13, 2002 - 1:38 am UTC

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.

Tom Kyte
October 14, 2002 - 7:25 am UTC

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

S.Srinivasan, March 05, 2003 - 5:20 am UTC

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

Tom Kyte
March 05, 2003 - 8:02 am UTC

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

S.Srinivasan, March 05, 2003 - 8:11 pm UTC

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

Tom Kyte
March 05, 2003 - 8:33 pm UTC

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

S.Srinivasan, March 06, 2003 - 3:54 am UTC

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

Joe, March 06, 2003 - 9:08 am UTC

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.


Tom Kyte
March 06, 2003 - 9:17 am UTC

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

Vj, March 17, 2003 - 9:19 am UTC

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

Tom Kyte
March 17, 2003 - 10:03 am UTC

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

Vj, March 17, 2003 - 9:22 am UTC

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

S.L, March 23, 2003 - 10:11 pm UTC

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

Tom Kyte
March 24, 2003 - 7:35 am UTC

try otn.oracle.com -> discussion forums -> developer

I've never written a report in my life.

Outputting Data from Reports To Excel

Nivas, March 24, 2003 - 1:08 am UTC

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

A reader, March 27, 2003 - 4:55 pm UTC

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



Tom Kyte
March 27, 2003 - 7:02 pm UTC

'' 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

Tarek, March 28, 2003 - 9:28 am UTC

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!!)

Tom Kyte
March 28, 2003 - 9:32 am UTC

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:
</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96624/toc.htm <code>

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

Tarek, March 28, 2003 - 12:49 pm UTC

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. 

Tom Kyte
March 28, 2003 - 4:08 pm UTC

dump_csv is a function use

exec dbms_output.put_line( dump_csv( ..... ) )




Column Header

John, November 07, 2003 - 9:29 am UTC

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



Tom Kyte
November 07, 2003 - 10:34 am UTC

whats awkward about dbms_sql?

but anyway, there is a describe columns API call you can use:

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_sql2.htm#1004494 <code>

after we parse, you can describe and be told

a) how many columns
b) what their names are

then you can print them out.

Can I load the CSV data output in my db without using sqlldr

Subodh Deshpande, March 26, 2004 - 4:31 am UTC

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

Tom Kyte
March 26, 2004 - 9:24 am UTC

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

Subodh Deshpande, March 27, 2004 - 1:22 am UTC

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



Tom Kyte
March 27, 2004 - 10:46 am UTC

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

Subodh Deshpande, March 28, 2004 - 4:27 am UTC

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

Nirmal, April 22, 2004 - 12:29 pm UTC

Tom,
Could you give me an example to load a comma delimited cvs file using utl_file utility.


Thanks in advance..


Is it possible

A reader, April 26, 2004 - 5:45 pm UTC

Is it possible to write to a Excel file directly, instead of a csv file?

opening excel/word from forms

anurag, May 21, 2004 - 7:22 am UTC

Hi!,

How do we call excel/word application thru forms. Pl. explain with example.

thanks


Tom Kyte
May 21, 2004 - 10:51 am UTC



host( "winword" )

is one way. OLE would do it as well, it is documented.

Proprietary XLS format

Yogesh, May 21, 2004 - 11:47 am UTC

Any resource for knowing the XLS proprietary format?

Tom Kyte
May 21, 2004 - 11:56 am UTC

probably -- google it.

Just use XSQL

Steve Pilgrim, May 21, 2004 - 1:08 pm UTC

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

Yogesh, August 16, 2004 - 12:23 pm UTC

I want to format some of the rows, for example make it bold. Can we do something for this ?

Tom Kyte
August 16, 2004 - 7:40 pm UTC

sylk file formats support that. google sylk, you can even do formulas and so on.

Excel columns

Yogesh, August 18, 2004 - 5:23 am UTC

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?

Tom Kyte
August 18, 2004 - 8:30 am UTC

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

denni50, August 18, 2004 - 10:51 am UTC

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

Yogesh, August 18, 2004 - 11:42 am UTC

I don't want any manual intervention. Secondly, can't ask end users to parse the file!! CanÂ’t we automate this?


Tom Kyte
August 18, 2004 - 11:51 am UTC

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

Yogesh, August 18, 2004 - 12:07 pm UTC

ItÂ’s 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

Jai Vrat Singh, August 18, 2004 - 12:12 pm UTC

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
_________________________________________________________ 

Tom Kyte
August 18, 2004 - 12:23 pm UTC

reread this page -- asked and answered and asked and answered ......

just ctl-f for unhandled.




Can I create a file in Excel format

Alex, August 18, 2004 - 5:39 pm UTC

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!

Tom Kyte
August 18, 2004 - 11:41 pm UTC

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

Alex, August 19, 2004 - 11:11 am UTC

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!

Tom Kyte
August 19, 2004 - 4:51 pm UTC

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

Shilei, October 13, 2004 - 12:40 pm UTC

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?

Tom Kyte
October 13, 2004 - 2:56 pm UTC

try otn.oracle.com => discussion forums

i've never actually run reports developer in my life.

Another version of Dump_Csv

Bill, October 28, 2004 - 2:18 pm UTC

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

Don D., January 05, 2005 - 1:32 pm UTC

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)?

Tom Kyte
January 05, 2005 - 6:15 pm UTC

well, the original question was in fact "create a file for EXCEL", not the other way around.

but you can use odbc if you like:

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

How to take care of the single quotes

Esg, January 12, 2005 - 7:55 pm UTC

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.

Tom Kyte
January 12, 2005 - 8:09 pm UTC

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

Esg, January 13, 2005 - 12:00 am UTC

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


Tom Kyte
January 13, 2005 - 8:51 am UTC

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

Esg, January 13, 2005 - 7:05 pm UTC

Tom in the above query I want to replace all single quotes with ||chr(39)||. How can I do it. Please help.

Tom Kyte
January 13, 2005 - 8:01 pm UTC

why, it would only but result in SQL that cannot possible execute.

but, if you really want to -- replace(), it is a function.

Query

sindhu, June 30, 2005 - 5:51 am UTC

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

Tom Kyte
June 30, 2005 - 9:40 am UTC

"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

denni50, June 30, 2005 - 11:37 am UTC

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!

:-)

Tom Kyte
June 30, 2005 - 12:01 pm UTC

I don't program *windows*

VB doesn't run on my platform of choice :)


PLSQL rocks, Analytics roll...

PLSQL, Analytics Rocks!....

denni50, June 30, 2005 - 1:15 pm UTC

...can't argue with that!

;~)

getting sql query in report

Vinay Krishna Ravi, March 23, 2006 - 4:13 am UTC

how can i know the actual query created by reports
in reports 6i after running report?


Tom Kyte
March 23, 2006 - 10:53 am UTC

by using sql_trace=true. It'll be written to a trace file.

Sending query output on printer

Dulal, February 12, 2007 - 6:10 am UTC

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






Tom Kyte
February 12, 2007 - 10:38 am UTC

print your file now, open in notepad or whatever and print it, all you did was create a file.

Sending query result to printer

Dulal, February 12, 2007 - 11:55 pm UTC

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

Tom Kyte
February 13, 2007 - 9:50 am UTC

nope - there is no such "code", you do things in windows like "file/print"

How to used ole2.obj_type in oracle

toanphamkhanh, September 20, 2007 - 10:14 pm UTC

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

Marius, November 24, 2008 - 8:40 am UTC

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


A reader, December 20, 2009 - 7:49 pm UTC

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?









create a file for excel

Venkat, August 16, 2010 - 3:11 am UTC

Hi Tom,

This is really helpful, but I have a question on top of this. How can we append the data into a Macro-enabled excel file. This is my actual requirement and found this on my way to search.

Thanks
Venkat

Error Completing Export

Jim, September 29, 2010 - 4:27 pm UTC

Hi Tom

I am getting an error trying to export about 6.6 million records. Had no problem with a table of 18,0000 records. 
Plenty of disc space

Any help would be appreciated

OS:    Windows 2003 R2 64-bit
RDBMS: 10.2.0.4.38 64-bit

SQL> exec dbms_output.put_line( dump_csv(' select * from dtree order by dataid ', '%', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree.txt'));
BEGIN dbms_output.put_line( dump_csv(' select * from dtree order by dataid ', '%', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree.txt')); END;

*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 148
ORA-06512: at "SYS.UTL_FILE", line 403
ORA-06512: at "SYS.UTL_FILE", line 861
ORA-06512: at "LLTEST.DUMP_CSV", line 44
ORA-06512: at line 1

Table: Dtree

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------------
 OWNERID                                   NOT NULL NUMBER(10)
 PARENTID                                  NOT NULL NUMBER(10)
 DATAID                                    NOT NULL NUMBER(10)
 NAME                                      NOT NULL VARCHAR2(255 CHAR)
 ORIGINOWNERID                             NOT NULL NUMBER(10)
 ORIGINDATAID                              NOT NULL NUMBER(10)
 USERID                                    NOT NULL NUMBER(10)
 GROUPID                                   NOT NULL NUMBER(10)
 UPERMISSIONS                              NOT NULL NUMBER(10)
 GPERMISSIONS                              NOT NULL NUMBER(10)
 WPERMISSIONS                              NOT NULL NUMBER(10)
 SPERMISSIONS                              NOT NULL NUMBER(10)
 ACLCOUNT                                  NOT NULL NUMBER(10)
 PERMID                                             NUMBER(10)
 DATATYPE                                           NUMBER(10)
 CREATEDBY                                          NUMBER(10)
 CREATEDATE                                         DATE
 MODIFYDATE                                         DATE
 MAXVERS                                            NUMBER(10)
 RESERVED                                           NUMBER(10)
 RESERVEDBY                                         NUMBER(10)
 RESERVEDDATE                                       DATE
 VERSIONNUM                                         NUMBER(10)
 DCOMMENT                                           VARCHAR2(4000 CHAR)
 DCATEGORY                                          VARCHAR2(255 CHAR)
 SUBTYPE                                            NUMBER(10)
 EXATT1                                             VARCHAR2(255 CHAR)
 EXATT2                                             VARCHAR2(255 CHAR)
 ORDERING                                           NUMBER(10)
 MAJOR                                              NUMBER(10)
 MINOR                                              NUMBER(10)
 RELEASEREF                                         NUMBER(10)
 CHILDCOUNT                                         NUMBER(10)
 ASSIGNEDTO                                         NUMBER(10)
 DATEASSIGNED                                       DATE
 DATEEFFECTIVE                                      DATE
 DATEEXPIRATION                                     DATE
 DATEDUE                                            DATE
 DATESTARTED                                        DATE
 DATECOMPLETED                                      DATE
 STATUS                                             NUMBER(10)
 PRIORITY                                           NUMBER(10)
 GIF                                                VARCHAR2(255 CHAR)
 EXTENDEDDATA                                       CLOB
 CATALOG                                            NUMBER(10)
 CACHEEXPIRATION                                    NUMBER(10)

Thanks
Jim

Tom Kyte
September 29, 2010 - 5:47 pm UTC

how big would the resulting output file be do you think. Sounds like you might be hitting a file size limitation.

when the write fails - how big does the file in the OS look?

Followup

Jim, October 04, 2010 - 3:55 pm UTC

Hi Tom

thanks for getting back to me on this

the os file that would be created would be very large from the table of 6,699,818 records

the OS file size created was only 314kb before it errored out

if i take out the order by
exec dbms_output.put_line( dump_csv (' select * from dtree ', '%', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree.txt'));

it errors out after dumping only 5 records = 5kb os file

I then ran a test on another table of  109,721,230 records called dauditnew 
it created an OS file size of 1,860,475kb (1.8gb) with no problems before i killed it

exec dbms_output.put_line( dump_csv(' select * from dauditnew ', '%', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dauditnew.txt'));

desc dauditnew


 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 EVENTID                                   NOT NULL NUMBER(19)
 AUDITID                                   NOT NULL NUMBER(10)
 AUDITSTR                                  NOT NULL VARCHAR2(32 CHAR)
 AUDITDATE                                 NOT NULL DATE
 DATAID                                             NUMBER(10)
 SUBTYPE                                            NUMBER(10)
 USERID                                             NUMBER(10)
 PERFORMERID                               NOT NULL NUMBER(10)
 VALUEKEY                                           VARCHAR2(255 CHAR)
 VALUE1                                             VARCHAR2(4000 CHAR)
 VALUE2                                             VARCHAR2(4000 CHAR)
 APPLICATIONID                                      VARCHAR2(255 CHAR)

*****************



test I ran on dtree table

i thought that it may be my terminator "%" because looking at the EXTENDEDDATA col which is clob i saw "%" in the data so I changed the terminator to use a "$" sign
that dd not work, so i added all the columns except the EXTENDEDDATA column

test10 shows that it worked, but was that the issue ?

apparently not as in the next tests i removed 5 columns at a time leaving in the EXTENDEDDATA col

removing 19 columns and leaving in the EXTENDEDDATA col worked


so I am not sure at this point why it errors out with all columns and the EXTENDEDDATA column 
why it works with all columns except EXTENDEDDATA or a combination of some 19 columns and the EXTENDEDDATA col


--test 10 (all columns except for EXTENDEDDATA (clob) column 

SQL> exec dbms_output.put_line( dump_csv(' select OWNERID,PARENTID,DATAID,NAME,ORIGINOWNERID,ORIGINDATAID,USERID,GROUPID,UPERMISSIONS,GPERMISSIONS,WPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree10.txt'));

PL/SQL procedure successfully completed.


--test20 (added EXTENDEDDATA col (clob) did not work

SQL> exec dbms_output.put_line( dump_csv(' select EXTENDEDDATA,OWNERID,PARENTID,DATAID,NAME,ORIGINOWNERID,ORIGINDATAID,USERID,GROUPID,UPERMISSIONS,GPERMISSIONS,WPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree20.txt'));
BEGIN dbms_output.put_line( dump_csv(' select EXTENDEDDATA,OWNERID,PARENTID,DATAID,NAME,ORIGINOWNERID,ORIGINDATAID,USERID,GROUPID,UPERMISSIONS,GPERMISSIONS,WPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree20.txt')); END;

*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 691
ORA-06512: at "LLTEST.DUMP_CSV", line 49
ORA-06512: at line 1

--test30 (left EXTENDEDDATA in and removed next five columns) did not work

SQL> exec dbms_output.put_line( dump_csv(' select EXTENDEDDATA,ORIGINDATAID,USERID,GROUPID,UPERMISSIONS,GPERMISSIONS,WPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree30.txt'));
BEGIN dbms_output.put_line( dump_csv(' select EXTENDEDDATA,ORIGINDATAID,USERID,GROUPID,UPERMISSIONS,GPERMISSIONS,WPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree30.txt')); END;

*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 691
ORA-06512: at "LLTEST.DUMP_CSV", line 49
ORA-06512: at line 1

--test80 (left EXTENDEDDATA in and removed next five columns) did not work

SQL> exec dbms_output.put_line( dump_csv(' select EXTENDEDDATA,WPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree80.txt'));
BEGIN dbms_output.put_line( dump_csv(' select EXTENDEDDATA,WPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree80.txt')); END;

*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 691
ORA-06512: at "LLTEST.DUMP_CSV", line 49
ORA-06512: at line 1


--test90 (left EXTENDEDDATA in and removed next five columns) did not work

SQL> 
exec dbms_output.put_line( dump_csv(' select EXTENDEDDATA,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree90.txt'));
BEGIN dbms_output.put_line( dump_csv(' select EXTENDEDDATA,GPERMISSIONSWPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree90.txt')); END;

*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 691
ORA-06512: at "LLTEST.DUMP_CSV", line 49
ORA-06512: at line 1


--test100 (left EXTENDEDDATA in and removed next five columns) DID WORK !
SQL> exec dbms_output.put_line( dump_csv(' select EXTENDEDDATA,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree100.txt'));

PL/SQL procedure successfully completed.


--test110 (left EXTENDEDDATA in and added two columns) did not work

SQL> exec dbms_output.put_line( dump_csv(' select EXTENDEDDATA,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree110.txt'));
BEGIN dbms_output.put_line( dump_csv(' select EXTENDEDDATA,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree110.txt')); END;

*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 691
ORA-06512: at "LLTEST.DUMP_CSV", line 49
ORA-06512: at line 1


--test120 (left EXTENDEDDATA in and remove one column) DID WORK !

SQL> exec dbms_output.put_line( dump_csv(' select EXTENDEDDATA,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree120.txt'));

PL/SQL procedure successfully completed.


Thanks
Jim


Tom Kyte
October 05, 2010 - 11:59 am UTC

ah, it might be a line width issue, current releases of utl_file support lines upto 32k in length - but only if you open the file that way. Otherwise the line length was really short.

read about utl_file.open (documentation)

see how to set the max line length

implement a 32k line length in your code (my old code updated to support the wide line length)

and report back....

Line Length Issue Follow-Up

Jim, October 07, 2010 - 3:37 pm UTC

Hi Tom

looks like you were right on 

I was testing with another table and ran into same issue
I created a one-record table to test with a auspect record
I ran the test and got the same error
I modified the dump.csv function as you suggested
from
    l_output := utl_file.fopen( p_dir, p_filename, 'w' );
to
    l_output := utl_file.fopen( p_dir, p_filename, 'w', '32767' );


as you can see below that before the change, it blew up but after the change
it worked !!

Thanks Very Much For The Assist
Jim

=========================================================================

CREATE TABLE "JIMTEST" 
   ( "EVENTID" NUMBER(19,0) NOT NULL ENABLE, 
 "AUDITID" NUMBER(10,0) NOT NULL ENABLE, 
 "AUDITSTR" VARCHAR2(32 CHAR) NOT NULL ENABLE, 
 "AUDITDATE" DATE NOT NULL ENABLE, 
 "DATAID" NUMBER(10,0), 
 "SUBTYPE" NUMBER(10,0), 
 "USERID" NUMBER(10,0), 
 "PERFORMERID" NUMBER(10,0) NOT NULL ENABLE, 
 "VALUEKEY" VARCHAR2(255 CHAR), 
 "VALUE1" VARCHAR2(4000 CHAR), 
 "VALUE2" VARCHAR2(4000 CHAR), 
 "APPLICATIONID" VARCHAR2(255 CHAR)
   );

insert into jimtest values
(
'32325824',
'4',
'Copy',
TO_DATE ('17-APR-2007 13:02:13','dd-MON-yyyy hh24:mi:ss'),
'6098697',
'144',
NULL,
'5619713',
'Atch 12 - H108 Investigation and Analysis of Impacts Due to New Work and Revised NSA Overarching Information Assurance, Fail Safe Design and Analysis, and System Security Requirements for AEHF System Study SOW 6 Dec 02 (P00019).pdf',
'Enterprise:Organizations:MILSATCOM:Departments:MCSW/PK:Departments:PKA:MCKA (AEHF):AEHF Contract Files:Conformed Contract 02-02-2007:CONFORMED CONTRACT (ASSEMBLING):Atch 12 - H108 Investigation and Analysis of Impacts Due to New Work and Revised NSA Overarching InformationAssurance, Fail Safe Design and Analysis, and System Security Requirements for AEHF System Study SOW 6 Dec 02 (P00019).pdf',
'Enterprise:Organizations:MILSATCOM:Departments:MCSW/PK:Departments:PKA:MCKA (AEHF):IG File Folder-AEHF:Conformed Contract:Atch 12 - H108 Investigation and Analysis of Impacts Due to New Work and Revised NSA Overarching Information Assurance, Fail Safe Design and Analysis, and System Security Requirements for AEHF System Study SOW 6 Dec 02 (P00019).pdf',
NULL
);

commit;

set serveroutput on size 1000000

exec print_table ('select * from jimtest where eventid=32325824 ');

EVENTID                       : 32325824
AUDITID                       : 4
AUDITSTR                      : Copy
AUDITDATE                     : 17-apr-2007 13:02:13
DATAID                        : 6098697
SUBTYPE                       : 144
USERID                        :
PERFORMERID                   : 5619713
VALUEKEY                      : Atch 12 - H108 Investigation and Analysis of
Impacts Due to New Work and Revised NSA Overarching Information Assurance, Fail
Safe Design and Analysis, and System Security Requirements for AEHF System Study
SOW 6 Dec 02 (P00019).pdf
VALUE1                        :
Enterprise:Organizations:MILSATCOM:Departments:MCSW/PK:Departments:PKA:MCKA
(AEHF):AEHF Contract Files:Conformed Contract 02-02-2007:CONFORMED CONTRACT
(ASSEMBLING):Atch 12 - H108 Investigation and Analysis of Impacts Due to New
Work and Revised NSA Overarching InformationAssurance, Fail Safe Design and
Analysis, and System Security Requirements for AEHF System Study SOW 6 Dec 02
(P00019).pdf
VALUE2                        :
Enterprise:Organizations:MILSATCOM:Departments:MCSW/PK:Departments:PKA:MCKA
(AEHF):IG File Folder-AEHF:Conformed Contract:Atch 12 - H108 Investigation and
Analysis of Impacts Due to New Work and Revised NSA Overarching Information
Assurance, Fail Safe Design and Analysis, and System Security Requirements for
AEHF System Study SOW 6 Dec 02 (P00019).pdf
APPLICATIONID                 :
-----------------

PL/SQL procedure successfully completed.


exec dbms_output.put_line( dump_csv(' select * from jimtest where eventid =32325824 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_jimtest-1.txt'));

BEGIN dbms_output.put_line( dump_csv(' select * from jimtest where eventid =32325824 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_jimtest-1.txt')); END;

*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 691
ORA-06512: at "LLTEST.DUMP_CSV", line 49
ORA-06512: at line 1



--after mod to dump.csv function:

SQL> exec dbms_output.put_line( dump_csv(' select * from jimtest where eventid =32325824 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_jimtest-1.txt'));
1

PL/SQL procedure successfully completed.




Creating a XLS File using SQL*Plus

Juan Manuel, December 03, 2010 - 3:32 pm UTC

Hi, here is an example to create a XML file as a XLS file for Excel using SQL*Plus.

http://tips-oracle-mx.blogspot.com/2010/12/sqlplus-con-salida-xls.html

Hope this help.

Hardinero, March 01, 2012 - 12:53 am UTC

Hi tom,

i tried your script and it worked for me..

however i can't find the csv file..it is in the unix folder right..but it's not ther when i looked into it.

DECLARE

l_var varchar2(4000);

BEGIN

l_var := dump_csv( 'SELECT ENAME FROM SCOTT.EMP',
',',
'/usr/tmp' ,
'test.csv' );

END;

Tom Kyte
March 01, 2012 - 7:52 am UTC

it is on the server, on the database server.

how to append data into standard excel format using UTL_FILE

Pradeep napa, April 05, 2012 - 2:43 am UTC

Hi TOM
The issue is not fixed for so many days,i need ur help.

i have a standard excel format,i need to append the values into that excel format by using UTL_FILE.
Assumtion if no standard excel format means that time it generates the excel sheet with values(in "W" mode).But in ("A" mode) data is not printing.

How to read the any excel fromat using UTL and how to append that values into excel format sheet.

Thanks for your help.

Regards
Napa
Tom Kyte
April 06, 2012 - 10:04 am UTC

I don't know what "standard excel format" is or means.

You'll have to understand the file format of excel, you'd have to write to the file in that format.

sorry, I don't do windows things - I don't really use excel and I'm certainly not familiar with its file format.

Chuck, April 06, 2012 - 11:43 am UTC

Here is documentation of the native Excel file format:
http://www.openoffice.org/sc/excelfileformat.pdf

There might be a commercial pl/sql package to output these, but I can't imagine that tackling something like that would be cost effective for in-house programming.
If you ask me, which you didn't.

Stored Proc to write to CSV file around 1 million records

Phaneendra, April 02, 2014 - 6:15 am UTC

Hi Tom,
I'm new to Stored Procedure / plsql. I have a requirement like where I need to fetch 1 million records(from Oracle 10g) from 11 tables using Stored Procedure and write to csv file in a batch, say 45000 records per batch. I've been googling and found that UTIL_FILE package helps in achieving so. Please let me know how can I split all of these records in to 45,000 per batch and write it to a csv file also let me know how much time would it take to completely write all 1 million records using UTIL_FILE package.

Thanks,
Phaneendra.
Tom Kyte
April 02, 2014 - 6:35 am UTC

it is going to be "a long time" to write 1,000,000 records using plsql in general.

The term "record" is ambiguous though - 1,000,000 records could be anywhere from 1mb (fast) to terabytes or beyond (slow). When talking about data, specify things in bytes and records...

you can find the beginning of your program here:
http://asktom.oracle.com/Misc/httpasktomoraclecomtkyteflat.html

a small plsql routine to dump records to a file. You would just change it to close the file and open a new one every 45,000 records - should be pretty trivial.


It took about a minute to unload 1,000,000 records on my laptop:

ops$tkyte%ORA11GR2> set timing on
ops$tkyte%ORA11GR2> exec dump_table_to_csv( 'big_table.big_table', '/tmp', 'tkyte.big_table' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:57.72
ops$tkyte%ORA11GR2> !ls -l /tmp/tkyte.big_table
-rw-rw-r-- 1 ora11gr2 ora11gr2 128252855 Apr  2 02:33 /tmp/tkyte.big_table

ops$tkyte%ORA11GR2> !wc /tmp/tkyte.big_table
  1000001   3366893 128252855 /tmp/tkyte.big_table



not horrible, not great.

A reader, April 28, 2014 - 10:41 pm UTC

select column1||','||column2 from table1
will do the trick. You dont' need the PLSQL code

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library