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?

  • How many times are you going to have to do this? – DPSSpatial Sep 28 '16 at 15:10
  • I would like to create two none-spatial layers in QGIS which represent a subset of data from the ORACLE database. One table would provide all the info about “cable-assets”, the other table would contain all the info about “cable-endpoints assets” A separate spatialLite database will host the geometry data. The tables with a ORACLE query as data source would be very frequently accessed, in order to have fluent operation these datasets needs to be as limited as possible. – PeterB Sep 28 '16 at 20:14
  • DB Manager will allow you to load SQL-based layers without geometry - you should try to create a temporary objectID column in your SQL using a row_number() function stackoverflow.com/questions/15091849/… – DPSSpatial Sep 28 '16 at 20:53

thx, DPSSpatial!

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

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.