• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3594
  • Last Modified:

How to connect to Oracle 10g Express from Delphi XE

I have Delphi XE Professional running on Windows 7 Professional (64 bit).
I would like my Delphi app to be able to connect to an Oracle database running on another machine on the same LAN. It is Oracle 10g Express Edition running on Windows XP Home Edition.

I have tried downloading Oracle stuff to get ODBC or OLEDB drivers installed but have found it unbelievably complicated and got in a complete mess. So, I have uninstalled all the Oracle stuff (and they don't make that very easy) to make a fresh start.

In my Delphi app, I am using a TADOConnection and would like to be able to specify something like "FILE=MyOracle.udl" for the ConnectionString. So, the Delphi bit of this is technically irrelevant because it will work fine if I can just get the data link in a UDL file to work.

So, my question is: what should I download from Oracle (please give a link) to get connected?

(If it helps, I used to be able to do this just fine on an XP machine using the "Oracle Provider for OLE DB".)
0
plumothy
Asked:
plumothy
  • 6
  • 5
  • 3
  • +1
2 Solutions
 
sdstuberCommented:
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
0
 
sdstuberCommented:
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;');
0
 
sdstuberCommented:
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)
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
plumothyAuthor Commented:
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?
0
 
sdstuberCommented:
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"
0
 
plumothyAuthor Commented:
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?
0
 
OP_ZaharinCommented:
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.
0
 
plumothyAuthor Commented:
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."
0
 
OP_ZaharinCommented:
- you can download the Oracle OLE Provider for OLE DB driver in the following link. kindly find the one suitable for your oracle version:
http://www.oracle.com/technetwork/database/windows/utilsoft-088126.html
0
 
sdstuberCommented:
I have not tried this, but I think the ODAC xcopy deployment is what you're looking for.


http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html


if this isn't it,  you may need to abandon the instant client and go back to the "heavy" install.
0
 
OP_ZaharinCommented:
- 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.
0
 
plumothyAuthor Commented:
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.
0
 
Geert GruwezOracle dbaCommented:
are you sure you have a listener running on the remote machine ?
0
 
plumothyAuthor Commented:
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:
the list of OLE DB Providers I get if I double-click on a UDL file in Windows Explorer is very different from the list I get if I edit the TADOConnection's ConnectionString property in the Delphi IDE; I presume that is a consequence of 64bit Windows v 32bit Delphi
the installer for "Oracle Provider for OLE DB" fails immediately (see previous Comment) - is that another 32/64bit thing?

Has anyone reading this ever got Oracle's OLE DB Provider to install on a Windows 7 Professional 64 bit PC?
0
 
plumothyAuthor Commented:
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.
0
 
Geert GruwezOracle dbaCommented:
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now