pythonsqloracle-databasepython-oracledb

SQL command works fine when run manually (SQL Developer) but gives ORA-00922 in Python's oracledb module

I am working with an Oracle SQL database, and I would like to run the command

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

It works fine when I run it from the SQL Developer app manually. However, when I run it from Python using the oracledb module, I get this error:

Error running SQL script: ORA-00922: missing or invalid option
Help: https://docs.oracle.com/error-help/db/ora-00922/

Just to be clear, I don't have an issue establishing a connection to Oracle using Python.

Here's my code:

import oracledb
import pandas
import os 
import csv
import logging 
import datetime 
import sys 

STARTER_QUERY = r"ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';"

Config = {}
exec(open("config/info-sql.txt").read(), Config) 
# print(Config)

def get_connection():
    connection = oracledb.connect(user=Config["username"], password=Config["password"], dsn=get_dsn(Config['ip'], Config['port'], Config['service_name']))
    return connection 

def run_sql_script(connection, sql_script):
    try:
        print(f"SQL script: {sql_script}")
        logging.info(f"SQL script: {sql_script}")
        cursor = connection.cursor()
        cursor.execute(sql_script)
        columns = [i[0] for i in cursor.description]
        data = cursor.fetchall()
        df = pandas.DataFrame(data, columns=columns)
        return df
    except Exception as e:
        print(f"Error running SQL script: {e}")
        return None
    
connection = get_connection()
if connection is None:
    sys.exit(0)

run_sql_script(connection, STARTER_QUERY)

Is there an issue with how I format the string? Any help would be appreciated.

Solution

  • The error is the ; at the end of the SQL statement. ; is a statement terminator used by IDEs to determine when one statement finishes and the next starts and is not part of the SQL statement.

    If you try to send a statement to an Oracle database that contains the statement terminator then it will raise a syntax error. Delete the ; and your code will work.

    fiddle

    If you want to send SQL multiple statements then either: