Export Oracle data to Excel

352580 pts.
Tags:
Oracle
Oracle export
SQL
SSRS
Is there sql code to export data from oracle to excel? I can right click on the data set to export but need to include it as part of the code. Any help would be much appreciated! Thanks

Software/Hardware used:
Oracle, Excel

Answer Wiki

Thanks. We'll let you know when a new response is added.

You could use this code. This use ODBC to connect to an Oracle database and copies the entire recordset into the worksheet.

Dim db as database
Dim rs as recordset
Dim SQLSt as string
Set db = OpenDatabase("ODBCDSNName", False, False, "ODBC;DSN=ODBCDSNName;ConnectString=OracleHostName;UID=OracleUserName;PWD=OraclePassword")
sqlSt = "SELECT col1, col2, ... FROM Table1 WHERE Col1 = '" & SomeVariable & "' ORDER BY Col1, Col2, ...;"
Set rs = db.OpenRecordset(sqlSt)
Range("WorkSheetName").Cells(1, 1).CopyFromRecordset rs

————– kccrosser
If you need to “push” it from Oracle (as a result of a nightly job, for example), the only way I have found is to use the Oracle UTL_FILE package to write the data as CSV records.

Just make sure to “wrap” the individual columns with quotes to avoid embedded commas and other special characters from messing up the import. Also, if your data includes numeric columns with leading zeros, you need to set the Excel column type to “TEXT”, or Excel will happily discard those leading zeros.

UTL_FILE is a pretty simple package to use. Not as simple as connecting from Excel as in the above example, but it is easy to write CSV files from Oracle with UTL_FILE.

————– jjohannsen

I ran across a good PL/SQL based tool that allows you to take a cursor and feed it into a stored procedure, and get a CLOB back as a valid OOXML Excel Spreadsheet. It seems really flexible. You can specify styles, sheet header and footer, and even formulas.

We are using a trial version of it on a pilot project right now, and really like where it is taking our business solutions. The business people seem to like it too, because we have shown them it becomes part of the software solution, and they get back native Excel files that they can use right away for further presentation and/or manipulation.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following