QGIS » QGIS Application

A side note: It looks like QGIS user must be the owner of the tables and views. In enterprise environment that is not so common. Normally db admins create the tables and views and grant rights to users based on their roles.

Hello,

in my case, I am the owner of the view, so this is not a DB rights problem.

Furthermore, as far as I can see, you do not need to be the owner to work with a table under QGis. In my environment, I can work (read or write) with layers or non-geographic tables which are stored under another schema than my user's mine.

There is an option in the Oracle Data Provider dialog box dedicated to connexions where you can enable listing of tables than are not in the schema of the user.

Regards,

I was just looking at the SQL that you captured: ...FROM user_tab_columns.

I have actually never used QGIS with Oracle but I have a few databases available and I can try to do some short test. But there are quite a many check boxes in the dialog. Could you add a complete list of all the selections that you have used in your trials?

Did you capture the SQL from the Oracle side or do you know some clever way for capturing them from QGIS? I have so limited Oracle user rights that I can't capture anything from Oracle. Anyway, the key is in "c.data_type='SDO_GEOMETRY'" which selects only objects with SDO_GEOMETRY column. Check box "List tables without geometries" should obviously remove that rule.

Hello,

I have "search only in user tables" checked on Oracle Provider connexions. As a result, QGis looks only in USER* tables.

Anyway, the key is in "c.data_type='SDO_GEOMETRY'" which selects only objects with SDO_GEOMETRY column. Check box "List tables without geometries" should obviously remove that rule.
But there is a UNION query in the SQL query above and you can see that it grabs anything that is a table or a view (WHERE c.object_type IN ('TABLE','VIEW','SYNONYM')) even if there is no geometric column.

To capture SQL queries, I just use Wireshark and make it capture all the traffic between my workstation and the Oracle server. The network stream is not encrypted so you can easily capture it (Analyse TCP flow)...

How is that view defined? Does it have numeric columns? e.g. describe in sqlplus.

You could also check the debug output.

I made a simple test by making a new view as
create view qgis_test_view as (select string_attribute from table);

This new view does not show in the Add Oracle Table(s) dialog even if the "Also list tables with no geometry" is checked so I can confirm this issue. I believe also that not all real non-spatial tables were listed but I had severe troubles with testing. QGIS 2.6 did not seem to finish the reading of layer list ever even I had checked the "Use estimated table metadata" option.

Hello,

the view is defined like this:

CREATE OR REPLACE VIEW "DDEV1"."PAE_LST" AS SELECT "ID_PAE", "TICO" FROM "PAE";

It is created with user DDEV1. Here is the SQL creation of table "PAE":

CREATE TABLE "PAE" (
    ID_PAE number(5),
    REF_PAE varchar2(9),
    REF_EXT varchar2(9),
    CODE_EI varchar2(10),
    COPYRIGHT varchar2(400),
    DENQ number(4) CONSTRAINT "PAE_DENQ_NN" NOT NULL,
    DBOR varchar2(50) CONSTRAINT "PAE_DBOR_NN" NOT NULL,
    NOMS varchar2(400) CONSTRAINT "PAE_NOMS_NN" NOT NULL,
    ETUD number(4) CONSTRAINT "PAE_ETUD_NN" NOT NULL,
    PETUD number(4),
    PDEN varchar2(300),
    ACTU varchar2(300),
    TICO varchar2(150),
    REG number(3),
    DPT number(3),
    COM number(5),
    INSEE number(5),
    PLOC varchar2(150),
    AIRE varchar2(150),
    LIEU varchar2(150),
    ADRS varchar2(150),
    CADA varchar2(100),
    REF_ENS number(4) ,
    IMPL number(4),
    SCI varchar2(50),
    DATAT varchar2(6),
    AUTR varchar2(255),
    PERS varchar2(255),
    LIEUH varchar2(150),
    HIST varchar2(4000) ,
    HISTURL varchar2(255),
    EPAN number(8,5),
    TYPO varchar2(300),
    PETAT varchar2(500),
    CONT varchar2(2000),
    DESCRI varchar2(4000),
    DESCURL varchar2(255),
    ZPLU number(9),
    PSTA number(4),
    VISI CHAR CHECK (VISI IN (0,1)),
    PSITE varchar2(300),
    DPRO varchar2(150),
    PPRO varchar2(500),
    PINDPAT varchar2(500),
    INTE number(4),
    AREMA varchar2(300),
    OBS varchar2(4000),
    ENTR varchar2(500),
    PRECO varchar2(4000),
    PRECOURL varchar2(255),
    OPROT number(4),
    AVIS_DPARC varchar2(2000),
    PEOB varchar2(1000),
    VALID CHAR CHECK (VALID IN (0,1)),
    ACCES CHAR CHECK (ACCES IN (0,1)),
    GEOM SDO_GEOMETRY,
    CONSTRAINT "PAE_PK" PRIMARY KEY ("ID_PAE")
);

