CODEWITHSUNDEEP
pythonsqloracle-databasecx-oracle
Johhan
Johhan

Reputation: 11

I'm using Python to run a SQL query but I always have this error "ORA-01830"

I try to run this SQL Query using python, cx_oracle, but I recieve a ORA-01830 ERROR

splited_date = (fecha.year, fecha.month, fecha.day)
print("flag {}{:02d}{:02d}".format(*splited_date))
while flag == 1:
    cursor = conn.cursor()  
    print(fecha.strftime('%Y-%m-%d'))
    try:
        print('Filling...')
        formatted_date = fecha.strftime('%Y-%m-%d')
        sql_query = "BEGIN PK_NAME(TO_DATE(:1, 'YYYY-MM-DD HH:MI')); END;"
        cursor.execute(sql_query, [formatted_date])
        print("table_proc")
        flag = 2
    except Exception as error:
        print(error)
        flag = 1
print("Filling finished.")

Upvotes: 0

Views: 81

Answers (2)

MT0
MT0

Reputation: 168232

Don't convert it from a python datetime.date to a string to an Oracle DATE. If fecha is a datetime.date then simply use:

while flag == 1:
    cursor = conn.cursor()  
    try:
        print('Filling...')
        sql_query = "BEGIN PK_NAME(:1); END;"
        cursor.execute(sql_query, [fecha])
        print("table_proc")
        flag = 2
    except Exception as error:
        print(error)
        flag = 1
print("Filling finished.")

If fecha is a datetime.datetime then either use the same code (if you want an Oracle DATE with a non-midnight time component) or:

cursor.execute(sql_query, [fecha.date()])

Upvotes: 1

Anthony Tuininga
Anthony Tuininga

Reputation: 7096

Your format contains year, month day, hour and minute, but the formatted date you supply only contains year, month and day! So the error ORA-01830: Date format picture ends before converting entire input string. makes sense! You need to fix that discrepancy, or better yet, avoid date conversion and just bind a date value directly!

Upvotes: 1

Related Questions