Oracle database connection lists table multiple times

[mirmalis]

What is the bug or the crash?

Inside oracle database I have schema "QGIS" with 1 view "TYRIMAI" and 1 table "LAYER_STYLES".

In QGIS inside "Browser" window
oracle connection configured with "Schema" value "QGIS" lists "TYRIMAI" multiple times

Inside Debugging Tool i see SQL that gets multiplied data is:

SELECT c.owner,c.table_name,c.column_name,NULL AS srid,o.object_type AS type FROM all_tab_columns c JOIN all_objects o ON c.table_name=o.object_name AND o.object_type IN ('TABLE','VIEW','SYNONYM') AND c.owner='QGIS' WHERE c.data_type='SDO_GEOMETRY'

OWNER	TABLE_NAME	COLUMN_NAME	SRID	TYPE
QGIS	TYRIMAI	OBJ		TABLE
QGIS	TYRIMAI	OBJ		SYNONYM
QGIS	TYRIMAI	OBJ		SYNONYM
QGIS	TYRIMAI	OBJ		VIEW

From what I understand the problem happens because other schemas has object with same name "TYRIMAI".

Inside this SQL changing c.owner='QGIS' to o.owner='QGIS' fixes the problem, also joining on owner and filtering c.owner='QGIS' fixes the problem.

here is data of joint tables seperately
select o.OWNER, o.OBJECT_NAME, o.object_type from all_objects o where object_name='TYRIMAI'

OWNER	OBJECT_NAME	OBJECT_TYPE
PUBLIC	TYRIMAI	SYNONYM
<other_schema1>	TYRIMAI	TABLE
<other_schema2>	TYRIMAI	SYNONYM
QGIS	TYRIMAI	VIEW

select c.owner,c.table_name,c.column_name FROM all_tab_columns c WHERE c.data_type='SDO_GEOMETRY' AND OWNER = 'QGIS'

OWNER	TABLE_NAME	COLUMN_NAME
QGIS	TYRIMAI	OBJ

Steps to reproduce the issue

Configure oracle connection with Schema value provided
Open said schema inside "Browser"

Versions

Qgis 3.28.15-Firenze

Supported QGIS version

• I'm running a supported QGIS version according to the roadmap.

New profile

• I tried with a new QGIS profile

Additional context

No response

[Djedouas]

Hi,

I think that this is not an issue.

Generally each geometry table appears multiple times, because with QGIS and Oracle DB you can load the layer with the geometry type that is more adapted to your data. QGIS suggests 4 geometry types, for instance here:

• MultiCurve
• MultiCurveZ
• CompoundCurve
• CompoundCurveZ

which you can see in the tooltip by hovering the mouse over the layer in the browser:

It's related to the SQL command done just after the one you mentionned which is:

SELECT DISTINCT t."GEOM".SDO_GTYPE,t."GEOM".SDO_SRID FROM (SELECT "GEOM" FROM "OUTLN"."LINE_DATA" WHERE "GEOM" IS NOT NULL AND rownum<=100) t WHERE NOT t."GEOM" IS NULL;

GEOM.SDO_GTYPE GEOM.SDO_SRID
-------------- -------------
	  2006		3857
	  3006		3857
	  2002		3857
	  3002		3857

and gives 4 SDO_GTYPE.

Could you please verify on your dataset?

Thanks, regards.

[github-actions]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale".
If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue.
In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue.
If there is no further activity on this issue, it will be closed in a week.

[github-actions]

While we hate to see this happen, this issue has been automatically closed because it has not had any activity in the last 42 days despite being marked as feedback. If this issue should be reconsidered, please follow the guidelines in the previous comment and reopen this issue.
Or, if you have any further questions, there are also further support channels that can help you.