[Qgis-user] Oracle - QGIS connection

[Jonathan Moules]

Hi All,
So, I finally figured it out by going back hooking up the old machine
again. Turns out it was partly user-error (nothing new there then :-) ) and
partly a documentation issue.

The following work:

Database: This needs to be the SERVICE_NAME (i.e. TNS_NAME). I was using
the SID. The help says the SID is valid, but it's not - I get the ORA-12514
if I enter a SID.

Host: This can be either the IP or DNS name. I was using the TNS_NAME. But
even when I tried the IP, it wasn't working because I was trying the SID as
above.

Similar to Bo's reply I just received while typing this.

But I do wonder - what is the point of a TNSnames if you have to manually
enter all of that same information in the first place?
With FME all I need to do is enter the SID and it magically works. I'll
open a feature request suggesting it be simplified.

Many Thanks for all the help folks!

Jonathan




On 25 September 2013 10:53, Duarte Carreira <DCarreira at edia.pt> wrote:

>  Does qgis use gdal to access oracle data?****
>
> ** **
>
> If it does you can try to use the osgeo4w console to try to connect with
> gdalinfo. See if that presents a clearer error. Maybe the oracle plugin is
> compiled against a different version of oracle…?****
>
> ** **
>
> If gdalinfo works, than you could create a .vrt that wraps the connection
> to your oracle spatial table. Try to open the .vrt in qgis… I have solved
> similar problems this way (dxf files wouldn’t load unless through a .vrt).
> ****
>
> ** **
>
> I have used process explorer before to find what dll is not being loaded
> or found… sometimes it worked sometimes it didn’t…****
>
> Just launch it and filter processes until you end up only with qgis
> related processes. You can find error messages like file not found and
> others.****
>
> ** **
>
> Duarte****
>
> ** **
>
> ** **
>
> *De:* Jonathan Moules [mailto:jonathanmoules at warwickshire.gov.uk]
> *Enviada:* quarta-feira, 25 de Setembro de 2013 10:33
> *Para:* Bob and Deb
> *Cc:* Duarte Carreira; qgis-user at lists.osgeo.org
>
> *Assunto:* Re: [Qgis-user] Oracle - QGIS connection****
>
> ** **
>
> Hi Bob,****
>
> Thanks for the reply. Unfortunately none of those things seems to have
> made a difference.****
>
> ** **
>
> I've now installed QGIS on a second machine, one that already has a
> working Oracle administrator install that various other applications are
> successfully using (i.e. FME). But QGIS on that machine has the exact same
> problem with the exact same failure mechanisms/errors.****
>
> ** **
>
> So the issue seems to be QGIS specific, but I don't know enough to be able
> to report it as a useful bug report.****
>
> ** **
>
> Cheers,****
>
> Jonathan****
>
> On 24 September 2013 18:14, Bob and Deb <bobdebm at gmail.com> wrote:****
>
> Hello Jonathan,****
>
> ** **
>
> How about setting the environment variable ORACLE_HOME to
> c:\admin\apps\oracle32\product\11.2.0\client_1 and TNS_ADMIN to the
> location of the directory that contains tnsnames.ora?  That is what I did
> and it works fine.  In the past I also had to rename the oci.dll (or you
> can remove it) from c:\osgeo4w\bin because it conflicted with the oci.dll
> in %ORACLE_HOME%.  Btw, we are connecting to Oracle 11.2.0 using OS
> authentication.  ****
>
> ** **
>
> Bob****
>
> ** **
>
> On Tue, Sep 24, 2013 at 9:48 AM, Jonathan Moules <
> jonathanmoules at warwickshire.gov.uk> wrote:****
>
>   Hi Duarte,****
>
> Cunning, I'd not played with the Python console before.****
>
> ** **
>
> The QGIS path is the same as the system one, though with extra stuff
> pre-pended - Oracle is in there just fine:****
>
> ** **
>
>
> 'c:\Admin\Apps\Progs\QGISDU~1\apps\grass\grass-6.4.3\bin;c:\Admin\Apps\Progs\QGISDU~1\apps\msys\bin;c:\Admin\Apps\Progs\QGISDU~1\bin;
> *c:\admin\apps\oracle32\product\11.2.0\client_1\bin*;C:\windows\system32;C:\windows;C:\windows\System32\Wbem;C:\windows\System32\WindowsPowerShell\v1.0\;C:\Program
> Files (x86)\Microsoft Application Virtualization Client;C:\Program Files
> (x86)\ActivIdentity\SecureLogin\;C:\Program
> Files\Intel\WiFi\bin\;C:\Program Files\Common
> Files\Intel\WirelessCommon\;c:\Admin\Apps\Progs\QGISDU~1\apps\msys\bin;c:\Admin\Apps\Progs\QGISDU~1\apps\Python27\Scripts;c:\Admin\Apps\Progs\QGISDU~1\apps\qgis\bin;c:\Admin\Apps\Progs\QGISDU~1\apps\grass\grass-6.4.3\lib'
> ****
>
>  ** **
>
> Trying TNS ping from within QGIS worked just fine too:****
>
> Attempting to contact (DESCRIPTION = (ADDRESS = (COMMUNITY = wcctcp.world)
> (PROTOCOL = TCP) (Host = co-gistest-li2) (Port = 1521)) (CONNECT_DATA =
> (SID = gistest) (GLOBAL_NAME = co_gistest.world)))
> OK (0 msec)****
>
>  ** **
>
> ----****
>
> ** **
>
> If I try and "cheat" and change my host from "co_gistest" to the actual
> machine name: "co-gistest-li2" - I get back an ORA-12514 instead where it
> says it doesn't know of the given host. But if I enter random gibberish it
> goes back to a ORA-12154.****
>
> ** **
>
> Very confusing (but it is Oracle).****
>
> ** **
>
> Jonathan****
>
> ** **
>
> ** **
>
> On 24 September 2013 17:28, Duarte Carreira <DCarreira at edia.pt> wrote:****
>
> Can you compare your path to what qgis is using?****
>
>  ****
>
> In python console type:****
>
> import os****
>
> os.environ["PATH"]****
>
>  ****
>
> It seems to me qgis is not finding your tns settings… your’re setting
> these up in tnsnames.ora?****
>
>  ****
>
> You can try tnsping from qgis and see what happens… try  print
> os.popen(“tnsping…”).read() in your python console…****
>
>  ****
>
> Duarte****
>
>  ****
>
> *De:* Jonathan Moules [mailto:jonathanmoules at warwickshire.gov.uk]
> *Enviada:* terça-feira, 24 de Setembro de 2013 17:16
> *Para:* Duarte Carreira
> *Cc:* qgis-user at lists.osgeo.org****
>
>
> *Assunto:* Re: [Qgis-user] Oracle - QGIS connection****
>
>  ****
>
> Hi Duarte,****
>
>  ****
>
> Yep it works fine:****
>
> sqlplus username/password at co_gistest****
>
>  ****
>
> Logs me straight in, and a trial select confirms I'm in.****
>
> It's just QGIS that is having issues.****
>
>  ****
>
> My PATH (this is windows) is:****
>
> c:\admin\apps\oracle32\product\11.2.0\client_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program
> Files (x86)\Microsoft Application Virtualization Client;C:\Program Files
> (x86)\ActivIdentity\SecureLogin\****
>
>  ****
>
> Am very perplexed!****
>
> Cheers,****
>
> Jonathan****
>
> On 24 September 2013 17:05, Duarte Carreira <DCarreira at edia.pt> wrote:****
>
> It could be environmental variables messed up when launching qgis… does
> sqlplus work?****
>
>  ****
>
> Duarte****
>
>  ****
>
> *De:* Jonathan Moules [mailto:jonathanmoules at warwickshire.gov.uk]
> *Enviada:* terça-feira, 24 de Setembro de 2013 16:00
> *Para:* qgis-user at lists.osgeo.org
> *Assunto:* Re: [Qgis-user] Oracle - QGIS connection****
>
>  ****
>
> Hi List,****
>
> I've done a lot more dabbling and it simply refuses to work. I've now got
> to the point where I've installed the Administrator install of the Oracle
> client. Both QGIS and the client are 32bit. But no matter what I keep
> getting the Ora-12154 error.****
>
>  ****
>
> If I try tnsping, it can resolve the name just fine, so I'm not clear why
> it is failing with QGIS.****
>
> I don't remember what I did previously to get it working but I'm sure it
> wasn't anything like this much trouble.****
>
>  ****
>
> Anyone?****
>
> Thanks,****
>
> Jonathan****
>
>  ****
>
>  ****
>
> On 23 September 2013 12:56, Jonathan Moules <
> jonathanmoules at warwickshire.gov.uk> wrote:****
>
> Hi List,****
>
> This is happening in 2.0.1 too. As other things can connect to the
> database from this machine I have to guess that it is a QGIS thing. I know
> it worked previously but doesn't seem to any more.****
>
>  ****
>
> Does it work for other folks? What are you entering into the parameters?**
> **
>
>  ****
>
> Thanks,****
>
> Jonathan****
>
>  ****
>
>  ****
>
> On 28 August 2013 14:33, Jonathan Moules <
> jonathanmoules at warwickshire.gov.uk> wrote:****
>
> Anyone?****
>
> I'm using QGIS weekly build d61cb25 on windows 7.****
>
>  ****
>
> Thanks,****
>
> Jonathan****
>
>  ****
>
>  ****
>
>
> This transmission is intended for the named addressee(s) only and may
> contain sensitive or protectively marked material up to RESTRICTED and
> should be handled accordingly. Unless you are the named addressee (or
> authorised to receive it for the addressee) you may not copy or use it, or
> disclose it to anyone else. If you have received this transmission in error
> please notify the sender immediately. All email traffic sent to or from us,
> including without limitation all GCSX traffic, may be subject to recording
> and/or monitoring in accordance with relevant legislation.****
>
>  ****
>
>
> This transmission is intended for the named addressee(s) only and may
> contain sensitive or protectively marked material up to RESTRICTED and
> should be handled accordingly. Unless you are the named addressee (or
> authorised to receive it for the addressee) you may not copy or use it, or
> disclose it to anyone else. If you have received this transmission in error
> please notify the sender immediately. All email traffic sent to or from us,
> including without limitation all GCSX traffic, may be subject to recording
> and/or monitoring in accordance with relevant legislation.****
>
> ** **
>
>
> This transmission is intended for the named addressee(s) only and may
> contain sensitive or protectively marked material up to RESTRICTED and
> should be handled accordingly. Unless you are the named addressee (or
> authorised to receive it for the addressee) you may not copy or use it, or
> disclose it to anyone else. If you have received this transmission in error
> please notify the sender immediately. All email traffic sent to or from us,
> including without limitation all GCSX traffic, may be subject to recording
> and/or monitoring in accordance with relevant legislation.****
>
> ** **
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user****
>
>  ** **
>
> ** **
>
>
> This transmission is intended for the named addressee(s) only and may
> contain sensitive or protectively marked material up to RESTRICTED and
> should be handled accordingly. Unless you are the named addressee (or
> authorised to receive it for the addressee) you may not copy or use it, or
> disclose it to anyone else. If you have received this transmission in error
> please notify the sender immediately. All email traffic sent to or from us,
> including without limitation all GCSX traffic, may be subject to recording
> and/or monitoring in accordance with relevant legislation.****
>

-- 
This transmission is intended for the named addressee(s) only and may 
contain sensitive or protectively marked material up to RESTRICTED and 
should be handled accordingly. Unless you are the named addressee (or 
authorised to receive it for the addressee) you may not copy or use it, or 
disclose it to anyone else. If you have received this transmission in error 
please notify the sender immediately. All email traffic sent to or from us, 
including without limitation all GCSX traffic, may be subject to recording 
and/or monitoring in accordance with relevant legislation.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20130925/001f4c68/attachment.html>