Oracle Bind Variable to_date parameter Error

Question

I am writing code which uses a Python script to execute a SQL (Oracle) statement, as shown below:

PYTHON:

with open(os.path.join(WORKING_PATH, "sql", "fetchCalendar.sql"), 'r') as 
    fetch_date = fetch.read()   
#(Some other code)
CD = timestamp_tostring(ACTUALDATE)
print("CD:", CD)
cnnSB.cursor.execute(fetch_date, CD)

fetchCalendar.sql

SELECT YEARNBR as yearnbr, QUARTERNBR as quarternbr, MONTHNBR as monthnbr, WEEKNBR as weeknbr
 FROM MFS_MFSCALENDAR
WHERE ACTUALDATE = TO_DATE(:CD, 'DD-MON-YY')

When I try running it with the following values, this is outputted:

CD: 30-AUG-18

Traceback (most recent call last): File "AHT_Init.py", line 919, in init_load(data, end_of_week, cur, 3) File "AHT_Init.py", line 644, in init_load populate_rollup(modelid, test_data, train_data, rollup_cmd) File "AHT_Init.py", line 684, in populate_rollup cnnSB.cursor.execute(fetch_date, CD) cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

(Emphasis on 'ORA-01036: illegal variable name/number')

Naturally, I tried changing the variable names to no avail. I tried running the SQL code using Oracle SQL Developer (passing in '01-JUN-18', including the single quotes, when prompted), and got this error:

ORA-01858: a non-numeric character was found where a numeric was expected 01858. 00000 - "a non-numeric character was found where a numeric was expected" *Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model. *Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.

Though, when I swapped out the problematic line with the following:

WHERE ACTUALDATE = TO_DATE('01-JUN-18', 'DD-MON-YY');

the code worked as desired. Can anybody walk me through what the issue might be, and how to solve it? Thank you so much!

EDIT: I am aware of the missing semicolon in the fetchCalendar.sql code; this is necessary for how Python interacts with Oracle in this context. I added a semicolon when trying it on Oracle SQL Developer for testing purposes, but it should not cause an issue when removed for the .sql file.

What happens if you do cnnSB.cursor.execute(fetch_date, CD=CD)? Typically if you use named bind variables you should send them to the execute() function by name as well. This question has the details: stackoverflow.com/questions/32868717/… — David Faber
– David Faber, Commented Mar 1, 2019 at 21:56
What is fetchCalendar.sql? I don't know Python, but is it necessary to pass a string and convert it back to a date? — William Robertson
– William Robertson, Commented Mar 1, 2019 at 23:02

NicholasTW · Accepted Answer · 2019-06-18 18:47:39Z

0

Found the answer: In short, wrongtype error.

My code was passing in a datetime.date to a SQL to_date cast which was expecting a string.

answered Jun 18, 2019 at 18:47

NicholasTW

953 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle Bind Variable to_date parameter Error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related