ArcSDESQLExecute: StreamPrepareSQL ArcSDE Error -38/SQL to Oracle?

2957
7
01-20-2017 02:55 PM
GeoffreyWest
Occasional Contributor III

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)

0 Kudos
7 Replies
ChristianWells
Esri Regular Contributor

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. 

0 Kudos
GeoffreyWest
Occasional Contributor III

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%'

0 Kudos
ChristianWells
Esri Regular Contributor

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 

0 Kudos
GeoffreyWest
Occasional Contributor III

CONVERT in the above statements converts from a string to TIME field from the SQL table.   

0 Kudos
ChristianWells
Esri Regular Contributor

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?

0 Kudos
GeoffreyWest
Occasional Contributor III

"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. 

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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.

0 Kudos