ORA-01008: not all variables bound - but they are bound

[lironshurgi]

1. What versions are you using?
   platform.platform: Linux-5.10.104-linuxkit-x86_64-with-glibc2.31
   sys.maxsize > 2**32: True
   platform.python_version: 3.9.13
   oracledb.version: 1.0.2
   sqlalchemy.version: 1.4.35
   pyramid.version: 2.0

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

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

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/wsgiref/handlers.py", line 137, in run
    self.result = application(self.environ, self.start_response)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/router.py", line 270, in __call__
    response = self.execution_policy(environ, self)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/router.py", line 276, in default_execution_policy
    return router.invoke_request(request)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/router.py", line 245, in invoke_request
    response = handle_request(request)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/tweens.py", line 43, in excview_tween
    response = _error_handler(request, exc)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/tweens.py", line 17, in _error_handler
    reraise(*exc_info)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/util.py", line 733, in reraise
    raise value
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/tweens.py", line 41, in excview_tween
    response = handler(request)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/router.py", line 143, in handle_request
    response = _call_view(
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/view.py", line 674, in _call_view
    response = view_callable(context, request)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/viewderivers.py", line 392, in viewresult_to_response
    result = view(context, request)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/viewderivers.py", line 141, in _requestonly_view
    response = view(request)
  File "/opt/app/AppCardAPI/tests/test_shurgi_pyramid.py", line 27, in get_api
    promotion_data = request.oracle_dbsession.execute(
  File "<string>", line 2, in execute
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1692, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/project_venv/lib/python3.9/site-packages/oracledb/cursor.py", line 378, in execute
    impl.execute(self)
  File "src/oracledb/impl/thin/cursor.pyx", line 122, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src/oracledb/impl/thin/protocol.pyx", line 301, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 302, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 295, in oracledb.thin_impl.Protocol._process_message
sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) ORA-01008: not all variables bound

[SQL:
                SELECT description
                FROM pmt.promotions
                WHERE promotion_id = :promotion_id
            ]
[parameters: {'promotion_id': 8597}]

(Background on this error at: https://sqlalche.me/e/14/4xp6)

4. Does your application call init_oracle_client()?
   No (the issue doesn't reproduce when using Thick mode)

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

Run the server:

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

from wsgiref.simple_server import make_server
import os
from pyramid.config import Configurator
from pyramid.response import Response
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker


def post_api(request):
    promotion_data = request.oracle_dbsession.execute(
        """      
                SELECT description
                FROM pmt.promotions
                WHERE promotion_id = :promotion_id
            """, {"promotion_id": request.matchdict["club_id"]}
    ).fetchone()
    request.oracle_dbsession.commit()
    return Response(status=204)

def get_api(request):
    promotion_data = request.oracle_dbsession.execute(
        """      
                SELECT description
                FROM pmt.promotions
                WHERE promotion_id = :promotion_id
            """, {"promotion_id": 8597}
    ).fetchone()
    request.oracle_dbsession.commit()
    return Response(status=200)


if __name__ == '__main__':
    # oracledb.init_oracle_client()
    with Configurator() as config:
        config.add_route('post_api', '/post_api/{club_id}')
        config.add_route('get_api', '/get_api')
        config.add_view(post_api, route_name='post_api')
        config.add_view(get_api, route_name='get_api')

        # Oracle connection
        engine = create_engine(
            f'oracle://{os.getenv("oracle_user")}:{os.getenv("oracle_pass")}@{os.getenv("oracle_host")}:{os.getenv("oracle_port")}/{os.getenv("oracle_db")}',
            convert_unicode=False, pool_recycle=10, pool_size=50, echo=True, echo_pool="debug"
        )
        oracle_session_factory = scoped_session(sessionmaker(bind=engine))
        config.add_request_method(
            lambda r: oracle_session_factory, "oracle_dbsession", reify=True
        )

        app = config.make_wsgi_app()
    server = make_server('0.0.0.0', 6333, app)
    print("--- start server")
    server.serve_forever()

Run the test and get the error on the server:

def test_load():
    import requests
    for i in range(2):
        res_1 = requests.post("http://0.0.0.0:6333/post_api/123",)
        res_2 = requests.get("http://0.0.0.0:6333/get_api",)
        print(res_1.status_code, res_2.status_code, i)

[anthony-tuininga]

The thin driver isn't supposed to ever get ORA-01008! There is code to perform all sorts of checks. I just ran this code and it produced the expected results:

sql = """      
                SELECT description
                FROM pmt.promotions
                WHERE promotion_id = :promotion_id
            """
cursor = conn.cursor()
cursor.prepare(sql)
print(cursor.bindnames())

This produces (for me) the results:

['PROMOTION_ID']

I presume you would get the same results. Assuming that is the case, can you replace your SQL with an equivalent set of SQL that demonstrates the problem but which I can also run? Are you able to demonstrate the issue without SQLAlchemy? Another option for debugging this: set the environment variable PYO_DEBUG_PACKETS to any value and rerun your script. You can skip the first part (that establishes the connection). Look for the first packet that contains your SQL and show the rest as an attachment.

[lironshurgi]

1. Are you able to demonstrate the issue without SQLAlchemy? - no, without sqlalchemy the issue doesn't reproduce
2. Logs with PYO_DEBUG_PACKETS: https://gist.github.com/lironshurgi/9577c35ddde5f956f406f5eee498c45c

[anthony-tuininga]

Interesting! Are you able to replicate the issue with a standalone example that I could run as well? That would be the most helpful. I'll see if I can get anything useful from the packet output, though. Thanks for providing that!

[anthony-tuininga]

From those logs I see an additional error (fetch out of sequence) and the fact that the statement is indeed being executed without passing any parameters -- but why is a mystery. I tried replacing the table pmt.promotions with a simple table containing the columns promotion_id and description and populating it with rows for the values 123 and 8597 -- but that didn't replicate the issue and everything works as it should. I am using sqlalchemy 1.4.39 and pyramid 1.10.5 -- not sure if that matters. Can you try using the same approach? I am also using Oracle Database 21. What database version are you using?

[anthony-tuininga]

One note: I see that the type bound in the first case is string and the second is integer. If you make them both string or both integer does the problem go away?

[lironshurgi]

1. I'm not sure why, but I can't reproduce it with any other table (I tried to create a few as you did)
2. I tried to test it with sqlalchemy 1.4.39 and got the same error
3. Oracle Database version: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0
4. Both type bounds are strings -> same error. one string one int (doesn't matter which one) -> same error. Both ints -> works.

[anthony-tuininga]

Interesting! So did you add data into the table that matches the data in your pmt.promotions table? The packet output suggests that the description column contains some interesting data -- not what I would expect. Can you run this in SQL*Plus?

select dump(description) from pmt.promotions where promotion_id = 123;
select dump(description) from pmt.promotions where promotion_id = 8597;

Can you also provide the table structure for the table? Preferably with the SQL used to create the table. This looks like an interesting problem. :-)

[lironshurgi]

1. Can you run this in SQL*Plus - When try to run dump I get this error: ORA-00932: inconsistent datatypes: expected - got CLOB. Without the dump I'm getting nothing for 123 and some description for 8597. I tested it '123' and 456 (which both of them don't exist in the table and still got the same error as in the issue)
2. table structure - I'm sorry I cannot provide the whole table structure but here is the relevant data (let me know if something else is needed)

create table pmt.PROMOTIONS
(
    PROMOTION_ID            NUMBER(10)                        not null,
    NAME                    VARCHAR2(50 char)                 not null,
    DESCRIPTION             CLOB                              not null,
    ...
    ...
)

[anthony-tuininga]

Thanks. That was sufficient. If I have a table with the description column being a CLOB I am able to replicate the issue. Now to find out what might be causing the problem to occur!

[anthony-tuininga]

For future reference, this setup script:

create table issue_41 (
    promotion_id number(9),
    description clob
);

insert into issue_41 values (8597, 'Description for 8597');
insert into issue_41 values (123, 'Description for 123');

commit;

and this Python script:

import oracledb

oracledb.defaults.fetch_lobs = False

SQL = "select description from issue_41 where promotion_id = :promotion_id"

conn = oracledb.connect("user/password@host/service_name")

# first round
cursor = conn.cursor()
cursor.execute(SQL, promotion_id="123")
result = cursor.fetchall()
print("Round 1:", result)

# second round
try:
    cursor.execute(SQL, promotion_id="8597")
    result = cursor.fetchall()
    print("Round 2:", result)
except oracledb.DatabaseError as e:
    print("Round 2: failure with:", e)

# third round
try:
    cursor.execute(SQL, promotion_id=8597)
    result = cursor.fetchall()
    print("Round 3:", result)
except oracledb.DatabaseError as e:
    print("Round 3: failure with:", e)

are sufficient to replicate the issue without SQLAlchemy involved. I hope to have a fix for this soon. Thanks for reporting it!

[anthony-tuininga]

I've just pushed a patch that corrects this. The problem occurs when a define is required (such as when converting CLOB to VARCHAR2, as is occurring in this scenario) and the bind parameter type/size changes. If you are able to build and test, you can confirm that. Otherwise, using the same type/size for your parameters will bypass this issue. Again, thanks for reporting this issue!

[lironshurgi]

Confirmed. When use both ints/strings with the same size - issue doesn't reproduce.
Thanks for your quick help

[anthony-tuininga]

This fix is included in python-oracledb 1.0.3, which was just released.