Loading non-spatial table in QGIS by querying Oracle database

Question

I am trying to create all (non-spatial) layer in QGIS2.16.2 Which uses a "select / join query" on Oracle database as data source.

I can successfully verify my query by executing it in QGIS DB Manager but It seems not to be possible to "load it as New Layer". After pressing the button "load now" nothing happens.

After some experiments I came to the conclusion that QGIS DB Manager only accepts simple queries (no joins ect ..) and needs a unique key column and a geometry column to load ash layer.

Unfortunately I have "read-only" permissions on the Oracle database, so creating a new view in the database is not possible.

Querying the different Oracle tables locally, using virtual layers takes to long or even makes QGIS crash due to the large data sets.

Are there other options?

Answer 1

It is indeed possible to load a layer which uses a "select / join query" on Oracle (or other) database as data source.

QGIS only accepts query’s which returns at least a “unique integer key column”. If the dataset can’t provide this, you can always use the row_number() SQL function generate a “unique integer column”. The query will have to look like this: SELECT row_number() over (order by my_column) as tmp_ID, my_column, my_other_column FROM my_table;

Just verify the query by executing it in QGIS DB Manager and select “load as new layer”, and select “Column with unique values” and select “tmp_ID” , for none spatial tables a “geometry column” is not necessary.

Further info: http://docs.qgis.org/2.0/en/docs/training_manual/databases/db_manager.html