How to connect to Oracle 10g Express from Delphi XE

easiest/smallest install you should need would be  the instant client


 http://www.oracle.com/technetwork/topics/winx64soft-089540.html

I'm not a delphi guy though, can you post a udl file so I can see what's in them?

I can try to help with the specifics from there

well, I did a little research on my own,  I think you probably want something like this...


CreateUDLFile('MyOracle.udl','ORAOLEDB.1;Data Source=REMOTESERVER:1521/XE;User ID=YOURUSER;Password=YOURPASSWORD;');

to user data sources  formatted like that  is called EZCONNECT,  make sure you have EZCONNECT enabled in your sqlnet.ora

after you install the client  go to

(your oracle home where you installed the client)\network\admin\sqlnet.ora

and make sure it has this line in it...

NAMES.DIRECTORY_PATH=(EZCONNECT)

you can add other path options  like TNSNAMES or LDAP if you want too, something like this...

NAMES.DIRECTORY_PATH=(TNSNAMES,EZCONNECT)

sdstuber,

Thank you for your responses.

I went to the link you posted. I was reminded once again of why I avoid Oracle as much as possible. There are 7 or 8 different products listed for 7 different versions of Instant Client. This is why I get in such a mess with Oracle. Having no idea which one I really needed, I chose the link at the top of the page: "Version 11.2.0.2.0 Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications".  It is a zip file with no setup or install executable.  What am I meant to do with that?

Ah but wait, "Installation Steps" at the bottom of the page which basically says "unzip the files to a folder, put that folder in your PATH and enjoy".  What a joke. Nothing has been installed has it?

So, I'm afraid I have to ask at this point: have I downloaded the right one and, if so, how do I use it?

that's the point of instant client,  , you just unzip.  No registry stuff or anything like that.   sort of going with the "xcopy deploy" strategy

The links below that one are to add other stuff "if needed"

OK, it sounds like I got the right one. How does that help me, though? (I have set the PATH as instructed.)

I still don't seem to be able to create a working UDL file.  In the UDL editor, I see a provider called "Oracle Provider for OLE DB". I'm not sure if it is a hangover from my earlier attempts or if it appeared because of unzipping the instant client (I doubt it).

Anyway, if I select this provider and click on Next, it tells me:
---------------------------
Microsoft Data Link Error
---------------------------
Provider is no longer available. Ensure that the provider is installed properly.
---------------------------
OK  
---------------------------

I despair at this point. Why do they make it so complicated and so difficult? (With other databases you just seem to install a small driver or provider and away you go.)

Does anyone have any further suggestions because I am really struggling with this?

hi plumothy,
- from the link given earlier by sdstuber, did u also download *Instant Client Package - ODBC: Additional libraries for enabling ODBC applications? you might have remove it after the first install earlier, therefore you need to install it again. after download, extract and run the odbc_install.exe.

Thank you.

Yes, I have now done that and I have been able to create an ODBC data source so that is a step in the right direction. I was able to create a UDL file using the "Microsoft OLE DB Provider for ODBC Drivers" and the "Test Connection" button reported success. So, I thought we had solved the problem.

Unfortunately, when I attempt to use that UDL in my Delphi app, I get an error message I have never seen before: "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application". I would welcome any suggestions on how to fix that.

Alternatively, how can I get the Oracle OLE Provider for OLE DB working? It is listed in the UDL editor but any attempt to use results in the message "Provider is no longer available. Ensure that the provider is installed properly."

- i believe the  Oracle OLE Provider for OLE DB driver is 32bit and delphi is also a 32bit application, therefore if you need to setup the odbc, you need to run the odbc manager located in the %systemdrive%\Windows\SysWoW64 folder.

OP Zaharin,

Thanks for the link to the OLE DB Provider. I followed the installation instructions. The first time I ran setup.exe it changed my Windows colour scheme and did absolutely nothing else. I ran it again and immediately got this error message from the universal installer: "Abnormal program termination. An internal error has occured. Please provide the following files to Oracle Support: Unknown, Unknown, Unknown".

This getting really frustrating.

are you sure you have a listener running on the remote machine ?

Yes, I presume the remote machine is set up correctly because I can easily connect to it using a UDL file on a Windows XP laptop.

Also, I can connect to it on my Windows 7 PC using an ODBC DSN - but I get an error (see earlier Comment) when trying to use it with "Microsoft OLE DB Provider for ODBC Drivers".

There seems to be more than one issue here:

Has anyone reading this ever got Oracle's OLE DB Provider to install on a Windows 7 Professional 64 bit PC?

Things are still very wrong, but I have managed to get an Oracle ODBC connection (ensuring I was using the 32bit ODBC Admin). I then used a text editor to create a UDL file ("Provider=MSDASQL.1;Data Source=MyOracleDB;Persist Security Info=False;User ID=UserName;").  (The MS UDL editor just couldn't get it to work!)

But, Delphi still produced errors. However, when searching elsewhere for information, I read that someone else had similar errors because the path to their development system had "(x86)" in it. My Delphi launches from "C:\Program Files (x86)\Embarcadero\RAD Studio\8.0\bin\bds.exe" so I changed the shortcut to "C:\Progra~2\Embarcadero\RAD Studio\8.0\bin\bds.exe". I still can't connect from within my IDE, but at least my app (when launched from within Delphi) does manage to connect. So, I can at last get on with this project.

My research into this shows that a large number of users have suffered horrendous connection problems on 64bit Windows and that this has been going on for several years! Oracle really should get this sorted out.

I have split the points between sdstuber and OP Zaharin - your input pointed me in the right direction and helped me to a partial solution - thanks guys.

are you sure you have installed a 32 bit oracle client ?
Delphi XE is 32 bit so it has to use a 32 bit client

always make sure the programs is the same bit alignment
you can install both a 32bit and a 64bit client on the same 64bit machine for different programs