@ZIGESAMM

We also use Oracle Instant Client (OIC) from both Visual FoxPro and C#/.NET. This what the connection string looks like:

DRIVER={Oracle in instantclient_19_6};DBQ=IFS10DEVDB01:1521/IFSDEV;UID=IFSAPP;PWD=<password>

This is ODBC, not OLEDB. But it works the same way, just need to know the connection string (I figured it out by looking at the connection strings in SQL Developer, and the above does not need anything with regard to TNS -- it just uses IP address and port). “IFS10DEVDB01” is the server (which could also be an IP address), 1521 is the port, and “IFSDEV” is the Oracle SID. “IFSAPP” is the user, and the password is obvious.

Also note this session would be running in “god” mode, because it is logging in with the App Owner user ID.

Here’s how we do it, from scratch:

1. If user does not have OIC installed, we copy a folder called “instantclient_19_6” from the network to a known spot on the user’s local drive, such as C:\Apps\Oracle\instantclient_19_6. This folder is straight from Oracle and represents version 19.6 of the Oracle Instant Client. It can just be copied as a folder without running addition setup for that part of it, which is nice.
2. In that local folder for OIC, run the executable odbc_install.exe. This will require admin rights to install correctly. That is what makes the driver available as an ODBC data source driver named “instantclient_19_6”.
3. Establish an ODBC connection in the programming environment of your choice using the connection string above.
4. Use the connection to perform queries, call package API calls, etc.
5. Disconnect.

A note about Visual FoxPro: for whatever reason, Foxpro can only read data. If I try to call a package API method, it will work once then Foxpro will crash, hard. I never figured out why that happens. The connection works great in C# for all uses, though some advanced API calls require passing CLOB values, and OIC does not support that. So, we end up using Oracle MDAC from time to time in C#. But we have standardized on OIC/ODBC because it is the only thing that works for accessing data from both Foxpro and C#.

Hope this helps!

Joe Kaufman

Thanks for the response.

I made some tests with your hints. But that was not successfull. Then for easier to test the odbc connection I used code sequence in VBA:

    Dim ERP As ADODB.Connection
    Dim records As ADODB.Recordset
   
    Set ERP = New ADODB.Connection
    Set records = New ADODB.Recordset
    ERP.ConnectionTimeout = 5
    ERP.Open "DRIVER={Oracle in instantclient_21_6};DBQ=ifs-test01.group.local:1521/IFSDEV;UID=IFSAPP;PWD=<pwd>"

I get the error message (80004005) that there is no data source found and no standard driver used.

Another test with the windows odbc dialog

has a timeout with error message

I found following hints in the oracle documentation

(https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/odbc-driver.html#GUID-F141C2E1-BA44-4399-B72A-3B1FBA2BC750):

Here it is written that on server there should be configured a TNS Service with the  Oracle Net Configuration Assistant (NETCA).

How can I check if there is configured a TNS Service?

What is the correct entry in C:\oracle\instantclient_21_6\network\admin\tnsnames.ora if needed ?

I’ve used the following quite successfully in VB (OraOLEDB)…

strDatabase = "ifst" 'From tnsnames.ora
strUserName = "ifsapp"
strPassword = "***********" ‘change to password
 
Set snpData = CreateObject("ADODB.Recordset")
Set dbDatabase = CreateObject("ADODB.Connection")
 
dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";"
dbDatabase.Open

I think the ADODB stuff may be more OLEDB than ODBC, but I cannot test in my Excel because it is 64 bit and I have the 32-bit driver installed. But something like this code looks like it would at least try to work (this is against SQL Server against a tool we use called MPulse):

Sub joetest()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim fld As ADODB.Field
    Dim Sok As String

    Sok = "SELECT * FROM PRT"
    cnn.Open "Driver={SQL Server};" & _
    "SERVER=<server>;" & _
    "UID=<user>;" & _
    "PWD=<password>;" & _
    "DATABASE=mpulse;"
    rst.Open Sok, cnn, adOpenForwardOnly, adLockReadOnly

    Sheets(1).Cells(2, 1).CopyFromRecordset rst
    rst.Close
End Sub

I believe the same would work for Oracle if I had a 64-bit Oracle ODBC driver installed.

I have not had to do anything with TNS. There is not even a file starting with TNS in my Oracle Instant Client local folder. Perhaps the Oracle servers were configured in some way such that straight IP address access works for us?

Joe Kaufman

Thanks for your response and help. Our IT employees will adjust the firewall. That will be the last obstacle which blocks the request for the connection.