Difficulty running script on server w/ Oracle OLE DB connection; Error 000732

[Brownschuh]

I recently wrote a python script to take CAMA data from an Oracle database in the Assessors' office and update a table on our enterprise geodatabase; basically just taking property ownership information from one table and updating it daily in another so we can consume it in our GIS system.  At first, I tested on my desktop machine where it ran just fine.  However, when I went to test it on our GIS server, that's where the trouble began.  It seems to not be able to connect to the Oracle database via the script, as I presented with the 'Error:  000732: < dataset name > does not exist or is not supported' error when running any tool that requires a connection as an input (e.g MakeTableView, CopyRows, TableToGeodatabase, etc).  Definitely has me stumped because I am able to connect to the database manually via an OLE DB connection in both ArcCatalog and in ArcMap; however, when run in IDLE it fails.

At first I though it might be a 32-bit vs 64-bit issue.  The desktop machine uses the 32-bit flavor of python in IDLE.  Thought perhaps the server was forcing the script to run using the 64-bit, but after running this helpful little bit of code:

import sys
print sys.version

it appears as though the server is running 32-bit as well.  

As far as I know the Oracle database we are connecting to is 11g.  However, both the server and desktop machines are using the 12c Release 2 (12.2.0.1.0) 32-bit flavor of the Oracle client because the 11g client does not does support Windows 10.   Currently, my desktop machine is running 10.3.1 and the server machine is running 10.4.1, w/ python versions 2.7.8 and 2.7.10, respectively.

The only thing I haven't tested is installing the 64-bit version of the client software on the server side.  Even though IDLE tells me it's running on the 32-bit side of things, I have a suspicion that somehow the connection to the database needs a 64-bit component ...    

[George_Thompson]

Yes you are correct that you need to have the 64 bit client installed for the Server component.

You also mentioned that the Server was returning 32 bit Python as default. It should be the 64 bit package for Server. Here is an article that may help: FAQ: Why do Python scripts fail on a machine with both ArcGIS for Server and Desktop installed? 

[Brownschuh]

Alright I shall obtain the 64-bit client for the server.  Do you think it would be wise to uninstall the 32-bit version of the client or could they be run simultaneously?  Reason I ask is sometimes I need to manually view the table in Catalog or ArcMap, and that would require the 32-bit.

Also, I am reluctant to fiddling around with the bit version of python on the server machine ... I currently have about 8 scripts running automatically in Task Scheduler.  Some of them run hourly.  My point is:  they all run just fine.  If they break, then I am in even more of a jam.

[George_Thompson]

Yes, just install the 64-bit client for the Server. I would leave the 32 bit client also. You can have both on your machine (I do for ArcMap/Pro).

I understand the concern with changing Python. Install the client and see if the results are different when running the script.

[Brownschuh]

Alright so now I have both 32-bit and 64-bit client installed.  Tried running it in IDLE using the 32-bit version of Python; same error.  Next, I tired running it using the 64-bit version of Python; same results ... ERROR 000732.

I will note that I tried this in IDLE (by manually navigating to where python is installed on the 64-bit side), through CMD line (again calling out the 64-bit version), and in Task Scheduler ... 

[MichaelVolz]

Scott:

Are you saying that there is no version of Oracle 11g that will work on a Windows 10 OS?

[Brownschuh]

As far as I know, 11g is not supported with Windows 10.  Tried installing and was presented with an error. 

For reference, Oracle 11g R2 installation on windows 10 & Error when trying to Install Oracle Client on W... | Oracle Community.   Honeslty, I am not an Oracle guy (nor is anyone at my organization ...)

But like I said, I've installed the 32-bit client software for 12c and was able to connect in ArcCatalog no problem.  The only issue I have is running that script on the server itself ....

[MichaelVolz]

Can you go into more detail on how you setup the OLE DB connection on the desktop as well as the server?

[Brownschuh]

Sure, no problem!

First I installed the client; just downloaded the zip file from the Oracle website, extracted it and placed it on the C:/ drive.  Opened up the extracted folder and ran the .exe file to install the client software.  Ran through all of the defaults except where it asked for Installation Type .... chose Administrator (other options included:  InstantClient, Runtime, and Custom).  Everything installed just fine.

Next, I opened up ArcCatalog and chose 'Add OLE DB Connection'.  Selected the 'Micosoft OLE DB Provider for Oracle' option under the Provider tab.  Under the Connection tab I provided the server name, using this format:  <servername>/<instance> and plugged in the username/password provided to us.  Hit 'Test Connection' and it succeeded.  Note we kept the default settings for all of the options under the Advanced and All tabs.  After that we hit OK and were to able to connect.

I used this same process on both the server side as well as the desktop side.

[JamesCrandall]

We've been doing this for several years by simply keeping it all in procedural code and a linked server instance (you're just managing non-spatial data).  Have the assessor setup a view, you create a linked server instance on your db and then you can just access the attributes as needed with a Stored Procedure or other.