This content has been marked as final. Show 13 replies
Hi,1 person found this helpful
You can export table data to csv file, see [Export table to .csv file|http://forums.oracle.com/forums/thread.jspa?messageID=3605757�] thread or search another possible solution in this forum.
Two simple ways are there
1) Use SQL Developer where you have this option to export data to excel.
set markup html on;
set markup html off;
Hello Nagappan,1 person found this helpful
if you need more than simple csv: at Re: How to save a query result and export it to, say excell? you can find links to different solutions. At least the packages behind second and fourth link support more than one worksheet.
P.S.: It should not be difficult to find these solutions using search.
Edited by: Marwim on 21.09.2009 07:10
I tried creating the file for export by using the following, but i am getting error while executing the procedure, please see the syntax below
create directory reports as '/pkg/test/reports'
grant read, write on directory reports to user1;
Inside the procedure i used the syntax as follows
v_wstring varchar2 (100);
v_file := '/pkg/test/reports/test1.xls';
wfile_handle := utl_file.fopen ('REPORTS',v_file, 'W');
v_wstring := 'Request'||chr(9);
But i am getting the following error, what could be the possible reason ?
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
ORA-06512: at "PRECOM_ADM.TEST", line 18
ORA-06512: at line 1
When i searched in the net, i got the information saying that
UTL_FILE parameter should be set in the init.ora file, but i am going to deploy the script
in production system, i am not sure whether the DBA would allow me to do this, what would
be other way to overcome this problem.
v_file := '/pkg/test/reports/test1.xls';Try this:
The directory object has already specified the path so you shouldn't include it in the filename as well.
v_file := 'test1.xls';
does Oracle have permission to write to the directory? By granting read/write to user1 you say that Oracle allows user1 to write to "REPORTS". This does not mean that Oracle is allowed to write to the directory.
How do i check that, whether oracle have permission to the target directory and if it is not there how do i grant that ?
I tried your options as well, but it is not working still
Just one more question, the oracle server is running in different machine ,and the directory which i created is in another machine (client side), that should'nt be a problem right ?
No, that's the problem. Oracle cannot see client directories (and it shouldn't). utl_file only works at server directories.
So which means i wont be able to export my data(fetched out of my pl/sql block), running from client machine to an excel file or csv file is it ?
It ispossible, just follow Walters link in the first reply to the opening posting.
I recently started using Excellant, a newer product available that seems tailor made for just what you are asking about.1 person found this helpful
Excellant accepts an XML-based spreadsheet spec, and a SQL query, or cursor and builds the spreadsheet out of the data resulting from the query. The product follows Microsoft's OOXML file format for Excel, and allows me to specify any number of worksheets, styles, formatting, header/footer, and formulas. Our business users are getting very excited about the possibilities, and I think we are probably going to see it get used more and more.
Here is the web site for the product [Peak42 Solutions|http://www.peak42solutions.com] .