This is one option using python script (not a model) and the cx_Oracle library. This would allow you to put the CAST from string to numeric of your x_coord/y_coord fields in the Oracle db. The basic idea is to fill a cursor with the results of the SQL, append those cursor rows to a new array/list, covert the list to a NumPy array and finally convert that to a FeatureClass.This example is pulled from an implementation I have that does something similar but you will have to straighten out the SQL for your needs and it will not work if you just copy/paste, but it should get you close to what you want I think.Hope this helps!
import arcpy
import cx_Oracle
import numpy as np
### Build a DSN (can be subsitited for a TNS name)
dsn = cx_Oracle.makedsn(param1, param2, param3)
oradb = cx_Oracle.connect("username", "password", dsn)
cursor = oradb.cursor()
sqlQry = """SELECT MyTableOrView.SomeField1 AS SomeTEXTField,
CAST(TO_CHAR(MyTableOrView.x_coords, 'fm9999999.90') AS FLOAT) AS x_coords,
CAST(TO_CHAR(MyTableOrView.y_coords, 'fm9999999.90') AS FLOAT) AS y_coords
FROM MyTableOrView"""
cursor.execute(sqlQry)
datArray = []
cxRows = cursor.fetchall()
for cxRow in cxRows:
datArray.append(cxRow)
#close the conn to ora
cursor.close()
oradb.close()
del cxRows, cursor
numpyarr_out = np.array(datArray, np.dtype([('SomeTEXTField', '|S25'), ('x_coords', '<f8'), ('y_coords', '<f8')]))
#convert the numpyarray to a gdb feature class
outFC = r'C:\MyGDB\xyPoints_FromStrings
if arcpy.Exists(outFC):
arcpy.Delete_management(outFC)
arcpy.da.NumPyArrayToFeatureClass(numpyarr_out, outFC, ("x_coords", "y_coords"))
Even better code that elimiates the "datArray" construction as it is not needed. Just use the cxRows list as-is! (I need go back and update some things now -- sometimes posting on these threads makes you re-evaluate things!)
import arcpy
import cx_Oracle
import numpy as np
### Build a DSN (can be subsitited for a TNS name)
dsn = cx_Oracle.makedsn(param1, param2, param3)
oradb = cx_Oracle.connect("username", "password", dsn)
cursor = oradb.cursor()
sqlQry = """SELECT MyTableOrView.SomeField1 AS SomeTEXTField,
CAST(TO_CHAR(MyTableOrView.x_coords, 'fm9999999.90') AS FLOAT) AS x_coords,
CAST(TO_CHAR(MyTableOrView.y_coords, 'fm9999999.90') AS FLOAT) AS y_coords
FROM MyTableOrView"""
cursor.execute(sqlQry)
datArray = []
cxRows = cursor.fetchall()
#close the conn to ora
cursor.close()
oradb.close()
del cursor
numpyarr_out = np.array(cxRows, np.dtype([('SomeTEXTField', '|S25'), ('x_coords', '<f8'), ('y_coords', '<f8')]))
#convert the numpyarray to a gdb feature class
outFC = r'C:\MyGDB\xyPoints_FromStrings
if arcpy.Exists(outFC):
arcpy.Delete_management(outFC)
arcpy.da.NumPyArrayToFeatureClass(numpyarr_out, outFC, ("x_coords", "y_coords"))