2

Do you have any idea how load Oracle spatial data to QGIS with using the PyQGIS? Is it possible? I try:

uri = QgsDataSourceURI()
uri.setConnection("*host*", "1521", "*database*", "*username*", "*password*")       
sql = "(SELECT fid, geom FROM target.ms_ft_bh095_v)"
uri.setDataSource("",sql,"GEOM","","fid")
layer = QgsVectorLayer(uri.uri(), "test", "oracle")

The error message is: ORA-00972: identifier is not long. But FID max value is cca 20 000. Is Oracle driver correct with this syntax? Ms_ft_bh095_v is view in this moment.

1

The actual error message is "ORA-00972: identifier is too long". This has nothing to do with the maximum size or value of a column, but rather that an identifier such as a table name or column name is too long (more than 30 characters).

That said, I see nothing wrong with the SELECT statement you use - unless the interface you use adds something to make it invalid. Or your view definition contains something weird. Or your table definition has something weird.

1

The below code worked for me.

# Import system modules
from xml.dom.minidom import Document
import string
import os
import sys
from qgis.core import *
from qgis.gui import *
from PyQt4.QtCore import *
from PyQt4.QtGui import QApplication
from PyQt4.QtXml import *
from ctypes import*
import cx_Oracle

strProjetName = "C:/OSGeo4W/apache/htdocs/QGIS-Web-Client-master/projects/myworld.qgs"

if os.path.isfile(strProjetName):
   os.remove(strProjetName)

def add_LayersFromDB():
    QGISAPP = QgsApplication(sys.argv, True)     
    QgsApplication.setPrefixPath(r"C:\OSGeo4W\apps\qgis", True)
    QgsApplication.initQgis()

    #Connect to Oracle and Fetch Table Names 
    con = cx_Oracle.connect('testinstance/testinstance@189.60.67.146:1521/new')
    print con.version
    cur = con.cursor()
    cur.execute(u"select TABLE_NAME from user_tab_columns where data_type='SDO_GEOMETRY'")

    tables = cur.fetchall()

    QgsProject.instance().setFileName(strProjetName)
    print QgsProject.instance().fileName()
    render = QgsMapRenderer()

    uri = QgsDataSourceURI()
    uri.setConnection("189.60.67.146", "1521", "new", "testinstance", "testinstance")

    render = QgsMapRenderer()

    for t in tables :
       print str(t[0])

       uri.setDataSource('',  str(t[0]), "GEOMETRY")
       uri.uri()
       vlayer = QgsVectorLayer(uri.uri(),str(t[0]), 'oracle')
       QgsMapLayerRegistry.instance().addMapLayer(vlayer)
       lst = [ vlayer.id() ]
       render.setLayerSet(lst)
       rect = QgsRectangle(render.fullExtent())            
       rect.scale(1.1)
       render.setExtent(rect)

    QgsProject.instance().write()

    cur.close()
    con.close()

    QgsApplication.exitQgis()


add_LayersFromDB()

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.