CODEWITHSUNDEEP
sqloracle-databasepython-2.7cx-oraclepython-datetime
A.Pet
A.Pet

Reputation: 97

cx_Oracle.DatabaseError: ORA-00904: "DATETIME"."DATETIME": invalid identifier

I'm trying to export an Oracle DB with Python's cx_Oracle. I connect to DB, query all of the rows I need, and then build an INSERT statement with them. I'm using code similar to this:

table_name = "AUDIT"
where_clause = "id > 10"
query = "SELECT * FROM " + table_name + " WHERE " + where_clause
curs = db_conn.cursor()
curs.execute(query)
res = curs.fetchall()
columns = []
for i in range(0, len(curs.description)):
    columns.append(curs.description[i][0])
column_names = ",".join(columns)
statement = "INSERT INTO %s (%s) VALUES %s" % (table_name, column_names, row)
print statement

The types are NUMBER,VARCHAR2,DATE,NUMBER, and I get an insert statement that looks good... except for the date field:

INSERT INTO AUDIT_LOG (LOG_DBID,USER_NAME,EVENT_DATE,SEVERITY) VALUES ('AUDIT_LOG_SQ.nextval', 'user@do',datetime.datetime(2018, 4, 28, 1, 57, 42), '1')

When I try to execute this, whether via sqlplus or the same cursor, I get the following error:

Error starting at line 1 in command:
INSERT INTO AUDIT_LOG (LOG_DBID,USER_NAME,EVENT_DATE,SEVERITY) VALUES ('AUDIT_LOG_SQ.nextval', 'SYSTEM@do',datetime.datetime(2018, 4, 28, 1, 57, 42), '1')
Error at Command Line:1 Column:95
Error report:
SQL Error: ORA-00904: "DATETIME"."DATETIME": invalid identifier
00904. 00000 -  "%s: invalid identifier"

Without the datetime element the queries work just fine. How do I insert a result I got from a cursor?

I checked SQL_Developer exports to compare with mine, and they have a different way of handling it:

INSERT INTO AUDIT_LOG (LOG_DBID,USER_NAME,EVENT_DATE,SEVERITY) VALUES ('AUDIT_LOG_SQ.nextval', 'user@do',to_timestamp('25-APR-18','DD-MON-RR HH.MI.SSXFF AM'), '1')

Which I haven't really found a way to duplicate (without turning the whole query into an ugly string concatenation), and I'm not sure I need to - there must be something I'm missing!

Any help would be appreciated, thanks!

Upvotes: 0

Views: 6743

Answers (2)

APC
APC

Reputation: 146229

SQL Error: ORA-00904: "DATETIME"."DATETIME": invalid identifier

You get this error because datetime.datetime is not valid Oracle syntax. You sort of know that, because you know how Oracle SQL Developer formats timestamps. But you're don't want to replicate the explicit to_timestamp() logic.

It's not clear to me where you're getting the values of EVENT_DATE from, but given that this is an audit function maybe what you need is sysdate , an Oracle built-in which gives us the current date and time.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

I don't know how to do what you're doing, but - as for 5 hours there are no answers, let me say a word or two.

This is a statement that failed:

INSERT INTO AUDIT_LOG (LOG_DBID,USER_NAME,EVENT_DATE,SEVERITY) VALUES 
('AUDIT_LOG_SQ.nextval', 'SYSTEM@do',datetime.datetime(2018, 4, 28, 1, 57, 42), '1')

What do you expect datetime.datetime to be? To me (and, presumably, Oracle), it looks as if it there's user whose name is datetime (the first one, in front of the dot) which has a function named datetime (the second one, behind the dot). Most probably neither of these two is true, is it?

I'd suggest you to try with the TO_DATE function which would accept two parameters: date (and time) and appropriate format mask, such as 

to_date('2018-04-28 01:57:42', 'yyyy-mm-dd hh24:mi:ss')

You'd put that instead of your datetime construct.

However, I'm afraid that - even if you do that - INSERT might fail once again because of the first value you're inserting.

LOG_DBID looks like an ID (NUMBER datatype); is it? INSERT you wrote is trying to insert a string into it - because you enclosed it into single quotes: 'audit_log_sq.nextval'. AUDIT_LOG_SQ is a sequence name. So, if you want to insert a numeric value into that column, you should omit single quotes.

Finally, statement that might work (at least, in Oracle itself) would look like this:

INSERT INTO AUDIT_LOG (LOG_DBID,USER_NAME,EVENT_DATE,SEVERITY) VALUES 
(AUDIT_LOG_SQ.nextval, 
 'SYSTEM@do', 
  to_date('2018-04-28 01:57:42', 'yyyy-mm-dd hh24:mi:ss'), 
 '1')

I hope it makes sense, even in your context.

Upvotes: 3

Related Questions