We use an Oracle 10g database. However we write reports using SSRS or report builder, using the Oracle connection type to the datbase. In oracle we have 2 text fields, one for lat and one for long. (I am in the US and we use WGS84 coordinates). Also, I only have SELECT rights on the oracle server. I cannot manipulate the database. I can create functions if that helps.
I need to plot these on a point layer in an SSRS map. However, when I use the following code in my select statement, SSRS throws an error:
Unsupported Oracle Data type USERDEFINED encountered
SDO_GEOMETRY('POINT(' || Longitude || ' ' || Latitude || ')',4326) as Coordinates
If I use the SQL code below I get an error that the FROM keyword is not found (makes sense since oracle doesn't use ::)
geography::STPointFromText('POINT(' + CAST(longitude AS VARCHAR(20)) + ' ' + CAST(latitude AS VARCHAR(20)) + ')', 4326)
How can I use the lat and long stored in Oracle text fields as geo data types that SSRS can use to plot the point? (NOT SQL Server... there is no SQL Server, only an SSRS server)