Search code examples
pythonsqloracleoracle-sqldeveloper

How to prevent ORA-00900 error when running Oracle SQL Developer query using pandas.read_sql()?

Below is an example of a SQL Developer query I'd like to run from Python.

sqlQuery = """
DEFINE a = 1
DEFINE b = 2

select &&a + &&b from dual
"""
result = pandas.read_sql(sql=sqlQuery, con=Oracle_Connection)

This query works when I run it inside of SQL Developer, however when I run the above code in Python, I get the following error: 'ORA-00900: invalid SQL statement'. Is there an easy fix I can make to resolve this?

Solution 
  • DEFINE a = 1
DEFINE b = 2

    DEFINE is a command of SQL*Plus or SQLDeveloper, that is not SQL command. You need to use bind variables instead:

    select :a + :b from dual;

    Full example:

    sqlQuery = """
select :a + :b from dual
"""
result = pandas.read_sql(sql=sqlQuery, con=Oracle_Connection, params={'a': 1, 'b': 2})