VBA : Databases: Retrieving data from an Oracle database to Excel using Oracle Objects for Office (OO4O) 
Options: Save as PDF | Save attached file | Toggle line numbers
| Type: |
sub |
| Added By: |
Rembo |
| Short Description: |
Oracle databases are widely used. To improve connectivity with coding environments like VB and VBA they created Oracle Objects for Office (OO4O). There are advantages using OO4O rather than ADO (with and OLEDB driver) or ODBC. First and foremost its simply fast, if coded properly. Secondly it is very easy to code. Once you get to know the object model of OO4O you'll find that's its real easy to navigate between records and to create routines to work with an Oracle database. The object model is very complete. |
| Notes: |
You have to have some knowledge of SQL and in particular the Oracle flavor. You'll also need the login details for your Oracle database and, depending on what you have to do with the database, you'll need to appropriate rights.
Note that the routine below uses late binding. Some believe that early binding will increase performance a little (less overhead) but I haven't tested that myself. The biggest performance gain can be made by using the correct connection settings. If you do use early binding you will have to set a reference to Oracle InProc Server x.x Type Library, where x.x is the highest version number available on your system.
You'll have to change the values of DBSID, DBUSER, DBPASS to match your Oracle configuration. You'll also need to change YourTable into the table name that you want to retrieve the records from.
Note that Excel 2003 and earlier only have place for 65536 rows of data. If your Oracle table has more data it will result in an error. |
| Added: |
Nov 11 2008 at 4:30 PM |
| Modified: |
Jan 21 2010 at 10:47 PM |
| Related URLs |
http://download.oracle.com/docs/html/B10952_01/o4o00364.htm http://www.oracle.com/technology/tech/windows/ole/index.html http://www.oracle.com/technology/software/tech/windows/ole/index.html http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10795/adfns_en.htm#1017302 http://www.orafaq.com/faqoo4o.htm http://download.oracle.com/docs/html/B10952_01/o4o00318.htm
|
Adjust the variables for your configuration and run the subroutine.
'Rembo created this routine. You can find me at http://scriptorium.serve-it.nl
'
'Data Base objects for use with late binding.
Dim oSession As Object
Dim oDBase As Object
Dim oDynaset As Object
Global Const ORADB_DEFAULT = &H0& 'The default OpenDatabase method. Note that on newer Oracle versions you
'can also use the "Oracle Mode (No Refetch)" (=&H4&) mode to boost performance
'See: http://download.oracle.com/docs/html/B10952_01/o4o00364.htm
Global Const ORADYN_NOCACHE = &H8& 'For fast retrieval of data
'See: http://download.oracle.com/docs/html/B10952_01/o4o00318.htm
Global Const DBSID = "orcl" 'The Oracle System ID, this uniquely identifies the Oracle database
Global Const DBUSER = "scott" 'The Oracle login name
Global Const DBPASS = "tiger" 'The Oracle password
Sub FetchOracleData()
'This sub retrieves all records from YourTable and puts them in a worksheet
'
'The SQL routine:
'select * from YourTable
Dim sSQL As String
Dim y As Long, x As Long
sSQL = "select * from YourTable"
Set oSession = CreateObject("oracleinprocserver.xorasession")
Set oDBase = oSession.OpenDatabase(DBSID, DBUSER & "/" & DBPASS, ORADB_DEFAULT)
Set oDynaset = oDBase.DBCreateDynaset(sSQL, ORADYN_NOCACHE)
'Turn off screen updating to increase performance
Application.ScreenUpdating = False
'Insert field names on row 1 of worksheet 1
For x = 0 To oDynaset.Fields.Count - 1
Worksheets(1).Cells(1, x + 1).Value = oDynaset.Fields(x).Name
Next x
'Insert records (if they exist) on row 2 and further of worksheet 1
If oDynaset.RecordCount > 0 Then
oDynaset.MoveFirst
For y = 0 To oDynaset.RecordCount - 1
For x = 0 To oDynaset.Fields.Count - 1
Worksheet(1).Cells(y + 2, x + 1).Value = oDynaset.Fields(x).Value
Next x
oDynaset.MoveNext
Next y
End If
'Turn screen updating back on
Application.ScreenUpdating = True
End Sub
