I have a Python script that has thrown this error. The script has remained functional and working properly until I have changed the paths of the data that is being read in it. Initially the application read 3 different tables which were all in a SQL Server DB. I made a very small subset of each table(3 records) and exported these tables to an Oracle DB, when I am reading the data from the Oracle environment this error is thrown. Here is where I make my ArcSDESQLExecute connection. What is the solution for this?
ticket_sql = "select {0} from {1} where {2} >= '{3}' and CONVERT(time, {4}) > '{5}' and jobid NOT LIKE '%ZZ%' ".format(self.ticket_id_field, self.ticket_audit_table_name, self.modified_date_field, last_date, self.modified_time_field, last_time)
self.logger.debug(ticket_sql)
conn = arcpy.ArcSDESQLExecute(self.korterra_sde_path)
results = conn.execute(ticket_sql)
If you print ticket_sql with the formatted text, does that SQL run in Oracle? I've noticed this error happening on occasion when there is an error in the text string.
Hi Christian,
When I print ticket_sql here is what is returned. Is the CONVERT function incorrect for Oracle? When I run this statement with the SQL Server table, it is correct.
select jobid from SEU_GIS_GAS.actionaudit where actiondtdate >= '2017-01-17' and CONVERT(time,actiondttime) > '14:28:55' and jobid NOT LIKE '%ZZ%'
Hi Geoffrey, what is your goal of the convert? Your inputs look like your inputting two field names, based on the SQL above.
Oracle uses convert character strings from one character set to another.
CONVERT in the above statements converts from a string to TIME field from the SQL table.
So if we are sending this to Oracle and we need to change the string to a time field, then CONVERT cannot be used.
Instead for Oracle, we must use CAST or a TO_TIMESTAMP function.
Can you clarify what "time" and "actiondttime" datatypes are in Oracle? In addition, which field are you attempting to convert, time or actiondttime?
"actiondttime" is a string in oracle, the value time above was used in the SQL expression to note to convert a SQL table field from string to time.
Just for reference, in case you hadn't looked up the codes already: Return codes.
SE_ATTR_NOEXIST (-38)
Returned from functions that attempt to access a column that does not exist. Sometimes returned after misspelling the name of the column.