Move optional install per review

Signed-off-by: Christopher Jones christopher.jones@oracle.com

Update Linux Python / Oracle Database images to use python-oracledb in preference to its predecessor cx_Oracle.

Update for Instant Client 19.18 (#2555)

NoSQL CE 22.3.32

fixing MD010/no-hard-tabs Hard tabs

feat: add jq to the OCI CLI container image

Resolves #2572.

Signed-off-by: Avi Miller avi.miller@oracle.com

chore: resolve linting issues found by super linter

Signed-off-by: Avi Miller avi.miller@oracle.com

#2579 - Add redis dockerfile

Address PR comments; change ordering of lang variables, set EXPOSE 6379 on docker image, set logging to stdout.

Instead of using pipes just use sed -e, use -i to create a backup of the original config for reference, add redis 6 version of dockerfile

correcting OracleDatabase/SingleInstance/readme.md (#2588)

Resolved docker version 23.0 issue (#2592)

Fixed broken link: Issue : 2537 (#2569)

• fixed broken link

• fixing linting issues

• fixing linting issues

• fixing broken link and linting

• corrected typo

Co-authored-by: Gerald Venzl gerald.venzl@oracle.com

Improvement

• support ARM architectures
• use open source image GraalVM - replace openjdk:17-oraclelinux8

@agilevic Any decision on named timezones is still pending. And so is that bug - sorry.

Were you fetching as string, or as lob locators? The former will be a lot faster than the latter.

Review the documentation: https://cx-oracle.readthedocs.io/en/latest/user_guide/initialization.html

Also, consider upgrading to the latest version of cx_Oracle, now called python-oracledb: see the release announcement.

The other piece is to install Instant Client x86 on your M1/M2 machine. You can simply run the same commands that you run on Apple Intel:

  cd $HOME/Downloads

  curl -O https://download.oracle.com/otn_software/mac/instantclient/198000/instantclient-basic-macos.x64-19.8.0.0.0dbru.dmg
  curl -O https://download.oracle.com/otn_software/mac/instantclient/198000/instantclient-sqlplus-macos.x64-19.8.0.0.0dbru.dmg

  hdiutil mount instantclient-basic-macos.x64-19.8.0.0.0dbru.dmg
  hdiutil mount instantclient-sqlplus-macos.x64-19.8.0.0.0dbru.dmg

  /Volumes/instantclient-basic-macos.x64-19.8.0.0.0dbru/install_ic.sh

  hdiutil unmount /Volumes/instantclient-basic-macos.x64-19.8.0.0.0dbru
  hdiutil unmount /Volumes/instantclient-sqlplus-macos.x64-19.8.0.0.0dbru

With this, your i386 Node.js code is initialized the same on M1 and Intel:

@nicolasbaer ping

I regularly build oracledb weekly and have encountered an issue this week. Setup is straight forward : sudo yum install python3-devel pip3 install oracledb

I have tested this using OEL7 and OEL8 across most hardware configurations on OCI and am having the same issue in each environment.

During the PIP build here is the error I'm encountering:

---- Output below (sorry formatting is messed up for copy/paste -----

warning: src/oracledb/impl/thin/network_services.pyx:65:0: The 'DEF' statement is deprecated and will be removed in a future Cython version. Consider using global variables, constants, and in-place literals instead. See https://github.com/cython/cython/issues/4310
    building 'oracledb.thin_impl' extension
    gcc -pthread -Wno-unused-result -Wsign-compare -DDYNAMIC_ANNOTATIONS_ENABLED=1 -DNDEBUG -O2 -g -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fexceptions -fstack-protector-strong -grecord-gcc-switches -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -D_GNU_SOURCE -fPIC -fwrapv -O2 -g -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fexceptions -fstack-protector-strong -grecord-gcc-switches -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -D_GNU_SOURCE -fPIC -fwrapv -O2 -g -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fexceptions -fstack-protector-strong -grecord-gcc-switches -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -D_GNU_SOURCE -fPIC -fwrapv -fPIC -I/usr/include/python3.6m -c src/oracledb/thin_impl.c -o build/temp.linux-x86_64-3.6/src/oracledb/thin_impl.o
    gcc -pthread -shared -Wl,-z,relro -Wl,-z,now -g -Wl,-z,relro -Wl,-z,now -g build/temp.linux-x86_64-3.6/src/oracledb/thin_impl.o -L/usr/lib64 -lpython3.6m -o build/lib.linux-x86_64-3.6/oracledb/thin_impl.cpython-36m-x86_64-linux-gnu.so
    Compiling src/oracledb/thick_impl.pyx because it changed.
    [1/1] Cythonizing src/oracledb/thick_impl.pyx

    Error compiling Cython file:
    ------------------------------------------------------------
    ...
                session_callback_bytes = params.session_callback.encode()
                create_params.plsqlFixupCallback = session_callback_bytes
                create_params.plsqlFixupCallbackLength = \
                        <uint32_t> len(session_callback_bytes)
            if params.access_token_callback is not None:
                create_params.accessTokenCallback = _token_callback_handler
                                                    ^
    ------------------------------------------------------------

    src/oracledb/impl/thick/pool.pyx:116:48: Cannot assign type 'int (void *, dpiAccessToken *) except? -1 nogil' to 'dpiAccessTokenCallback'

    Error compiling Cython file:
    ------------------------------------------------------------
    ...
            params.portNumber = self.port
            params.timeout = self.timeout
            params.name = name_buf.ptr
            params.nameLength = name_buf.length
            if self.callback is not None:
                params.callback = _callback_handler
                                  ^
    ------------------------------------------------------------

    src/oracledb/impl/thick/subscr.pyx:159:30: Cannot assign type 'void (void *, dpiSubscrMessage *) except * nogil' to 'dpiSubscrCallback'
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/tmp/pip-build-1n4gr6zh/oracledb/setup.py", line 95, in <module>
        extra_compile_args=extra_compile_args)
      File "/usr/lib/python3.6/site-packages/setuptools/__init__.py", line 129, in setup
        return distutils.core.setup(**attrs)
      File "/usr/lib64/python3.6/distutils/core.py", line 148, in setup
        dist.run_commands()
      File "/usr/lib64/python3.6/distutils/dist.py", line 955, in run_commands
        self.run_command(cmd)
      File "/usr/lib64/python3.6/distutils/dist.py", line 974, in run_command
        cmd_obj.run()
      File "/usr/lib/python3.6/site-packages/setuptools/command/install.py", line 61, in run
        return orig.install.run(self)
      File "/usr/lib64/python3.6/distutils/command/install.py", line 556, in run
        self.run_command('build')
      File "/usr/lib64/python3.6/distutils/cmd.py", line 313, in run_command
        self.distribution.run_command(command)
      File "/usr/lib64/python3.6/distutils/dist.py", line 974, in run_command
        cmd_obj.run()
      File "/usr/lib64/python3.6/distutils/command/build.py", line 135, in run
        self.run_command(cmd_name)
      File "/usr/lib64/python3.6/distutils/cmd.py", line 313, in run_command
        self.distribution.run_command(command)
      File "/usr/lib64/python3.6/distutils/dist.py", line 974, in run_command
        cmd_obj.run()
      File "/usr/lib/python3.6/site-packages/setuptools/command/build_ext.py", line 78, in run
        _build_ext.run(self)
      File "/usr/lib64/python3.6/distutils/command/build_ext.py", line 339, in run
        self.build_extensions()
      File "/usr/lib64/python3.6/distutils/command/build_ext.py", line 448, in build_extensions
        self._build_extensions_serial()
      File "/usr/lib64/python3.6/distutils/command/build_ext.py", line 473, in _build_extensions_serial
        self.build_extension(ext)
      File "/usr/lib/python3.6/site-packages/setuptools/command/build_ext.py", line 199, in build_extension
        _build_ext.build_extension(self, ext)
      File "/tmp/pip-build-1n4gr6zh/oracledb/.eggs/Cython-3.0.0b1-py3.6.egg/Cython/Distutils/build_ext.py", line 123, in build_extension
        ext,force=self.force, quiet=self.verbose == 0, **options
      File "/tmp/pip-build-1n4gr6zh/oracledb/.eggs/Cython-3.0.0b1-py3.6.egg/Cython/Build/Dependencies.py", line 1134, in cythonize
        cythonize_one(*args)
      File "/tmp/pip-build-1n4gr6zh/oracledb/.eggs/Cython-3.0.0b1-py3.6.egg/Cython/Build/Dependencies.py", line 1301, in cythonize_one
        raise CompileError(None, pyx_file)
    Cython.Compiler.Errors.CompileError: src/oracledb/thick_impl.pyx

    ----------------------------------------
Command "/usr/bin/python3.6 -u -c "import setuptools, tokenize;__file__='/tmp/pip-build-1n4gr6zh/oracledb/setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" install --record /tmp/pip-bprfl3fp-record/install-record.txt --single-version-externally-managed --compile" failed with error code 1 in /tmp/pip-build-1n4gr6zh/oracledb/`
                                    

I haven't seen that with my images, but its good to have it on record. Thanks for sharing. I'll close this.

@cjbj Here is the script.

I'll let @suraj-ora-2020 comment if anything can be improved or added.

I would suggest making sure to drop the new user at the end of the test

This is what I was testing:

import os
import time

import oracledb

MLT = 20
SLEEP = 120

un = os.environ.get('PYTHON_USERNAME')
pw = os.environ.get('PYTHON_PASSWORD')
cs = os.environ.get('PYTHON_CONNECTSTRING')

oracledb.init_oracle_client()

sql = "select sid || ' ' || serial# from v$session where sid = sys_context('USERENV','SID')"

#------------------------------------------------------------------------------

def start_pool():

    pool_min = 0
    pool_max = 1
    pool_inc = 0

    pool = oracledb.create_pool(user=un, password=pw, dsn=cs,
                                min=pool_min,
                                max=pool_max,
                                increment=pool_inc,
                                max_lifetime_session=MLT,
                                session_callback=init_session)

    return pool

def init_session(connection, requestedTag_ignored):
    with connection.cursor() as cursor:
        s, = connection.cursor().execute(sql).fetchone()
        print(f"In init_session: SID and SERIAL#: {s}")

#------------------------------------------------------------------------------

pool = start_pool()

with pool.acquire() as connection:
    s, = connection.cursor().execute(sql).fetchone()
    print(f"In block 1: SID and SERIAL# {s}")
    print(f"Sleeping {SLEEP} seconds with max_lifetime_session of {MLT}")
    connection.cursor().callproc("dbms_session.sleep", (MLT,))

time.sleep(2)

with pool.acquire() as connection:
    s, = connection.cursor().execute(sql).fetchone()
    print(f"In block 2: SID and SERIAL# {s}")

with pool.acquire() as connection:
    s, = connection.cursor().execute(sql).fetchone()
    print(f"In block 3: SID and SERIAL# {s}")

The output is like:

cjones@mdt:~/p$ python3 cp.py
In init_session: SID and SERIAL#: 73 14816
In block 1: SID and SERIAL# 73 14816
Sleeping 120 seconds with max_lifetime_session of 20
In block 2: SID and SERIAL# 73 14816
In init_session: SID and SERIAL#: 73 64501
In block 3: SID and SERIAL# 73 64501
cjones@mdt:~/p$

Showing the session was recreated. However when I played with some times, or set the pool min size to 1, I got different behavior. I suspect there are some heuristics in the implementation.

But fundamentally, trying to reduce the number of sessions to help failover is optimizing for arguably the wrong case. It would generally be better to keep sessions open so normal app access was efficient. If you are worried about the number of sessions, then try DRCP.

1. What versions are you using?

platform.platform: Linux-4.18.0-425.3.1.el8.x86_64-x86_64-with-glibc2.28
sys.maxsize > 2**32: True
platform.python_version: 3.10.1

cx_Oracle.version: 8.3.0
cx_Oracle.clientversion: (21, 4, 0, 0, 0)

2. Describe the problem

I had run the script described in 3 and checked gv$session with the following SQL.

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
col USERNAME for a10
set lines 300
select INST_ID, SID, SERIAL#, username, status, logon_time from gv$session where username = '<username>' order by LOGON_TIME;

Since the first time.sleep waits for more than max_lifetime_session, I thought that a new session would be created in the subsequent dbms_session.sleep, but the session that existed when the pool was created was used as is.

Also, we do not plan to use DRCP and have not set it up(conn_str is as follows).

<scan_name>@<scan_port>/<service_name>

3. Include a runnable Python script that shows the problem.

• Create Table for test

CREATE TABLE TEST_TBL(id number, name varchar2(20), db varchar2(20), drink varchar2(20));

• run a python script below

import cx_Oracle
import pprint
import time

db_config = ('<username>', '<password>', '<conn_str>')
pool = cx_Oracle.SessionPool(user=db_config[0], password=db_config[1],
                             dsn=db_config[2], min=2,
                             max=5, increment=1, max_lifetime_session=30)

def fetch(sql: str, bind: list[str|int] =[]):
    with pool.acquire() as connection:
        with connection.cursor() as cursor:
            cursor.execute(sql, bind)
            rows = cursor.fetchall()
            return rows


def exec(sql: str, bind: list[str|int] =[]):
    with cx_Oracle.connect(db_config[0], db_config[1], db_config[2]) as connection:
        with connection.cursor() as cursor:
            try:
                cursor.execute(sql, bind)
                result = connection.commit()
                return result
            except Exception as e:
                print("exec失敗")
                return e

def dbms_sleep(seconds):
    with pool.acquire() as conn:
        cursor = conn.cursor()
        print("dbms sleep start:", seconds)
        cursor.callproc("dbms_session.sleep", (seconds,))
        print("dbms sleep end:", seconds)

sql_insert_all = """INSERT ALL
INTO test_tbl VALUES (1,'u1', 'oracle', 'sake')
INTO test_tbl VALUES (2,'u2', 'postgresql', 'beer')
INTO test_tbl VALUES (3,'u3', 'oracle', 'wine')
INTO test_tbl VALUES (4,'u4', 'postgresql', 'beer')
INTO test_tbl VALUES (5,'u5', 'mysql', 'wine')
select * from dual"""
sql_select_all = 'select * from test_tbl'
sql_delete='DELETE FROM test_tbl'

result = exec(sql_insert_all)
if result is not None:
    print("insert failed: ", result)
    exit(1)

print("initial data")
rows = fetch(sql_select_all)
pprint.pprint(rows)

print("start")
print("sleeping...")
# sleep longer than max_lifetime_session
time.sleep(70)
print("sleeping...end")
# this session will use a new session
dbms_sleep(10)
print("sleeping...")
time.sleep(70)
print("sleeping...end")
dbms_sleep(10)

print("Delete Data")
result = exec(sql_delete)
if result is not None:
    print("delete failed: ", result)
    exit(1)

print("Done")

I'm going to reopen this since I was also seeing something I wanted to check. Having it open will remind me to look at it.

@suraj-ora-2020 can you provide the actual GRANT statement to make it easy for @petronny ?

• The cx_Oracle behavior of this setting is inherited from the OCI Session Pool OCI_ATTR_SPOOL_MAX_LIFETIME_SESSION attribute behavior.
• The OCI Session Pool behavior may vary with Oracle Client version. In recent versions of Oracle Client, more pool maintenance occurs in the background but in earlier versions may only have been triggered only at acquire or connection close time. (I say this in case people test your scenario with 19c client libraries).
• The 'Thin' mode of python-oracledb (the newest version with a new name of cx_Oracle) may have subtle differences since it doesn't use the OCI Session Pool. The 'Thick' mode does use the OCI Session Pool.
• Our cx_Oracle / python-oracledb doc isn't the greatest on what max_lifetime_session means. By co-incidence we discussed pool shrinkage yesterday and I noted that doc needs improvement.
• You could test inside Python by checking "SELECT SYS_CONTEXT('USERENV','SID') FROM DUAL" for connections acquired from the pool
• Since pools are living & breathing, try with a pool max size of 1.
• look at tests/test_2400_pool.py for some behaviors
• You really don't want to be changing from the default of 0 (no timeout) unless you have a very good reason. Forcing session recreation impacts performance and scalability.
• What are you trying to achieve for your application? Why?

• Fundamentally you should look at moving from Python 3.6 to a newer version of Python.
• Update pip before installing: pip3 install pip --upgrade
• There are RPMs on yum.oracle.com for OL7 & 8 that work with Python 3.6.

  sudo dnf -y install oraclelinux-developer-release-el8
  sudo dnf -y install python3-oracledb
  

  or

  sudo yum -y install oraclelinux-developer-release-el7
  sudo yum -y install python3-oracledb
  

  I'm still working with the Linux team on updates

Discussed in https://github.com/oracle/python-oracledb/discussions/155

Originally posted by mikekenn March 6, 2023 Im following along in the Django documentation and I want to connect the application for a Free Tier OCI DB that I have running.

I am able to connect to the DB via VS Code and perform all the expected actions. But I would like it, so that when I do python manage.py migrate all the tables get created in the Oracle DB rather than the provided sqlite db.

I would also like to be able to run packages/procedures from the app as well. Do I still have to create a connection to the database every time I want to do a query/insert/update? Something like this?

connection = oracledb.connect(
                              user="USERNAME",
                              password="PASSWORD",
                              dsn="DSN_VALUE_FROM_OCI_CONNECTION_UI",
                              config_dir="/Location/ToApp/Wallet",
                              wallet_location="/Location/ToApp/Wallet",
                              wallet_password=pw)

with connection.cursor() as cursor:

    cursor.execute("""
        begin
            execute immediate 'drop table todoitem';
            exception when others then if sqlcode <> -942 then raise; end if;
        end;""")

    cursor.execute("""
        create table todoitem (
            id number generated always as identity,
            description varchar2(4000),
            creation_ts timestamp with time zone default current_timestamp,
            done number(1,0),
            primary key (id))""")

    print("Table created")

I have changed the section in settings.py to the following & I have OracleDB installed

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'NAME_FOUND_IN_ORACLE_CLOUD_UI',
        'USER': 'USERNAME',
        'PASSWORD': 'USER_PASSWORD',
        'HOST': 'Value_In_TNSNAMES.ORA_FILE',
        'PORT': 'Value_In_TNSNAMES.ORA_FILE', 
    }
}

Is there something I am missing? Also, I am working on a "3 GHz 6-Core Intel Core i5" desktop & a "Apple M1" laptop. Guidance on how to get this to work on both machines would be greatly appreciated!

Please let me know if there is more information that is required!

On second thoughts, lets continue the discussion in the duplicate Discussion https://github.com/oracle/python-oracledb/discussions/155

cx_Oracle and oracledb have different behavior when setting undecodable bytes data to an object type.

obj = mytype.newobject()
obj.DATA = b'\x1f\x8b\x08\x00\xa7'

In cx_Oracle, this will not raise an error, and will save the bytes to Oracle correctly.

In oracledb, this raises a UnicodeDecodeError

My use case is that I am logging http requests to an Oracle table. Some requests contain Excel uploads. The bytes for an Excel file are not decodable into the str type.

I have a package with a record type, collection of that record, and a procedure to save the http logs in bulk.

Setting an attribute on the record type to the Excel bytes works on cx_Oracle, and saves to the database correctly.

In oracledb, it doesn't work.

Here is a minimum reproducable example.

CREATE PACKAGE MYPACKAGE
IS
    TYPE MY_RECORD_TYPE IS RECORD (
        data VARCHAR2(4000)
    );
END MYPACKAGE;

import cx_Oracle
import oracledb

oracledb.init_oracle_client()


# These bytes can be found in the start of any Excel and cannot be decoded to str
bad_bytes = b'\x1f\x8b\x08\x00\xa7'

ORACLE_DSN = 'scott/tiger@local'
PLSQL_RECORD_TYPE = 'MYSCHEMA.MYPACKAGE.MY_RECORD_TYPE'


# It works fine with cx_Oracle
cx_conn = cx_Oracle.connect(ORACLE_DSN)
cx_type = cx_conn.gettype(PLSQL_RECORD_TYPE)
cx_rec = cx_type.newobject()

# This works
cx_rec.DATA = bad_bytes

# It fails with oracledb in thick mode
od_conn = oracledb.connect(ORACLE_DSN)
od_type = od_conn.gettype(PLSQL_RECORD_TYPE)
od_rec = od_type.newobject()

od_rec.DATA = bad_bytes

1. What versions are you using?

   oracledb.version '1.2.1'

Give your database version.

19c

Also run Python and show the output of:

>>> print("platform.platform:", platform.platform())
platform.platform: Linux-5.10.102.1-microsoft-standard-WSL2-x86_64-with-glibc2.28

>>> print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
sys.maxsize > 2**32: True

>>> print("platform.python_version:", platform.python_version())
platform.python_version: 3.10.9

>>> oracledb.__version__
'1.2.1'

2. Is it an error or a hang or a crash?

Error

4. What error(s) or behavior you are seeing?

>>> orr.REQUEST_DATA = bad_bytes
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/app/project/lib/python3.10/site-packages/oracledb/dbobject.py", line 54, in __setattr__
    self._impl.set_attr_value(attr_impl, value)
  File "src/oracledb/impl/base/dbobject.pyx", line 94, in oracledb.base_impl.BaseDbObjectImpl.set_attr_value
  File "src/oracledb/impl/base/connection.pyx", line 76, in oracledb.base_impl.BaseConnImpl._check_value
UnicodeDecodeError: 'utf-8' codec can't decode byte 0x8b in position 1: invalid start byte

5. Does your application call init_oracle_client()?

Yes, thick mode.

6. Include a runnable Python script that shows the problem.

See above.

Include all SQL needed to create the database schema.

Format code by using three backticks on a line before and after code snippets, for example:

Closing. I think this has run its course.

Pull Requests to fix issues are welcome.

@suraj-ora-2020 can you comment?