Can't connect to Oracle DB #20371

edgui-appolonicorreia · 2022-06-14T01:44:50Z

I'm having an issue to connect to an Oracle DB. I've already followed the steps to create a requirements-local.txt file into docker/ cx_Oracle package, but still not working.

How to reproduce the bug

touch ./docker/requirements-local.txt
echo "cx_Oracle" >> ./docker/requirements-local.txt
docker-compose build --force-rm
docker-compose up

Expected results

I was expecting to be able to connect into a Oracle database.

Actual results

Actually I got this error message:
ERROR: (builtins.NoneType) None (Background on this error at: http://sqlalche.me/e/13/dbapi)

Screenshots

Environment

(please complete the following information):

browser type and version: Brave
superset version: latest
any feature flags active: cx_Oracle driver
Database: AWS Oracle

Checklist

Make sure to follow these steps before submitting your issue - thank you!

I have checked the superset logs for python stacktraces and included it here as text if there are any.
I have reproduced the issue with at least the latest released version of superset.
I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

I'm using an AWS Oracle instance to test.
I've checked the logs of the superset_worker superset_worker_beat containers, and seems that the cx-Oracle lib was successfully installed.

The superset_app container log:

I'm using the following string connection:
oracle://<username>:<password>@<hostname>:<port>
from https://superset.apache.org/docs/databases/oracle/

The text was updated successfully, but these errors were encountered:

cofin · 2022-06-14T21:58:46Z

I'm working through a similar problem now. Did you install the Oracle instant client into the Docker container or system you are running superset from? cx_oracle requires either the instant client or full Oracle client. The recently released python-oracledb no longer has this requirement, so installation should get easier in the near future.

I'm using the following in my Docker container to install the latest SQL client:

RUN mkdir -p /usr/share/oracle/network/admin \
    && cd /usr/share/oracle \
    && apt-get update -y \
    && apt-get install -y --no-install-recommends git ca-certificates wget build-essential unzip curl libaio1 libaio-dev \
    && rm -rf /var/lib/apt/lists/* \
    && apt-get purge -y --auto-remove -o APT::AutoRemove::RecommendsImportant=false \
    && wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-basiclite-linuxx64.zip \
    && unzip instantclient-basiclite-linuxx64.zip \
    && rm -f instantclient-basiclite-linuxx64.zip \
    && cd  /usr/share/oracle/instantclient* \
    && rm -f *jdbc* *occi* *mysql* *README *jar uidrvci genezi adrci \
    && echo  /usr/share/oracle/instantclient* > /etc/ld.so.conf.d/oracle-instantclient.conf \
    && ldconfig \
    && echo 'local_xe=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xepdb1)))' > /usr/share/oracle/network/admin/tnsnames.ora```

cofin · 2022-06-14T22:26:30Z

One additional note, in case it helps.

I had to connect using a TNS formatted string instead of the easy connect format: oracle+cx_oracle://username:password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_or_scan_address)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db_service_name)))

EDIT: Updated connect string to reference the Oracle driver correctly.

edgui-appolonicorreia · 2022-06-17T19:37:24Z

One additional note, in case it helps.

I had to connect using a TNS formatted string instead of the easy connect format: cx_oracle+oracle://username:password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_or_scan_address)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db_service_name)))

Unfortunately didn't work! I got the same issue.

pikachurus · 2022-06-18T19:40:54Z

in my case helped at first install oracleclient 11.2, then remove config
/etc/ld.so.conf.d/oracle-instantclient.conf (in container) and install oracleclient 21.6

docker exec [container-id] dpkg -i oracle-instantclient11.2-basic_11.2.0.4.0-2_amd64.deb
docker exec [container-id] rm /etc/ld.so.conf.d/oracle-instantclient.conf
docker exec [container-id] dpkg -i oracle-instantclient-basic_21.6.0.0.0-2_amd64.deb

and i don't know why

arsenyyyyy · 2022-06-23T11:44:54Z

in case anyone is interested, it might help: just install instantclient following this instructions:
https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html#oracle-instant-client-rpms

edgui-appolonicorreia · 2022-07-16T18:49:00Z

I'm working through a similar problem now. Did you install the Oracle instant client into the Docker container or system you are running superset from? cx_oracle requires either the instant client or full Oracle client. The recently released python-oracledb no longer has this requirement, so installation should get easier in the near future.

I'm using the following in my Docker container to install the latest SQL client:

RUN mkdir -p /usr/share/oracle/network/admin \
    && cd /usr/share/oracle \
    && apt-get update -y \
    && apt-get install -y --no-install-recommends git ca-certificates wget build-essential unzip curl libaio1 libaio-dev \
    && rm -rf /var/lib/apt/lists/* \
    && apt-get purge -y --auto-remove -o APT::AutoRemove::RecommendsImportant=false \
    && wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-basiclite-linuxx64.zip \
    && unzip instantclient-basiclite-linuxx64.zip \
    && rm -f instantclient-basiclite-linuxx64.zip \
    && cd  /usr/share/oracle/instantclient* \
    && rm -f *jdbc* *occi* *mysql* *README *jar uidrvci genezi adrci \
    && echo  /usr/share/oracle/instantclient* > /etc/ld.so.conf.d/oracle-instantclient.conf \
    && ldconfig \
    && echo 'local_xe=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xepdb1)))' > /usr/share/oracle/network/admin/tnsnames.ora```

I tried that approach, adding those lines at the Dockerfile and running docker compose -f docker-compose-non-dev.yml up --build, but I got a new error message (hahahahahahaahahaha) when I tried to connect to the DB:

superset_app          | 127.0.0.1 - - [16/Jul/2022:17:48:23 +0000] "GET /health HTTP/1.1" 200 2 "-" "curl/7.74.0"
superset_app          | CommandException
superset_app          | Traceback (most recent call last):
superset_app          |   File "/app/superset/databases/commands/test_connection.py", line 77, in run
superset_app          |     engine = database.get_sqla_engine()
superset_app          |   File "/app/superset/utils/memoized.py", line 50, in __call__
superset_app          |     value = self.func(*args, **kwargs)
superset_app          |   File "/app/superset/models/core.py", line 401, in get_sqla_engine
superset_app          |     raise self.db_engine_spec.get_dbapi_mapped_exception(ex)
superset_app          |   File "/app/superset/models/core.py", line 399, in get_sqla_engine
superset_app          |     return create_engine(sqlalchemy_url, **params)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/__init__.py", line 525, in create_engine
superset_app          |     return strategy.create(*args, **kwargs)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/strategies.py", line 61, in create
superset_app          |     entrypoint = u._get_entrypoint()
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/url.py", line 172, in _get_entrypoint
superset_app          |     cls = registry.load(name)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 277, in load
superset_app          |     raise exc.NoSuchModuleError(
superset_app          | sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:cx_Oracle.oracle
superset_app          | 
superset_app          | The above exception was the direct cause of the following exception:
superset_app          | 
superset_app          | Traceback (most recent call last):
superset_app          |   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1516, in full_dispatch_request
superset_app          |     rv = self.dispatch_request()
superset_app          |   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1502, in dispatch_request
superset_app          |     return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 89, in wraps
superset_app          |     return f(self, *args, **kwargs)
superset_app          |   File "/app/superset/views/base_api.py", line 113, in wraps
superset_app          |     raise ex
superset_app          |   File "/app/superset/views/base_api.py", line 110, in wraps
superset_app          |     duration, response = time_function(f, self, *args, **kwargs)
superset_app          |   File "/app/superset/utils/core.py", line 1533, in time_function
superset_app          |     response = func(*args, **kwargs)
superset_app          |   File "/app/superset/utils/log.py", line 244, in wrapper
superset_app          |     value = f(*args, **kwargs)
superset_app          |   File "/app/superset/views/base_api.py", line 83, in wraps
superset_app          |     return f(self, *args, **kwargs)
superset_app          |   File "/app/superset/databases/api.py", line 709, in test_connection
superset_app          |     TestConnectionDatabaseCommand(item).run()
superset_app          |   File "/app/superset/databases/commands/test_connection.py", line 124, in run
superset_app          |     raise DatabaseTestConnectionDriverError(
superset_app          | superset.databases.commands.exceptions.DatabaseTestConnectionDriverError: Could not load database driver: BaseEngineSpec
superset_app          | 2022-07-16 17:48:25,194:WARNING:superset.views.base:CommandException
superset_app          | Traceback (most recent call last):
superset_app          |   File "/app/superset/databases/commands/test_connection.py", line 77, in run
superset_app          |     engine = database.get_sqla_engine()
superset_app          |   File "/app/superset/utils/memoized.py", line 50, in __call__
superset_app          |     value = self.func(*args, **kwargs)
superset_app          |   File "/app/superset/models/core.py", line 401, in get_sqla_engine
superset_app          |     raise self.db_engine_spec.get_dbapi_mapped_exception(ex)
superset_app          |   File "/app/superset/models/core.py", line 399, in get_sqla_engine
superset_app          |     return create_engine(sqlalchemy_url, **params)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/__init__.py", line 525, in create_engine
superset_app          |     return strategy.create(*args, **kwargs)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/strategies.py", line 61, in create
superset_app          |     entrypoint = u._get_entrypoint()
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/url.py", line 172, in _get_entrypoint
superset_app          |     cls = registry.load(name)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 277, in load
superset_app          |     raise exc.NoSuchModuleError(
superset_app          | sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:cx_Oracle.oracle
superset_app          | 
superset_app          | The above exception was the direct cause of the following exception:
superset_app          | 
superset_app          | Traceback (most recent call last):
superset_app          |   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1516, in full_dispatch_request
superset_app          |     rv = self.dispatch_request()
superset_app          |   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1502, in dispatch_request
superset_app          |     return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 89, in wraps
superset_app          |     return f(self, *args, **kwargs)
superset_app          |   File "/app/superset/views/base_api.py", line 113, in wraps
superset_app          |     raise ex
superset_app          |   File "/app/superset/views/base_api.py", line 110, in wraps
superset_app          |     duration, response = time_function(f, self, *args, **kwargs)
superset_app          |   File "/app/superset/utils/core.py", line 1533, in time_function
superset_app          |     response = func(*args, **kwargs)
superset_app          |   File "/app/superset/utils/log.py", line 244, in wrapper
superset_app          |     value = f(*args, **kwargs)
superset_app          |   File "/app/superset/views/base_api.py", line 83, in wraps
superset_app          |     return f(self, *args, **kwargs)
superset_app          |   File "/app/superset/databases/api.py", line 709, in test_connection
superset_app          |     TestConnectionDatabaseCommand(item).run()
superset_app          |   File "/app/superset/databases/commands/test_connection.py", line 124, in run
superset_app          |     raise DatabaseTestConnectionDriverError(
superset_app          | superset.databases.commands.exceptions.DatabaseTestConnectionDriverError: Could not load database driver: BaseEngineSpec

And I used this syntax string connection: cx_oracle+oracle://username:password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_or_scan_address)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db_service_name)))

Any guesses?

cofin · 2022-07-17T03:10:39Z

It looks like I gave you the incorrect connect string format. According to the docs, it's oracle+cx_oracle instead of cx_oracle+oracle. Sorry about that.

Here is the documentation in case you need to adjust additional options.

Jika97 · 2022-10-26T21:34:00Z

Hello,

I have the same issue on an Apache Superset 2.0.0 install on Ubuntu 20.04 via docker-compose.

My Oracle database is on another server.
I installed everything cx_Oracle and Oracle Instant Client on the server, but I have the following error:

ERROR: (builtins.NoneType) None (Background on this error at: https://sqlalche.me/e/14/dbapi)

My SQL ALCHEMY URI : oracle+cx_oracle://user:password%3A%29%[email protected]:1521/?service_name=SERVICE_NAME

When I run a small python connection script, however I manage to connect to the database and get my data.

import cx_Oracle
from sqlalchemy import create_engine

connection = cx_Oracle.connect(user="user", password="password:)!",
                               dsn="x.x.x.x/SERVICENAME")

cursor = connection.cursor()
cursor.execute("""
        SELECT id2, ub
        FROM data
        """,
)
for id2, ub in cursor:
    print("Values:", id2, ub)

Could someone help me, please ?

nmabhinandan · 2023-04-03T08:23:53Z

any updates?

edgui-appolonicorreia · 2023-04-04T22:03:05Z

any updates?

Unfortunately not :/

There is a long time since I tested it . I need to try it again.

mfol · 2023-07-02T01:41:28Z

how wonderful apache is....
in my case it was because there was the @ character in the password :)

Example:
cx_oracle+oracle://username:pass@word@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_or_scan_address)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db_service_name)))

Due to "@" the connection does not work...

----------- EDIT --------------
Using escape character %40 instead of @ is working

Narender-007 · 2023-08-23T05:14:17Z

hello everyone ,

i got this error
ERROR: (builtins.NoneType) None
[SQL: (cx_Oracle.DatabaseError) DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help
(Background on this error at: https://sqlalche.me/e/14/4xp6)]
(Background on this error at: https://sqlalche.me/e/14/dbapi)

please can you help it anyone.

amarnath-tiwari-nagarro · 2023-08-28T06:21:31Z

hello everyone ,

i got this error ERROR: (builtins.NoneType) None [SQL: (cx_Oracle.DatabaseError) DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help (Background on this error at: https://sqlalche.me/e/14/4xp6)] (Background on this error at: https://sqlalche.me/e/14/dbapi)

please can you help it anyone.

same error for me as well. Can anybody help on this.

antiwari001 · 2023-08-30T11:42:01Z

Found a solution to above problem, used docker image apache/superset to build another custom container and that resolved this issue. PSB Dockerfile which is working fine.

FROM apache/superset
USER root
RUN pip install cx_Oracle
RUN apt update
RUN apt -y install alien libaio1 wget
RUN wget https://download.oracle.com/otn_software/linux/instantclient/185000/oracle-instantclient18.5-basiclite-18.5.0.0.0-3.x86_64.rpm
RUN alien -i oracle-instantclient18.5-basiclite-18.5.0.0.0-3.x86_64.rpm
RUN sh -c 'echo /usr/lib/oracle/18.5/client64/lib/ > /etc/ld.so.conf.d/oracle.conf'
RUN ldconfig
USER superset

Narender-007 · 2023-08-31T10:48:11Z

Found a solution to above problem, used docker image apache/superset to build another custom container and that resolved this issue. PSB Dockerfile which is working fine.

FROM apache/superset USER root RUN pip install cx_Oracle RUN apt update RUN apt -y install alien libaio1 wget RUN wget https://download.oracle.com/otn_software/linux/instantclient/185000/oracle-instantclient18.5-basiclite-18.5.0.0.0-3.x86_64.rpm RUN alien -i oracle-instantclient18.5-basiclite-18.5.0.0.0-3.x86_64.rpm RUN sh -c 'echo /usr/lib/oracle/18.5/client64/lib/ > /etc/ld.so.conf.d/oracle.conf' RUN ldconfig USER superset

i am added that thing but getting same error

mtk12 · 2023-09-22T13:31:27Z

pip install cx_Oracle
mkdir -p /opt/oracle
cd /opt/oracle
wget https://download.oracle.com/otn_software/linux/instantclient/1920000/instantclient-basic-linux.x64-19.20.0.0.0dbru.zip
unzip instantclient-basic-linux.x64-19.20.0.0.0dbru.zip
apt-get install -y --no-install-recommends libaio1
sh -c "echo /opt/oracle/instantclient_19_20 > /etc/ld.so.conf.d/oracle-instantclient.conf"
ldconfig

Running These commands worked for me

hundag20 · 2023-10-23T14:26:06Z

I'm working through a similar problem now. Did you install the Oracle instant client into the Docker container or system you are running superset from? cx_oracle requires either the instant client or full Oracle client. The recently released python-oracledb no longer has this requirement, so installation should get easier in the near future.

I'm using the following in my Docker container to install the latest SQL client:

RUN mkdir -p /usr/share/oracle/network/admin \
    && cd /usr/share/oracle \
    && apt-get update -y \
    && apt-get install -y --no-install-recommends git ca-certificates wget build-essential unzip curl libaio1 libaio-dev \
    && rm -rf /var/lib/apt/lists/* \
    && apt-get purge -y --auto-remove -o APT::AutoRemove::RecommendsImportant=false \
    && wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-basiclite-linuxx64.zip \
    && unzip instantclient-basiclite-linuxx64.zip \
    && rm -f instantclient-basiclite-linuxx64.zip \
    && cd  /usr/share/oracle/instantclient* \
    && rm -f *jdbc* *occi* *mysql* *README *jar uidrvci genezi adrci \
    && echo  /usr/share/oracle/instantclient* > /etc/ld.so.conf.d/oracle-instantclient.conf \
    && ldconfig \
    && echo 'local_xe=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xepdb1)))' > /usr/share/oracle/network/admin/tnsnames.ora```

Thanks this worked for me

foretony5211 · 2023-11-09T04:56:14Z

I'm working through a similar problem now. Did you install the Oracle instant client into the Docker container or system you are running superset from? cx_oracle requires either the instant client or full Oracle client. The recently released python-oracledb no longer has this requirement, so installation should get easier in the near future.
I'm using the following in my Docker container to install the latest SQL client:
RUN mkdir -p /usr/share/oracle/network/admin \
    && cd /usr/share/oracle \
    && apt-get update -y \
    && apt-get install -y --no-install-recommends git ca-certificates wget build-essential unzip curl libaio1 libaio-dev \
    && rm -rf /var/lib/apt/lists/* \
    && apt-get purge -y --auto-remove -o APT::AutoRemove::RecommendsImportant=false \
    && wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-basiclite-linuxx64.zip \
    && unzip instantclient-basiclite-linuxx64.zip \
    && rm -f instantclient-basiclite-linuxx64.zip \
    && cd  /usr/share/oracle/instantclient* \
    && rm -f *jdbc* *occi* *mysql* *README *jar uidrvci genezi adrci \
    && echo  /usr/share/oracle/instantclient* > /etc/ld.so.conf.d/oracle-instantclient.conf \
    && ldconfig \
    && echo 'local_xe=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xepdb1)))' > /usr/share/oracle/network/admin/tnsnames.ora```
Thanks this worked for me

thanks for you , this does not worked for me , because i use oracle 11.2 , so I change the instantclient url from version 21 to 19 ， it worked for me

mkdir -p /usr/share/oracle/network/admin \
    && cd /usr/share/oracle \
    && apt-get update -y \
    && apt-get install -y --no-install-recommends git ca-certificates wget build-essential unzip curl libaio1 libaio-dev \
    && rm -rf /var/lib/apt/lists/* \
    && apt-get purge -y --auto-remove -o APT::AutoRemove::RecommendsImportant=false \
    && wget https://download.oracle.com/otn_software/linux/instantclient/1921000/instantclient-basiclite-linux.x64-19.21.0.0.0dbru.zip \
    && unzip instantclient-basiclite-linux.x64-19.21.0.0.0dbru.zip \
    && rm -f instantclient-basiclite-linux.x64-19.21.0.0.0dbru.zip \
    && cd  /usr/share/oracle/instantclient* \
    && rm -f *jdbc* *occi* *mysql* *README *jar uidrvci genezi adrci \
    && echo  /usr/share/oracle/instantclient* > /etc/ld.so.conf.d/oracle-instantclient.conf \
    && ldconfig \
    && echo 'local_xe=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xepdb1)))' > /usr/share/oracle/network/admin/tnsnames.ora

crcjwice123 · 2024-01-18T04:25:22Z

Found a solution to above problem, used docker image apache/superset to build another custom container and that resolved this issue. PSB Dockerfile which is working fine.

FROM apache/superset USER root RUN pip install cx_Oracle RUN apt update RUN apt -y install alien libaio1 wget RUN wget https://download.oracle.com/otn_software/linux/instantclient/185000/oracle-instantclient18.5-basiclite-18.5.0.0.0-3.x86_64.rpm RUN alien -i oracle-instantclient18.5-basiclite-18.5.0.0.0-3.x86_64.rpm RUN sh -c 'echo /usr/lib/oracle/18.5/client64/lib/ > /etc/ld.so.conf.d/oracle.conf' RUN ldconfig USER superset

this approach works for me, I entered the container and ran these commands.

mayasirode · 2024-04-02T18:55:14Z

i had the same issue connecting a docker based superset to a oracle database.

with this very good step by step tutorial i installed a superset instance by scratch and with the following steps (after the installation) my connection to oracle db worked:

cd superset
. superset_env/bin/activate`
sudo pip install cx_Oracle
sudo apt update
sudo  apt -y install alien libaio1 wget
sudo wget https://download.oracle.com/otn_software/linux/instantclient/2113000/oracle-instantclient-basiclite-21.13.0.0.0-1.x86_64.rpm
sudo alien -i oracle-instantclient-basiclite-21.13.0.0.0-1.x86_64.rpm` 
sudo sh -c 'echo /usr/lib/oracle/21.13/client64/lib/ > /etc/ld.so.conf.d/oracle.conf'
sudo ldconfig

the connection string in superset was:

oracle+cx_oracle://USRER:PASSWORD@HOST:PORT/SERVICE_NAME

thank you very much to
@shantanukhond for the tutorial and @antiwari001 for the solution to the oracle problem!!

rusackas · 2024-04-02T19:14:08Z

it seems cx_oracle has been moved and renamed to python-oracledb - has anyone tried this to make sure it works? If it does, someone should probably update the docs page accordingly.
https://github.com/oracle/python-oracledb/

Nicoautoxp · 2024-04-17T15:23:01Z

i had the same issue connecting a docker based superset to a oracle database.

with this very good step by step tutorial i installed a superset instance by scratch and with the following steps (after the installation) my connection to oracle db worked:
cd superset
. superset_env/bin/activate`
sudo pip install cx_Oracle
sudo apt update
sudo  apt -y install alien libaio1 wget
sudo wget https://download.oracle.com/otn_software/linux/instantclient/2113000/oracle-instantclient-basiclite-21.13.0.0.0-1.x86_64.rpm
sudo alien -i oracle-instantclient-basiclite-21.13.0.0.0-1.x86_64.rpm` 
sudo sh -c 'echo /usr/lib/oracle/21.13/client64/lib/ > /etc/ld.so.conf.d/oracle.conf'
sudo ldconfig
the connection string in superset was:

oracle+cx_oracle://USRER:PASSWORD@HOST:PORT/SERVICE_NAME

thank you very much to @shantanukhond for the tutorial and @antiwari001 for the solution to the oracle problem!!

Following this i also sucessfully connected to my oracledb. I did have to use the long connection string from @cofin and manually install the gevent python library

vranjan25 · 2024-05-09T17:56:54Z

Add the below in Dockerfile

Install Oracle Instant Client

RUN mkdir -p /opt/oracle
&& wget --no-check-certificate --no-cookies --header "Cookie: oraclelicense=accept-securebackup-cookie" https://download.oracle.com/otn_software/linux/instantclient/214000/instantclient-basic-linux.x64-21.4.0.0.0dbru.zip
&& unzip instantclient-basic-linux.x64-21.4.0.0.0dbru.zip -d /opt/oracle
&& rm -f instantclient-basic-linux.x64-21.4.0.0.0dbru.zip
&& apt-get update
&& apt-get install -y libaio1
&& echo "/opt/oracle/instantclient_21_4" > /etc/ld.so.conf.d/oracle-instantclient.conf
&& ldconfig

Note: make sure you include wget & unzip package before oracle client. You may reuse the step

RUN mkdir -p ${PYTHONPATH} superset/static requirements superset-frontend apache_superset.egg-info requirements
&& useradd --user-group -d ${SUPERSET_HOME} -m --no-log-init --shell /bin/bash superset
&& apt-get update -qq && apt-get install -yqq --no-install-recommends
build-essential
curl
default-libmysqlclient-dev
libsasl2-dev
libsasl2-modules-gssapi-mit
libpq-dev
libecpg-dev
libldap2-dev
wget
unzip
&& touch superset/static/version_info.json
&& chown -R superset:superset ./*
&& rm -rf /var/lib/apt/lists/*

vranjan25 · 2024-05-09T17:59:29Z

I'm having an issue to connect to an Oracle DB. I've already followed the steps to create a requirements-local.txt file into docker/ cx_Oracle package, but still not working.

How to reproduce the bug

touch ./docker/requirements-local.txt

echo "cx_Oracle" >> ./docker/requirements-local.txt

docker-compose build --force-rm

docker-compose up

Expected results

I was expecting to be able to connect into a Oracle database.

Actual results

Actually I got this error message: ERROR: (builtins.NoneType) None (Background on this error at: http://sqlalche.me/e/13/dbapi)

Screenshots

Environment

(please complete the following information):

browser type and version: Brave

superset version: latest

any feature flags active: cx_Oracle driver

Database: AWS Oracle

Checklist

Make sure to follow these steps before submitting your issue - thank you!

I have checked the superset logs for python stacktraces and included it here as text if there are any.

I have reproduced the issue with at least the latest released version of superset.

I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

I'm using an AWS Oracle instance to test. I've checked the logs of the superset_worker superset_worker_beat containers, and seems that the cx-Oracle lib was successfully installed.

The superset_app container log:

I'm using the following string connection: oracle://<username>:<password>@<hostname>:<port> from https://superset.apache.org/docs/databases/oracle/

See below the steps I posted

eudson · 2024-05-17T06:06:43Z

@vranjan25 am not sure what I might be doing wrong but I have been trying this for a while now. I gave up months ago and resorted to do it directly o the container but it is not sustanable because every time I have to rebuild the container I need to manually repeat the process. Here is how my docker-compose and Dockerfile look like. Let me know if am doing something wrong.

Dockerfile

FROM apache/superset:${TAG:-latest}

USER root

RUN apt-get update \
    && apt-get install -y wget zip \
    && apt-get install -y libaio1 \
    && mkdir /opt/oracle \
    && cd /opt/oracle/ \
    && wget https://download.oracle.com/otn_software/linux/instantclient/2112000/instantclient-basic-linux.x64-21.12.0.0.0dbru.zip \
    && unzip instantclient-basic-linux.x64-21.12.0.0.0dbru.zip \
    && sh -c "echo /opt/oracle/instantclient_21_12 > /etc/ld.so.conf.d/oracle-instantclient.conf" \
    && ldconfig

USER superset

docker-compose.yml

  superset:
    env_file:
      - path: docker/.env # default
        required: true
      - path: docker/.env-local # optional override
        required: false
    # image: *superset-image
    build: 
      context: .
      dockerfile: Dockerfile
    container_name: superset_app
    command: ["/app/docker/docker-bootstrap.sh", "app-gunicorn"]
    user: "root"
    restart: unless-stopped
    ports:
      - 8080:8088
    depends_on: *superset-depends-on
    volumes: *superset-volumes

vivekkranjan · 2024-05-17T09:58:45Z

Use the below in the Dockerfile. You don't need any change in docker-compose.yml

SQLALCHEMY:
oracle+cx_oracle://user_name:password@your_db

# Install Oracle Instant Client
RUN mkdir -p /opt/oracle \
    && wget --no-check-certificate --no-cookies --header "Cookie: oraclelicense=accept-securebackup-cookie" https://download.oracle.com/otn_software/linux/instantclient/2340000/instantclient-basic-linux.x64-23.4.0.24.05.zip \
    && unzip instantclient-basic-linux.x64-23.4.0.24.05.zip -d /opt/oracle \
    && rm -f instantclient-basic-linux.x64-23.4.0.24.05.zip \
    && apt-get update \
    && apt-get install -y libaio1 \
    && echo "/opt/oracle/instantclient_23_4" > /etc/ld.so.conf.d/oracle-instantclient.conf \
    && ldconfig \
    && mkdir -p /opt/oracle/instantclient_23_4/network/admin \
    && touch /opt/oracle/instantclient_23_4/network/admin/listener.ora \
    && touch /opt/oracle/instantclient_23_4/network/admin/tnsnames.ora \
    && touch /opt/oracle/instantclient_23_4/network/admin/sqlnet.ora \
    && echo "LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=your_host)(PORT=your_port))))" > /opt/oracle/instantclient_23_4/network/admin/listener.ora \
    && echo "your_db=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=your_host)(PORT=your_port))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=your_service_name)))" > /opt/oracle/instantclient_23_4/network/admin/tnsnames.ora \
    && echo "NAMES.DIRECTORY_PATH=(TNSNAMES)" > /opt/oracle/instantclient_23_4/network/admin/sqlnet.ora

cjbj · 2024-05-29T05:13:51Z

Alternatively, try the new version of cx_Oracle (now called python-oracledb) since it doesn't need Instant Client. Install steps are at https://cjones-oracle.medium.com/steps-to-use-apache-superset-and-oracle-database-ae0858b4f134

sachin-bureau · 2024-06-05T18:28:22Z

hey @cjbj were you able to get this connected with the python-oracledb ,
I was trying to connect ADW using its wallets to connects by unzipping the wallet in $TNS_ADMIN/tnsnames.ora but unable to achieve the connection

cjbj · 2024-06-05T22:39:26Z

[@sachin-bureau I updated this comment with my findings]

To use Thin mode with mTLS and Oracle Autonomous Database (e.g ADB-S, ATP, ADW, whatever SEO acronym you know it as!), extract the ewallet.pem file from the wallet ZIP file and grab one of the connect descriptors from the wallet tnsnames.ora file. Nothing else is needed. If you are following the steps in my blog, put this connect descriptor in the $HOME/superset/tnsnames.ora, and put the .pem file where ever you like; I chose to move it to the directory $HOME/pemdir/. Your SQLAlchemy URI in the Superset "Connect a database" pane would be of the form oracle://cj:mypassword@cjdb. Before creating the connection, navigate to the Advanced tab -> Other -> Engine Parameters box and enter the extra SQLAlchemy config_args like:

{
    "connect_args": {
	"config_dir": "/home/opc/superset",
	"wallet_location": "/home/opc/pemdir",
	"wallet_password": "mywalletpassword"
    }
}

To save reading the docs: "config_dir" is where the tnsnames.ora file is. (You don't need to set TNS_ADMIN if you use this). "wallet_location" is where the .pem file is. "wallet_password" is the password you created when you downloaded the wallet from Oracle Cloud.

Alternatively you could use python-oracledb "Thick" mode by adding a call to oracledb.init_oracle_client() to superset_config.py. (Don't forget to edit sqlnet.ora and change its WALLET_LOCATION DIRECTORY path). Your SQLAlchemy URI would be of the form oracle://mydbusername:mydbpassword@mydb and you wouldn't need to set any Engine Parameters.

Also consider changing the database to use 1-way TLS so you don't need a wallet file in the first place!

General install info is here, and connection steps here.

sachin-bureau · 2024-06-06T08:10:15Z

@cjbj i was able to connect with thick client here is a quick DIY blog:
https://medium.com/@tripathi.sachinxx/connect-oracle-adw-with-superset-f1796fe534b7

cjbj · 2024-06-06T08:28:15Z

@sachin-bureau nice work. Earlier I also appended the steps on my blog https://cjones-oracle.medium.com/steps-to-use-apache-superset-and-oracle-database-ae0858b4f134 so the info is in one place.

yalcinarslan · 2024-09-04T06:30:20Z

@cjbj Thanks for your blog post. I followed your blog to connect Oracle 11gR2.
When I try to test the connection getting the below error. How can we handle it?

ERROR: (builtins.NoneType) None
[SQL: (oracledb.exceptions.OperationalError) DPY-6005: cannot connect to database (CONNECTION_ID=J3dEWf7vjzFS+8nWuTbKyQ==).
DPY-3010: connections to this database server version are not supported by python-oracledb in thin mode
Help: https://python-oracledb.readthedocs.io/en/latest/user_guide/troubleshooting.html#dpy-3010
(Background on this error at: https://sqlalche.me/e/14/e3q8)]
(Background on this error at: https://sqlalche.me/e/14/dbapi)

cjbj · 2024-09-13T07:27:07Z

@yalcinarslan Use "Thick" mode by calling oracledb.init_oracle_client() in $HOME/superset/superset_config.py For details about what parameters to pass, see https://python-oracledb.readthedocs.io/en/latest/user_guide/initialization.html#enabling-python-oracledb-thick-mode

Or upgrade your database.

AzaZPPL · 2024-10-14T19:50:47Z

For those using the Docker Compose setup and wanting to switch to the new oracledb instead of cx_Oracle with Instant Client, here are the steps:

Create requirements_local.txt in the docker folder.
Add oracledb to this file to download the latest python-oracledb.

Confirm the downloaded version or check here. For example, if it's 8.3.0, write:

import sys
import oracledb
oracledb.version = "8.3.0"
sys.modules["cx_Oracle"] = oracledb

Append this code to docker/pythonpath_dev/superset_config.py.
Run docker-compose up -d to start the setup.

If your connection uses a service name, convert the connection string. For example, change:
oracle://user:[email protected]:1521/myservicename to oracle://user:[email protected]:1521/?service_name=myservicename.

With thanks to the write up of @cjbj.

edgui-appolonicorreia added the #bug Bug report label Jun 14, 2022

rusackas added the data:connect:oracle Related to Oracle label Jan 23, 2023

rusackas mentioned this issue Apr 3, 2024

can not connect oracle using oracledb package #25100

Open

Can't connect to Oracle DB #20371

Can't connect to Oracle DB #20371

Comments

edgui-appolonicorreia commented Jun 14, 2022 • edited Loading

How to reproduce the bug

Expected results

Actual results

Screenshots

Environment

Checklist

Additional context

cofin commented Jun 14, 2022

cofin commented Jun 14, 2022 • edited Loading

edgui-appolonicorreia commented Jun 17, 2022

pikachurus commented Jun 18, 2022

arsenyyyyy commented Jun 23, 2022

edgui-appolonicorreia commented Jul 16, 2022

cofin commented Jul 17, 2022

Jika97 commented Oct 26, 2022 • edited Loading

nmabhinandan commented Apr 3, 2023

edgui-appolonicorreia commented Apr 4, 2023

mfol commented Jul 2, 2023 • edited Loading

Narender-007 commented Aug 23, 2023 • edited Loading

amarnath-tiwari-nagarro commented Aug 28, 2023

antiwari001 commented Aug 30, 2023 • edited Loading

Narender-007 commented Aug 31, 2023 • edited Loading

mtk12 commented Sep 22, 2023

hundag20 commented Oct 23, 2023

foretony5211 commented Nov 9, 2023 • edited Loading

crcjwice123 commented Jan 18, 2024

mayasirode commented Apr 2, 2024

rusackas commented Apr 2, 2024

Nicoautoxp commented Apr 17, 2024

vranjan25 commented May 9, 2024 • edited Loading

Install Oracle Instant Client

vranjan25 commented May 9, 2024

How to reproduce the bug

Expected results

Actual results

Screenshots

Environment

Checklist

Additional context

eudson commented May 17, 2024 • edited Loading

Dockerfile

docker-compose.yml

vivekkranjan commented May 17, 2024 • edited Loading

cjbj commented May 29, 2024

sachin-bureau commented Jun 5, 2024

cjbj commented Jun 5, 2024 • edited Loading

sachin-bureau commented Jun 6, 2024 • edited Loading

cjbj commented Jun 6, 2024

yalcinarslan commented Sep 4, 2024

cjbj commented Sep 13, 2024

AzaZPPL commented Oct 14, 2024

edgui-appolonicorreia commented Jun 14, 2022 •

edited

Loading

cofin commented Jun 14, 2022 •

edited

Loading

Jika97 commented Oct 26, 2022 •

edited

Loading

mfol commented Jul 2, 2023 •

edited

Loading

Narender-007 commented Aug 23, 2023 •

edited

Loading

antiwari001 commented Aug 30, 2023 •

edited

Loading

Narender-007 commented Aug 31, 2023 •

edited

Loading

foretony5211 commented Nov 9, 2023 •

edited

Loading

vranjan25 commented May 9, 2024 •

edited

Loading

eudson commented May 17, 2024 •

edited

Loading

vivekkranjan commented May 17, 2024 •

edited

Loading

cjbj commented Jun 5, 2024 •

edited

Loading

sachin-bureau commented Jun 6, 2024 •

edited

Loading