When I activate debug, I can see this:

[4136] ..\\..\\..\\..\\..\\..\\src\\providers\\oracle\\qgsoracletablemodel.cpp(44) : (QgsOracleTableModel::addTableEntry) [0ms] DDEV1.PAE_LST. type=100 srid=0 view=yes pk= sql=
[4136] ..\\..\\..\\..\\..\\..\\src\\providers\\oracle\\qgsoracletablemodel.cpp(48) : (QgsOracleTableModel::addTableEntry) [0ms] View without pk skipped.

I understand that QGis doesn't find primary key but how can you declare a primary key in a VIEW ?

The first field: "ID_PAE" is the primary key of table "PAE" and I thought it would be seen as the primary key in the view....

Digging into Oracle documentation, I've found that you can add primary key constraint on a view using DISABLE NOVALIDATE options on the constraint.

I've rewritten my view definition like this:

CREATE OR REPLACE VIEW "DDEV1"."PAE_LST" (
  "ID_PAE",
  "TICO",
  CONSTRAINT "TD_PK" PRIMARY KEY ("ID_PAE") DISABLE NOVALIDATE
) AS
SELECT "ID_PAE", "TICO" FROM "PAE";

But I've got the same problem... I've also tried with other non-geographic views from non-geographic tables and got the same symptoms.

When I just add the geometry column, QGis is able to list the view, even if there is no primary key on the constraint:

CREATE OR REPLACE VIEW "DDEV1"."PAE_LST" (
  "ID_PAE",
  "TICO",
  "GEOM" 
) AS
SELECT "ID_PAE", "TICO", "GEOM" FROM "PAE";

The above view is visible (and can be opened) on the layer list of the Oracle Provider.

Here is what the debugger is able to see with the geographic view:

[4136] ..\\..\\..\\..\\..\\..\\src\\providers\\oracle\\qgsoracletablemodel.cpp(44) : (QgsOracleTableModel::addTableEntry) [0ms] DDEV1.PAE_LST.GEOM type=3|6|0 srid=0|0|0 view=yes pk=ID_PAE|TICO|GEOM sql=
[4136] ..\\..\\..\\..\\..\\..\\src\\providers\\oracle\\qgsoraclesourceselect.cpp(433) : (QgsOracleSourceSelect::setLayerType) [3ms] entering.
[4136] ..\\..\\..\\..\\..\\..\\src\\providers\\oracle\\qgsoracletablemodel.cpp(44) : (QgsOracleTableModel::addTableEntry) [0ms] DDEV1.PAE_LST. type=100 srid=0 view=yes pk= sql=
[4136] ..\\..\\..\\..\\..\\..\\src\\providers\\oracle\\qgsoracletablemodel.cpp(48) : (QgsOracleTableModel::addTableEntry) [0ms] View without pk skipped.

I think this is a bug... But perhaps there is a better way to declare non-geographic views ?

Oracle is a bit special in this detail (too) and you really can create a primary key for a view. Do it this way for an existing view:

alter view your_view add constraint your_view_pk
primary key(attr_name) disable novalidate;

Hello,

thank you very much Jürgen !