ogr2ogr with a sql statement fails to convert an oracle database to a shapefile - quote issue?

[ptitjano]

Steps to reproduce the problem.

The following command works correctly

ogr2ogr -f "ESRI Shapefile" -a_srs EPSG:4326 tmp/P_30140_ENTITY_POINT.shp OCI:SUR_WEB/password@QUADRIGE -sql @point_OK.sql

with points_ok.sql:

SELECT e.ENT_ID , e.ENT_LATITUDE AS LATITUDE, e.ENT_LONGITUDE AS LONGITUDE, p.ENT_POSITION 
	FROM SUR_DBA.P_30140_ENTITY_POINT p, SUR_DBA.P_30140_ENTITY e 
	WHERE p.ENT_ID = e.ENT_ID

This command fails

ogr2ogr -f "ESRI Shapefile" -a_srs EPSG:4326 tmp/P_30140_ENTITY_POINT.shp OCI:SUR_WEB/password@QUADRIGE -sql @point_KO.sql

with point_ko.sql:

SELECT e.ENT_ID , TO_NUMBER(TRIM(REPLACE(e.ENT_LATITUDE, '.', ','))) AS LATITUDE, TO_NUMBER(TRIM(REPLACE(e.ENT_LONGITUDE, '.', ','))) AS LONGITUDE, p.ENT_POSITION 
	FROM SUR_DBA.P_30140_ENTITY_POINT p, SUR_DBA.P_30140_ENTITY e 
	WHERE p.ENT_ID = e.ENT_ID

It returns the following error:

ogr2ogr -f "ESRI Shapefile" -a_srs EPSG:4326 tmp/P_30140_ENTITY_POINT.shp OCI:SUR_WEB/password@QUADRIGE -sql @point_KO.sql
ERROR 1: ORA-01722: invalid number
 in SELECT e.ENT_ID , TO_NUMBER(TRIM(REPLACE(e.ENT_LATITUDE, '.', ','))) AS LATITUDE, TO_NUMBER(TRIM(REPLACE(e.ENT_LONGITUDE, '.', ','))) AS LONGITUDE, p.ENT_POSITION          FROM SUR_DBA.P_30140_ENTITY_POINT p, SUR_DBA.P_30140_ENTITY e         WHERE p.ENT_ID = e.ENT_ID
ERROR 1: Terminating translation prematurely after failed
translation from sql statement.

However, point_ko.sql is a valid sql statement. It looks like an issue with the single quotes.

Operating system

Linux Debian stable / unstable

GDAL version and provenance

Tested with gdal 3.6.2 and 3.7.1

[rouault]

I know very little about Oracle and the OCI driver, but I doubt this is an issue about quoting. Perhaps an issue with encoding, decimal separator, etc. ?

https://gdal.org/drivers/vector/oci.html mentions: "It might be necessary to define the environment variable NLS_LANG to "American_America.UTF8" to avoid issues with floating point numbers being truncated to integer on non-English environments."

You should try to reduce the error to the minimum SQL, with ogrinfo, probably only using to_number() on a literal or column

http://www.dba-oracle.com/sf_ora_01722_invalid_number.htm might also help