cx_Oracle import problem

[guimilare]

Hello Splunkers.

I wrote two python scripts to use in my environment as scripted-lookups: one of them makes a query in an Oracle database, while the other do some math checks.
The second works perfectly, but not the first.

I noticed that the problem with the first script resides on cx_Oracle module.
When I run my script with pure python, I can import the module:

When I run with the splunk python, I get the following error:

Has anyone ever experienced this?
Can you tell me step by step how to get around this problem?

Thanks in advance!
Regards, GMA

[guimilare · Accepted solution]

So, here is the roadmap to correctly install cx_Oracle:

The first task is installing the Oracle client.
It is important that the client version is 11.2.
In higher versions, the cx_Oracle connector does not work correctly.

1) From the http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html page, download the following executables:

instantclient-basic-linux.x64-11.2.0.4.0.zip
instantclient-sqlplus-linux.x64-11.2.0.4.0.zip
instantclient-sdk-linux.x64-11.2.0.4.0.zip

2) In the /opt/splunk folder, unzip the files

3) Configure oracle env:

vim /etc/profile.d/oracle.sh

#! / bin / bash
LD_LIBRARY_PATH = "/usr/lib/oracle/11.2/client64/lib:${LD_LIBRARY_PATH}"
export LD_LIBRARY_PATH
TNS_ADMIN = "/etc/oracle"
export TNS_ADMIN
ORACLE_HOME = "/usr/lib oracle/11.2/client64/lib"
export ORACLE_HOME

4) Environment variables:
source /etc/profile.d/oracle.sh

5) Copy or create the tnsnames.ora:
touch /etc/oracle/tnsnames.ora

6) Create symbolic link to ORACLE_HOME to avoid the "can not locate Oracle include files" error:
mkdir /usr/lib/oracle/11.2/client64/lib/sdk
ln -s /usr/include/oracle/11.2/client64 /usr/lib/oracle/11.2/client64/lib/sdk/include

The next step is to install the cx_Oracle connector.
Download cx_Oracle version 5.1.2 from https://sourceforge.net/projects/cx-oracle/files/5.1.2/cx_Oracle-5.1.2-11g-py26-1.x86_64.rpm/downloa... and install the RPM.

It is possible that when importing the cx_Oracle library, the following error occurs:

Traceback (most recent call last):
import cx_Oracle
ImportError: libclntsh.so.11.1: Can not open shared object file: No such file or directory

To bypass this, create the following symbolic links (the paths to the files may change depending on the environment and where the files were extracted. Run find / -name lib * .so * to find the correct path):

/usr/lib64/libclntsh.so.11.1 → /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1
/usr/lib64/libnnz11.so → /usr/lib/oracle/11.2/client64/lib/libnnz11.so
/usr/lib64/libocci.so.11.1 → /usr/lib/oracle/11.2/client64/lib/libocci.so.11.1
/usr/lib64/libociei.so → /usr/lib/oracle/11.2/client64/lib/libociei.so
/usr/lib64/libsqlplusic.so → /usr/lib/oracle/11.2/client64/lib/libsqlplusic.so
/usr/lib64/libsqlplus.so → /usr/lib/oracle/11.2/client64/lib/libsqlplus.so

Now the environment is ready. To test, simply execute the commands below:

[splunk @ splunktest-01 ~] $ python
Python 2.6.6 (r266: 84292, Sep 4 2013, 07:46:00)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-3)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>>

If no error are displayed, the environment is correctly set up.

[beatus]

guimilare,
Splunk ships with it's own Python environment. This means if you're running a Python script you'd need to have the module you'd like to import in the current working directory. For more on how Python imports work, this blog post seems helpful: https://leemendelowitz.github.io/blog/how-does-python-find-packages.html

I'd also say, do NOT modify Splunk's python modules. It's not supported to install additional modules into Splunk's python environment. You should be putting the modules next to the python script you're trying to run.

All that said, I will echo what p_gurav said - If you're trying to import data from a database directly, you're probably best off utilizing Splunk DB Connect. https://splunkbase.splunk.com/app/2686/
Specifically for a lookup: http://docs.splunk.com/Documentation/DBX/3.1.3/DeployDBX/Createandmanagedatabaselookups

[p_gurav]

You can refer db_connect app and look at db_lookup() script. Also this answer may help:
https://answers.splunk.com/answers/2580/i-want-sample-code-to-connect-to-oracle-database-and-lookup-...