OGR2OGR – How to Execute SQL Query from Text File

ogrogr2ogr

In ogr2ogr or ogrinfo, I can successfully query an Oracle database using this command as a test: 

ogrinfo -al -so "OCI:USER/PASSWORD@SERVER/DATABASE" -sql "SELECT * FROM SCHEMA.TABLE"

However, building up the query and reusing older queries, I need to get the -sql flag input from a text file:

ogrinfo -al -so "OCI:USER/PASSWORD@SERVER/DATABASE:SCHEMA.TABLE" -sql C:/TEMP/sql.sql

The return this answer from the database: ERROR 1: ORA-00900
The sql.sql file holds same query (SELECT * FROM SCHEMA.TABLE) as above. I've tried to change the sql.sql files quotes with/without/doubles and others with no luck.

How can I format the sql text file, or ogr flag, so it would be read correctly by Oracle 11g.

Best Answer

It is understandable to read only the manual page of ogr2ogr http://www.gdal.org/ogr2ogr.html and miss the page about options which are general to all OGR utilities https://gdal.org/programs/vector_common_options.html because the first one does not mention that the latter exists. However, you can save you -sql "<query>" terms into text files and reuse them by referencing them with --optfile as documented in the common options for all ogrtools.

Contents of file C:/TEMP/sql.sql:

-sql "SELECT * FROM SCHEMA.TABLE"

Ogr2ogr command to use:

ogrinfo -al -so "OCI:USER/PASSWORD@SERVER/DATABASE:SCHEMA.TABLE" --optfile C:/TEMP/sql.sql

Notice also this usage example and not so clear description sentence in the ogr2ogr manual page:

[-sql |@filename]

-sql sql_statement: SQL statement to execute. The resulting table/layer will be saved to the output. Starting with GDAL 2.1, the syntax can be used to indicate that the content is in the pointed filename.

It means that with GDAL 2.1 which is currently the development version you can use also this syntax:

ogrinfo -al -so "OCI:USER/PASSWORD@SERVER/DATABASE:SCHEMA.TABLE" -sql @C:/TEMP/sql.sql
Related Question