The above answer would be correct if what you want is to IMPORT the file into the database.
If you want to EXPORT data to an excel file, I don’t think there is a native way to do it directly from the database. You could, however, write the data to a CSV file to open it with Excel.
Answered by Carlosdl, You could, however, write the data to a CSV file to open it with Excel. It is bit difficult and there r some procedures….
A flat file pipe-delimited & it is easily to get it into oracle. The application will read the file and convert the data to SQL INSERT statements so that it can be run as a script. The “SQL INSERTS” have been designed to work with Oracle database. The code could easily be modified to work with any SQL compatible database. As far as my knowledge, there may be some problem with date/time fields also, if u r using CSV format.
U may go for as I written earlier, as it is easy than to write data in CSV file….
U can use a 3rd party convert tool for this works. For Convert Tool please visit HERE But I never use it and it is free. So, if u wish to use this, please check in all respect before using it.
I am not sure if you want to export to excel from oracle, or export the excel data to oracle.
My answer is assuming you want to export from oracle using excel. I use excel often to import data. What you need to do is create an ODBC connection to your database. Then use it to import the data.
I am working with a colleague who looked over my shoulder as I was answering this, and he thinks you can do it with the spool function from the oracle prompt as well. He suggests something like this.
set feed off
markup html on
select * from yourexcel;
set markup html off
I have added this, as he is quite knowledgeable (more than me) with oracle. Maybe you understand it, (I have to admin I don’t even though he explained it) hope it helps.
you can also convert it to a TEXT file and then using Forms10g and UTIL_FILE, populate a table with it. I have done it and it works great.
One way i did it was to use forms10g to call a reports10g. the report had the account for the rows i needed in the Excel file. My forms10g used the SET_REPORT_OBJECT_PROPERTY (v_rep_id,REPORT_DESFORMAT,’DELIMITEDDATA’) and the SET_REPORT_OBJECT_PROPERT(v_rep_id,REPORT_DESNAME,’C:datadirectoryfilename.csv’);
This created a file with the .CSV extension that was easily imported into Excel