oracle connectivity

[sathishesb]

Can you please suggest me some way how to connect to a oracle database using excel and want to execute some sql query, for a sample

select name,marks from student where marks>30 

i need to fetch the data in a table format.

Please guide me

[Aflatoon]

Here is an example. You may need to use a different provider depending on which version of the Oracle client tools you have installed:
[vba]

Sub GetOracleData()
' Sample demonstrating how to return a recordset from an Oracle db
' requires a reference to the Microsoft ActiveX Data Objects Library.

Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, TR As Range
Dim varData, lngRecCount As Long, lngFieldCount As Long, avarData() As Variant
Dim i As Long, j As Long

Application.ScreenUpdating = False

Set TR = Range("A1")
Set cn = New ADODB.Connection
With cn
.Provider = "MSDAORA.Oracle"
' .Provider = "OraOLEDB.Oracle"
.ConnectionString = "Data Source=database;User ID=itsme;Password=secret"
.Open
End With
strQuery = "SELECT * FROM table_name WHERE some_field < 100"
Set rst = New ADODB.Recordset
rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText

With rst
lngFieldCount = .Fields.Count
' output the field names
For i = 1 To lngFieldCount
TR.Offset(0, i - 1) = .Fields(i - 1).Name
Next i
' use CopyFromRecordset method to output data on worksheet
TR.Offset(1, 0).CopyFromRecordset rst
.Close
End With

Set rst = Nothing
cn.Close
Set cn = Nothing
Application.ScreenUpdating = True
End Sub
[/vba]

[sathishesb]

Thanks a lot for the code.
I am getting an error at
[vba]Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, TR As Range[/vba]

with a error message as,

user-defined type not defined 

is there any change i need to do to the code, other than connection string.

[Aflatoon]

If you were to read the comment line above the first declaration line, you would see what is required.

[sathishesb]

sorry for this question,
It throws an error such as,

TNS names couldn't be resolved 

but i am 200% sure i am giving correct data source name, user and password.

do i need to change anything in this part

[vba]With cn
.Provider = "MSDAORA.Oracle"
'.Provider = "OraOLEDB.Oracle"
.ConnectionString = "Data Source=abcd;User ID=xxxx;Password=yyyyy"
.Open
End With[/vba]

i am trying to connect to Oracle data base..

please help me

[Aflatoon]

Does the data source work if you connect using MSquery? Are you sure your client tools are set up properly?

[sathishesb]

ya both my client and Query works fine in other tool, but throws the same error in vba ..

[Aflatoon]

It may be that you need a different provider. I would check a site like www.connectionstrings.com for your Oracle version.