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

Question

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?

Sayan Malakshinov · Accepted Answer · 2020-07-09 08:56:10Z

1

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})

answered Jul 9, 2020 at 8:56

Sayan Malakshinov

8,7501 gold badge21 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related