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 ...
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?