CODEWITHSUNDEEP
pythonoracle11g
Virginia Walters
Virginia Walters

Reputation: 23

Python issue with feeding variable to SQL statement. cur.execute(None, {'id': test}) cx_Oracle.DatabaseError: ORA-00911: invalid character

I am trying to feed a variable from input to a SQL statement that I want to execute. I keep getting an error invalid char. Thank you in advance for your help

import cx_Oracle
import pandas as pd


con = cx_Oracle.connect(user/password@database')
print("Enter the \"SQLID\" to search")
test = input()

cur = con.cursor()
#cur.prepare('select * from dba_users where username  = :sql_id')
cur.prepare('select sample_time,session_id,session_serial\#,sql_id,sql_plan_hash_value,wait_time  \
from v$active_session_history \
where sql_id = :id \
order by sample_time desc')


cur.execute(None, {'id': test})
columns = [desc[0] for desc in cur.description]
data = cur.fetchall()
df = pd.DataFrame(list(data), columns=columns)

writer = pd.ExcelWriter('Data.xlsx')
df.to_excel(writer, sheet_name='TEST1')
writer.save()

res = cur.fetchall()
print(res)

cur.close()
con.close()

Input variable is a Varchar here is an example 5m6mu5pd9w028

Upvotes: 0

Views: 98

Answers (1)

Luke Woodward
Luke Woodward

Reputation: 64959

The problem is the \ character in your SQL string, in session_serial\#. Get rid of it.

Also, you can remove the need to use backslashes as line continuation characters by using triple-quoted strings:

cur.prepare('''select sample_time,session_id,session_serial#,sql_id,sql_plan_hash_value,wait_time
from v$active_session_history 
where sql_id = :id
order by sample_time desc''')

Upvotes: 2

Related Questions