3

Oracle connections are working OK in the Browser panel, the Data Source Manager, and the Python console, but any attempt to open a connection in the DB Manager is failing.
The database is a fresh 12.1.0.2 database with just a few Spatial layers in it. The Oracle client is 64-bit.

I've also tried QGIS 2.18, another PC with a fresh install of both the 64-bit Oracle client and QGIS 3.0 (standalone installer), and connecting to the main 11.2.0.4 ArcSDE database, with the same or very similar results.

Does anyone have any idea what I've not done or am doing wrong? Is there something in the setup I should have set? (pretty much all I've done is set the default CRS to EPSG:27700)

In the Browser panel, I set up a new connection by right-click:
Name: TEST
Database: GMAPQGIS (Oracle Service name & SID)
Host: svrshir123
Port: 1521
Username: AJS
Password: ********
Save Username: ticked
Save Password: ticked
Only look in meta data tables: ticked

The connection tests OK, and shows the expected usernames & tables when expanded in the browser. It also opens OK in the Data Source Manager (though fairly slowly).

In the Python console, the same connection parameters work OK, giving "return value = 1.0"

from qgis.PyQt.QtSql import QSqlDatabase, QSqlQuery
db = QSqlDatabase.addDatabase("QOCISPATIAL")
HOST = 'svrshir123'
DATABASE = 'GMAPQGIS'
PORT = '1521'
db.setDatabaseName(HOST + ':' + str(PORT) + '/' + DATABASE)
db.setUserName('AJS')
db.setPassword('*******')
if not db.open():
print("DB open FAILED")
q = QSqlQuery(db)
q.exec_("SELECT count(*) FROM v$option WHERE parameter = 'Spatial' AND value = 'TRUE'")
q.first()
print("return value =",str(q.value(0)))

However, in DB Manager I just get "Not connected" and a "Python error" header bar when attempting to open the connection (there seems no way of creating a new connection within DB Manager).
The Stack trace shows the following:

An error has occurred while executing Python code:
TypeError: 'QSqlQuery' object is not an iterator
Traceback (most recent call last): File "C:/Program Files/QGIS3~1.0/apps/qgis/./python/plugins\db_manager\db_model.py", line 441, in rowCount
self._refreshIndex(parent, True)
File "C:/Program Files/QGIS3~1.0/apps/qgis/./python/plugins\db_manager\db_model.py", line 490, in _refreshIndex
if item.populate():
File "C:/Program Files/QGIS3~1.0/apps/qgis/./python/plugins\db_manager\db_model.py", line 167, in populate
if not connection.connect():
File "C:/Program Files/QGIS3~1.0/apps/qgis/./python/plugins\db_manager\db_plugins\oracle\plugin.py", line 121, in connect
return self.connectToUri(uri)
File "C:/Program Files/QGIS3~1.0/apps/qgis/./python/plugins\db_manager\db_plugins\oracle\plugin.py", line 73, in connectToUri
self.db = self.databasesFactory(self, uri)
File "C:/Program Files/QGIS3~1.0/apps/qgis/./python/plugins\db_manager\db_plugins\oracle\plugin.py", line 79, in databasesFactory
return ORDatabase(connection, uri)
File "C:/Program Files/QGIS3~1.0/apps/qgis/./python/plugins\db_manager\db_plugins\oracle\plugin.py", line 156, in init
Database.init(self, connection, uri)
File "C:/Program Files/QGIS3~1.0/apps/qgis/./python/plugins\db_manager\db_plugins\plugin.py", line 218, in init
self.connector = self.connectorsFactory(uri)
File "C:/Program Files/QGIS3~1.0/apps/qgis/./python/plugins\db_manager\db_plugins\oracle\plugin.py", line 159, in connectorsFactory
return OracleDBConnector(uri, self.connName)
File "C:/Program Files/QGIS3~1.0/apps/qgis/./python/plugins\db_manager\db_plugins\oracle\connector.py", line 127, in init
self._checkSpatial()
File "C:/Program Files/QGIS3~1.0/apps/qgis/./python/plugins\db_manager\db_plugins\oracle\connector.py", line 138, in _checkSpatial
self.has_spatial = self._fetchone(c)[0] > 0
File "C:/Program Files/QGIS3~1.0/apps/qgis/./python/plugins\db_manager\db_plugins\connector.py", line 142, in _fetchone
return c.fetchone()
File "C:/Program Files/QGIS3~1.0/apps/qgis/./python/plugins\db_manager\db_plugins\oracle\QtSqlDB.py", line 147, in fetchone
if not next(self.qry):
TypeError: 'QSqlQuery' object is not an iterator

Looking at the code, it seems to be failing on the same query I tested above in the Python console.

  • Has this ever worked and stopped working ? Or has it never worked at all ? – Albert Godfrind Apr 11 '18 at 8:19
  • I mean in the DB Manager ? – Albert Godfrind Apr 11 '18 at 8:28
  • It's a new QGIS install as a try out. We've been using ArcMap, but the licence costs are biting. – Andrew_S Apr 11 '18 at 9:56
  • It turns out that this is Bug report #14026, from Dec 2015, and still unresolved. I think I'll use ogr2ogr command line instead, or possibly switch the database to PostGIS – Andrew_S Jun 25 '18 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.