How to write a script or database connectivity to connect oracle databse

[upadhyay40]

Hello all,

How to write a script or database connectivity to connect oracle databse, can all please help me out to connect to the oracle database and retrieve data from database.

Thanks

[Saket]

hi upadhyay40,

use this piece of code to connect to your oracle database, and execute the query

Code:

Set ConObj = CreateObject("ADODB.Connection")
ConObj.Provider = "MSDAORA.1"
ConObj.Properties("Data Source").Value = YourServerName
ConObj.Properties("Initial Catalog").Value = YourDatabase
ConObj.Properties("User ID").Value = YourUserName
ConObj.Properties("Password").Value = YourPassword
ConObj.Open
If ConObj.Errors.Count = 0 Then
    Set RecObj = CreateObject("ADODB.Recordset")
    RecObj.Open YourQuery, ConObj,adLockPessimistic
End if

your results for the query will be in recordset RecObj
let me know if there is any issue.

[upadhyay40]

Hi saket,

I got error "Item cannot be found in the collection corresponding to the requested name or ordinal" on line 4

My code is

Code:

Set ConObj = CreateObject("ADODB.Connection")
ConObj.Provider = "MSDAORA.1"
ConObj.Properties("Data Source").Value = "15.5.7.13"
ConObj.Properties("Initial Catalog").Value = "sid"
ConObj.Properties("User ID").Value = "User"
ConObj.Properties("Password").Value = "Pass"
ConObj.Open
If ConObj.Errors.Count = 0 Then
    Set RecObj = CreateObject("ADODB.Recordset")
    RecObj.Open "select * from USER", ConObj,adLockPessimistic
End if

Please let me know if i make any mistake, hoping for your cooperation

Thanks
Mahesh

[Saket]

I think you have provided your host ip for data source value
you should give the 'Service Name' to connect to a oracle DB

[upadhyay40]

Hi Saket,

Please correct me if i am wrong, i was given my server Ip on data source row is this ok or wrong, and can you please let me explain from where i can get Service name, please take in consider.

Thanks
Mahesh

[Saket]

Server IP for data source is wrong.
In oracle, usually the database resides on a server and it uses a service name (tnsname) to connect to the DB server. the service name or the tnsname should be there on both i.e server and the client machine as well. Use NetConfiguration Assistant to create a tnsname.

let me know if you need more help on this.

[v_selvam]

Giveing server IP and is not a Wrong one.

If you connect oracle database, the service name should be configured in the 'tnsnames.ora' file. If you need more information on this, contact any developer or DBA who is using in oracle DB

[Saket]

@ v_selvam - I was never able to connect to a oracle db with providing server IP. have you tried this ever?
would you like to share how to do this?

[upadhyay40]

Hi Saket,

Sorry i was not replying, actually i had discussion with my DB admin , he said for getiing tnsname i need to install oracle client on my end, so i am installing that now, so if i will get problem i will just let you know, take in consider

Thanks
Mahesh

[v_selvam]

@Saket - When you connect oracle database, you need the oracle service name which you configured in the 'tnsnames.ora' file.

Here is the example for oracle service name configuration

Code:

ORA_Servicename =
(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   )
(CONNECT_DATA =
   (SERVICE_NAME = DataBaseName)
)
)

In the above code, 'ORA_Servicename' is service name. The connecting string is given below
ConnectionString = "User Id=scott;Password=tiger;Data Source=ORA_Servicename;"