OLE DB Connection to Oracle XE - connection not open

[deleted-user-VeZur_n9o0OZ]

Hello all,

I'm new to SDE, and have fallen at the first hurdle it seems. I am trying to add a simple table in ArcMap but am failing to create the OLE DB connection to my Oracle XE database.

Today I have:
Installed Oracle XE 10g on a virtual server (win2k8)
Installed Oracle XE  10g client on my laptop (windows 7)
Attempted to add a OLD DB connection to the server from ArcCatalog 10

I am given the error ORA-06413 Connection not open when trying to test my connection.
Under the 'server name' I'm entering the IP Address of my virtual server. Do I need to take another step after installing my Oracle Client on my laptop to tell it how to talk to my DB?

Any input appreciated,
Thanks,
James

[VinceAngelo]

Oracle Express is supported with ArcSDE 10 for non-production use, but you've got a couple of
issues to work out first...

ArcSDE has its own connection protocol (in place of OLE DB), so an OLE DB connection doesn't really
have anything to do with ArcSDE (OLE DB connections from ArcGIS are not geometry type aware).

ArcSDE uses Oracle OCI, so you need a robust TNS client (not Instant Client) for Direct Connect
or application server connection to Oracle.  Try an Oracle site for help in listener configuration --
you really don't want to consider ArcSDE until after you've got listener.ora (server) and tnsnames.ora
(client) set up so that the 'tnsping' and 'sqlplus' utilities can successfully access your Oracle server
from the client host.

- V

[deleted-user-VeZur_n9o0OZ]

Vince,

Thanks for getting back to me. Your response sent me down a path of Oracle DBA learning!

I have since installed Oracle 11g R2 on my server, and the client app on my laptop (with ArcGIS Desktop). I can remote connect from my client via SQLPLUS. My connection command is as follows:

[HTML]CONNECT TSB/password@212.64.133.133:1522/ORCL. [/HTML]

This was quite a breakthrough. I was not able to substitute my server's IP address for a hostname despite updating my etc/hosts file accordingly. I don't think this is an issue however. tnsping works from my client machine with the same 212.64.133.133:1522/ORCL string.

So with that said, I should now be able to create and OLE DB connection to access a (non-spatial) table in Oracle. Right? I'm getting the error 'Test connection failed because of an error in initializing provider. Unspecidfied error.'

In catalog I'm adding an OLE DB Connection. I'm choosing Microsoft OLD DB Provider for Oracle. My server name is 212.64.133.133:1522/ORCL (I've tried just the IP and the host name). I'm stumped.

Any suggestions anyone?
James

[VinceAngelo]

Is it your intention to install ArcSDE?  If so, getting a working OLE connection doesn't
really matter, since ArcSDE can access both spatial and non-spatial tables.

What version of ArcGIS are you using?  ArcGIS 10 supports Query Layers, which is yet
another protocol which supports spatial tables.

Searching a Microsoft forum may help with Microsoft provider issues.

- V

[deleted-user-VeZur_n9o0OZ]

Yes it is. I'm still waiting on my EDN license so am attempting an OLD DB connection rather than twiddle my thumbs. But agreed, OLE DB connection isn't important in the long run. Would be nice to have however.

I'm using Arc 10 but am yet to Install Oracle Spatial (or equivalent) which I think you need to use query layers. I'm told they're only fit for read only access but that works for me.

Right I'll look in a MS forum for help with my provider issue.

Thanks!
James

[VinceAngelo]

Spatial is not needed to use SDO_GEOMETRY.  The Intermedia package installs all you need,
and you have to go out of your way to prevent Intermedia install, so it's probably available.

- V

[deleted-user-VeZur_n9o0OZ]

Vince,

FYI I got over my OLE DB connection problem. I had to uninstall my 64bit Oracle Client and Install the 32bit Oracle Client. I discovered this when trying to add a query layer.

You were right. I created a table with an SDO_Geometry field and added it to ArcMap. The polygon I added didn't come through but I think it's to do with the SRID i entered. I'm working on it.

BTW the old forums used to have a flaf to mark threads as resolved and assign points to contributors. Have esri dropped that? What's your incentive for assisting the likes of me?

Thanks,
James

[VinceAngelo]

Juggling both 32-bit and 64-bit clients on a single Windows host is a major PITA.  I never measured
any performance benefit to 64-bit ArcSDE on a Windows host (32-bit was actually a bit faster), so
if you run ArcGIS Desktop on the same host, it's just easier to use one 32-bit client for both ArcGIS
and ArcSDE. [Two notes: 1) There was a benefit to 64-bit Linux on a 64-bit VM server (but multiple
clients is easy on Linux) 2) ArcGIS 10.1 will flip the equation by making 64-bit easier to use than 32-bit]

The points didn't make me eligible for prizes. I just like to help folks (though it's easier to help
those that appreciate the effort -- Thanks!).

- V

[AnastasiaAourik]

A slight tangent on this thread...

I need to have ole db connection to oracle (installed oracle 10g client 32 bit)
only so that my python script can pull a table in oracle
(this table has lat/long values)
and generate/regenerate an arcsde feature class (my arcsde database is sql server)

Thus, need ole db connection.
I am having trouble defining the ole db connection
only on my server 2003 x64 machine.

I did not install the oracle client 32 bit, but it sure looks like it is 32-bit client.
Any clues what may be wrong???

[CarlTrapani]

Hi AnastasiaAourik and anyone else who might hit this problem,

I think this problem may be a bug in the 10.2.0.1 Oracle client:
https://forums.oracle.com/forums/thread.jspa?messageID=1499744

I don't have an Oracle MetaLink account, so I can't verify the bug or get any patches. However, I installed Oracle 10.2.0.3 32bit (x86) client from OTN:
http://www.oracle.com/technetwork/database/10203vista-087538.html
and using that client, I was able to make an OLE DB connection.

Here's my environment and my problem/solution:
-I need to make an OLE DB connection to an Oracle db using a Python script which will update a geodb (in SQL Server). In my DEV environment, I'm connecting to an Oracle 11.1 server, but in the final deployment it will be an Oracle 10.2 server.
-I've installed the 32 bit Oracle 10.2.0.3 Admin client on a Win Server 2003 R2 x64 SP2. I'm running ArcGIS Desktop 10, SP1 and ArcGIS Server 10, SP1 on that server. This is where the Python script will run on a schedule.
-I can connect to the Oracle db server using SQLPlus and TNSPING works too from the Win 2003 server.

I entered the following OLE DB values when making a connection in ArcCatalog:
Using Oracle Provider for OLE DB
Data Source: [theTNSName, for example ORCL]
Username: [theUsername]
Password: [thePassword]
Testing the connection worked.

When I was using the 10.2.0.1 Oracle client, I got the following error:
---------------------------
Microsoft Data Link Error
---------------------------
Test connection failed because of an error in initializing provider. ORA-12154: TNS:could not resolve the connect identifier specified
---------------------------
OK  
---------------------------

Hope this helps someone out there.
Carl