Why is 'invalid length' error message showing when using Oracle_cx?

Question

Using cx_Oracle, I am trying to use a Python script to execute a sql command, using the bind variables 'plat' and 'tick'. When trying to execute this command, it gives me the error "ORA-24373: invalid length specified for statement".

To debug, I made a SQL call through Oracle (not Python) using the same arguments as my script (plat=1234567, tick='ABCDE'), and it ran as expected. I tried passing the parameters as both a dict and individual named variables, but both times I got the same error.

I tried changing the values to be lower ('1' and 'A'), but even that is an 'invalid length'.

updateRecords.py

import os
import cx_Oracle

# For security reasons I cannot show my 'create_connection()' function, 
# but suffice to say I have tested it and it works as desired.

...

#Setup:
WORKING_PATH = os.path.dirname(os.path.abspath(__file__))
SQL_PATH = os.path.join(WORKING_PATH, 'sql')
cnnSB = create_connection()
cnnSB_cursor = cnnSB.cursor()

...
fetchComp = open(os.path.join(SQL_PATH, 'fetchRecentEntry.sql'), 'r')
for x in range(0, 5):
    cnnSB_cursor.execute(fetchComp.read(), {"plat":'A', "tick":1}) # ERROR LINE

fetchRecentEntry.sql

select * 
from MFS_PCIINCEXTFUNDBYPLAT
where PLATFORM = :plat
and TICKER = :tick
and STARTDATE = (select max(STARTDATE) from MFS_PCIINCEXTFUNDBYPLAT
                 where PLATFORM = :plat
                 and TICKER = :tick)

The above snippet results in the following error message:

File "updateRecords.py", line 297, in main
    cnnSB_cursor.execute(fetchComp.read(), plat='A', tick=1)
cx_Oracle.DatabaseError: ORA-24373: invalid length specified for statement

Other things I have checked:

-My fetchComp.read() DOES return the desired code

-Passing in variables as a dict object does NOT change the error message

It "works" for me (I get an ORA-942- it would be good if you gave a complete test case, gave versions and operating system info). You could check line ending and character set issues. — Christopher Jones, Jul 24 '19 at 05:55

NicholasTW · Accepted Answer · 2019-07-24T16:25:13.513

I found a solution: The issue comes from the .read() being called inside of a loop. As a result, it would read the file correctly the first time, but on subsequent loops it would only read the null/EOF.

To fix, all I had to do was set the sql.read() to a variable before the loop, and use that variable instead of calling .read() with each loop.

Example:


sql = fetchComp.read()
    for index, testRow in testDF.iterrows():
        cnnSB_cursor.execute(sql, tick=testRow[1], plat=testRow[0])
        compDF = pd.DataFrame(cnnSB_cursor.fetchall())

Why is 'invalid length' error message showing when using Oracle_cx?

1 Answers1