11

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.

Improve this question

2 Answers 2

Reset to default
14

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
Improve this answer
2
  • The --optfile flag with link to a text file will be very useful for a variety of uses of OGR to store lengthy parameters for other flags than -sql, particularly when you have a number of OGR commands to run. I've read and used the gdal/ogr documentations many times for some years, but never stumbled across the quite obvious --optfile flag just on the front page of the raster and vector pages. The --optfile flag can be used for GDAL as well.
    – Karl Zinglersen
    Commented Mar 16, 2016 at 13:42
  • Apparently, the optfile must not have blank/new lines in the text, so these must be cleaned (e.g. in Notepad++) as well as comments.
    – Karl Zinglersen
    Commented Mar 16, 2016 at 14:54
2

If I understand correctly the command is in the file, so you will need to read that file to get the text.

You can grab the first line of your file with and hold it in the variable sqlcmd using set /p sqlcmd=< sql.sql. And then %sqlcmd% should work in your ogrinfo command.

(I got the command line information from here: https://stackoverflow.com/questions/130116/windows-batch-commands-to-read-first-line-from-text-file.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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