1

I am trying to replicate a working Oracle SQL query into ogr2ogr, but having issues with a date field where clause.

My query is:

select BLOCK_STATUS_DATE, GEOMETRY
from WHSE_FOREST_TENURE.FTEN_CUT_BLOCK_POLY_SVW
where BLOCK_STATUS_DATE > '01-JUL-18'
and rownum < 9
order by BLOCK_STATUS_DATE asc

This query returns a logical result, with dates that are all newer than July 1, 2018.

05-JUL-19   [MDSYS.SDO_GEOMETRY]
05-JUL-19   [MDSYS.SDO_GEOMETRY]
05-JUL-19   [MDSYS.SDO_GEOMETRY]
18-JUL-19   [MDSYS.SDO_GEOMETRY]
18-NOV-19   [MDSYS.SDO_GEOMETRY]
18-NOV-19   [MDSYS.SDO_GEOMETRY]
18-NOV-19   [MDSYS.SDO_GEOMETRY]

The same query, run through an ogr2ogr command, returns 8 rows with dates ranging from 2002 to 2019. It seems the SQL dialect is getting confused between Oracle SQL and whatever dialect ogr2ogr uses.

For completeness, here is my total ogr2ogr command:

ogr2ogr.exe -a_srs epsg:3005 -f "GPKG" T:\test\RKB_cutblocks.gpkg OCI:USER/PASSWORD@IDWPROD1:no_Table -progress -sql @T:\tempQueryFolder\query.sql -lco IDENTIFIER='layer1' -lco DESCRIPTION='layer1' 

If anyone knows how I can cast the date in my query, or specify a proper SQL dialect to ogr2ogr, or any other solution, please let me know!

  • 1
    Oracle dates can be quite tricky. The Oracle way is probably to set the correct NLS_DATE_FORMAT on the client side. You seem to be on Windows so you could do SET NLS_DATE_FORMAT=the_right_format before running your ogr2ogr command. But I think that it is easier and more reliable to create a new column to your Oracle table, fill if with dates as strings, and use the new column for filtering with ogr2ogr. – user30184 Dec 3 '19 at 23:08
  • Thanks - this gets me partway towards a solution . I'm in Oracle 12c and trying to set the format to 'dd-mmm-yy' using the Oracle line: ALTER SESSION NLS_DATE_FORMAT = 'dd-mmm-yy' But I hear you on the new column creation, and think that could work by extracting each element (day, month, year) as separate integer fields, something like: select EXTRACT(year FROM BLOCK_STATUS_DATE) as xYEAR, EXTRACT(month FROM BLOCK_STATUS_DATE) as xMONTH, EXTRACT(day FROM BLOCK_STATUS_DATE) as xDAY – grego Dec 4 '19 at 17:28
1

In your sql statement:

select BLOCK_STATUS_DATE, GEOMETRY from WHSE_FOREST_TENURE.FTEN_CUT_BLOCK_POLY_SVW where BLOCK_STATUS_DATE > '01-JUL-18' and rownum < 9 order by BLOCK_STATUS_DATE asc

The statement relies on oracle to take care of the conversion of '01-JUL-18' to a date. While this works sometimes you never really know how oracle is going to interpret that string and convert it to a date. You can be more explicit by handling the conversion of the string '01-JUL-18' to a date using the to_date oracle function.

Example: select BLOCK_STATUS_DATE, GEOMETRY from WHSE_FOREST_TENURE.FTEN_CUT_BLOCK_POLY_SVW where BLOCK_STATUS_DATE > to_date('01-JUL-18', 'DD-MON-YY') and rownum < 9 order by BLOCK_STATUS_DATE asc

I just tested this sql with the OGR command you provided and can verify that it returned only data after July 1, 2018. That said the extraction was SLOW!

I'm guessing you probably want records for July 1st so you may want to say >=. You can also get down to the time for that date. See (https://www.techonthenet.com/oracle/functions/to_date.php).

Finally while i'm at it if you want to rid yourself of the dependency of the TNSNAMES config with your oracle client installation you can specify your database connection like this (changed output to shapefile to simplify):

ogr2ogr -a_srs epsg:3005 -f "ESRI Shapefile" out.shp OCI:<user>/<pass>@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<host>)(PORT=<ora port>)))(CONNECT_DATA=(SERVICE_NAME=<service name>))):no_table -progress -sql @./query.sql

Hope this helps.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.