cx_Oracle.DatabaseError: ORA-00933: SQL command not properly ended

Question

I am getting the error cx_Oracle.DatabaseError: ORA-00933: SQL command not properly ended when trying to run the following code. I have used

import cx_Oracle
ip = '127.0.0.1'
port = 1234
SID = 'abcd'
dsn_tns = cx_Oracle.makedsn(ip, port, SID)

conn = cx_Oracle.connect('username', 'password', dsn_tns)
curs = conn.cursor()
curs.execute('select sysdate from dual;')  # Error is here
curs.close()
conn.close()

Running the following works as expected:

conn = cx_Oracle.connect('username', 'password', dsn_tns)
print (conn.version)
conn.close()

Cheeks

I don't think you need the semicolon at the end of the query, maybe it has something to do with that – Martella

@Duikboot, you're correct. Make an answer and I'll accept it. – Cheeks

To explain a bit: the semi-colon is used in command-line tools to tell them that you are not going to enter another line of SQL therefore telling those tools to send all preceding text to the DB for processing,. The DB expects a SQL statement that doesn't have a trailing semicolon. – Inkerman

@ChristopherJones I'm very confused about the conventions. Do you have any documentation I can refer to? I have a script that (a) will not work if there are no semicolons, (b) will not work if there are semi-colons but no trailing semi-colon, and (c) will not work if each line ends in a semi-colon. I get ORA-00933: SQL command not properly ended in all cases. – Baumbaugh

The DB expects SQL statements without a trailing semi-colon. It expects PL/SQL statements with a final semi-colon. This is easy in the old cx_Oracle and its replacement python-oracledb drivers. Other Client tools like SQL*Plus need to be told when you have finished typing in a statement (or whether you might add another line or more). They have a convention which I recently summarized here. SQL*Plus strips the final character before sending SQL or PL/SQL to the database. – Inkerman

Answer 1

You don't need the semicolon at the end of the query, maybe it has something to do with that

Martella

Can you explain why not? – Baumbaugh

@christopher-jones explained it very nice in the comments of the question. – Martella