CODEWITHSUNDEEP
pythonsqloracle-databasepython-oracledb
MSuccessor
MSuccessor

Reputation: 21

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.

Upvotes: 1

Views: 76

Answers (2)

Ivan Gonzalez
Ivan Gonzalez

Reputation: 1

  • The prefix r at the beginning is redundant since you are not using any escape characters.

  • Where are you defining get_dsn?

  • The command ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD' could not retrieve any results, check that first.

  • Try adding a Try-Except statement to check, something like this:

    try:
         print(f"SQL script: {sql_script}")
         logging.info(f"SQL script: {sql_script}")
         cursor = connection.cursor()
         cursor.execute(sql_script)

         if cursor.description:
             columns = [i[0] for i in cursor.description]
             data = cursor.fetchall()
             df = pandas.DataFrame(data, columns=columns)
             return df
         else:
             #Whatever you need
except Exception as e:

Upvotes: 0

MT0
MT0

Reputation: 168470

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:

  • Send them one-by-one (omitting the statement terminator each time); or
  • Wrap then in a PL/SQL block as that can contain multiple nested statements. The PL/SQL block must contain the ; terminators for the PL/SQL and SQL statements within it but must not contain the / terminator for the PL/SQL block.

Upvotes: 2

Related Questions