How to connect MapInfo Professional to Oracle database?

[Ammar]

Hello everyone,

I am relatively new to MapInfo and GIS in general. I have a project which is to connect Mapinfo Professional to Oracle database server and I have a couple of questions regarding that. My setup is:

Oracle 11g R2 on Oracle Enterprise Linux 5 - 32 bits (Everything is running on VMware Player)

MapInfo Professional 11.5 running on Windows 7 - 64 bits (VMware Player)

Oracle Instant Client + ODBC drivers (both 32 bits and 64 bits) on the machine that runs MapInfo and set the ORACLE_HOME variable to the InstantClient 32 folder and the TNS_ADMIN to the folder that conatins the TNSNAME.ORA and configured the TNSNAME.ORA to points to the Oracle server.

Now, when I try to connect Mapinfo to Oracle using the Open DBMS Connection I got two choices only: ODBC and FDO. There is NO choice for Oracle here. After making all the selection, I get the Oracle ODBC Driver Connect with three fields: Service Name, Username and Password. I am assuming the service name is the same once that I put in my TNSNAMES.ORA and the username and password are for the Oracle Server's user (in my case SYS). After entering that info, I get the following error:

A connection could not be made using the file data source parameters entered.

Questions:

- Do I need to install Oracle Spatial too to my Oracle 11g R2 server?

- Is the above mentioned service name the one configured in TNSNAMES.ORA?

- Do I need to set the variables path for Oracle Instant Client 32bits or 64 bits?

- Any tips or suggestions for that connection to happen?

Thank you in advance

//Ammar

[Peter Horsbøll Møller]

Ammar,

I would try to uninstall the two Oracle clients and then only install the 32 bit Oracle client.

MapInfo Pro doesn't detect that you have an Oracle client installed. It might be caused by the 64 bit client being installed as well.

You should when you use Open DBMS Connection see a third option. I think it's called ORAINET, but I'm not completely sure. It's been a while since I played with Oracle and MapInfo Pro.

Connecting to Oracle using ODBC will only let you access the non-spatial part of the data. MapInfo doesn't support Oracle Locator/Oracle Spatial thru ODBC - not sure if any applications do.

Peter Horsbøll Møller

Pitney Bowes Software

---

Date: Tue, 23 Apr 2013 07:38:48 -0700
From: shaarba...@gmail.com
To: mapi...@googlegroups.com
Subject: [MI-L] How to connect MapInfo Professional to Oracle database?

--
--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en
 
---
You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Eric Blasenheim]

I have successfully run with both 64 and 32  bit clients "installed". There really is no installer.  You just unzip the files somewhere.  I think the problem may be the lack of the 32-bit location for Oracle being on your system path. If they are not on the path, Windows will not be able to find the OCI.DLL which is the starting point for everything. Add that DLL location to the system path.  The OCI will show up as a DBMS connection.  It works for me.

 

Eric Blasenheim

Pitney Bowes Software

[Ammar]

Thank you very much for your reply. 

I have installed both clients and re-installed the 32-bits client only but the same thing happened. Any ideas? 

[Ammar]

Eric,

Thanks for your reply. I think I have set the path correctly. I have installed the client in the following directory c:\OracleInstallClient\32bit. 

Please check the attached screen shot that shows the variable PATHs that I have set and if you could please tell me if I did something wrong.

Thanks

//Ammar

[Eric Blasenheim]

I cannot tell if these are correct.  You have to add the location of OCI.DLL to your Windows system PATH variable in that Windows dialog that you sent picture of.  You do have an Oracle folder in the path but I don't know if it is the right one. My download created a folder called 11-32\instantclient_11_2. So see if OCI.DLL is in that folder . 

 

If you get that right and Oracle finds all its dlls,  then Professional will show OCI as an option. If you get the TNS_ADMIN wrong, the connection option to OCI will be there but you will not be able to successfully connect. So you can tell what is wrong.

 

ORACLE_HOME and TNS_ADMIN are probably not both necessary but they only help to find the TNSNAMES.ORA file so you only NEED TNS_ADMIN. Again, they have nothing to do with whether the connect option shows up.

Eric Blasenheim

Pitney Bowes Software 

On Tuesday, April 23, 2013 10:38:48 AM UTC-4, Ammar wrote:

[Ammar]

Eric. You are the man!

I thought that the ORACLE_HOME is the most important variable that I had to set. I didn't know that PATH should be set too. I have set PATH to point to OCI.DLL and now I have an Oracle Spatial option in the connection type! (See attached screen shot).

Thank you so much Eric.

[Ammar]

Ok, after being able to get the Oracle Spatial choice in MapInfo Professional, now I am facing a connection problem (obviously)! 

I have set the below TNSNAMES.ORA file on the client machine and the LISTENER.ORA file on the server:

#TNSNAMES.ORA

ocellus =
    (DESCRIPTION =
        (ADDRESS_LIST =     
        (ADDRESS = (PROTOCOL=TCP)(HOST = oracle.localdomain) (PORT = 1521))
        )
        (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAMe = ocellus)
        )    
        )

#LISTENER.ORA

  (DESCRIPTION_LIST =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.localdomain)(PORT = 1521))
)
  )

SID_LIST_LISTENER2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = OCELLUS)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0_dbhome_1)
    )
  )

The ORACLE_SID is OCELLUS.

The host name is oracle.localdomain

Pinging between the client/server works fine.

I am using the following in MapInfo:

USERNAME: sys

PASSWORD: password

SERVER NAME: oracle.localdomain

but I am getting the following error:

Oracle Error: ORA-12170: TNS: Connect timeout occurred

Tried to re-connect but this time got the following error:

Oracle Error: ORA-12504: TNS: listener was not given the SERVICE_NAME in CONNECT_DATA

I have turned off the firewall on both the client (Windows firewall)  the Server (IPtables) but got the same problem!

Any ideas?

Thanks in advance

On Thursday, April 25, 2013 12:08:12 AM UTC+2, Eric Blasenheim wrote:

[Eric Blasenheim]

I think you just use ocellus as it says in the tnsnames.ora. If you have the TNS_ADMIN set so that oracle code can find the file, you should be all set.

On Tuesday, April 23, 2013 10:38:48 AM UTC-4, Ammar wrote:

[Ammar]

I was able to connect to the Oracle server using the command line (sqlplus) but when tried to do the same in MapInfo I got the following error (see attached screen shot):

Oracle Error: ORA-12170: TNS:Connect timeout occurred

Any ideas?

Thanks in advance

[Ammar]

The problem is solved now!

I had to enter the following path in the Server Name of the MapInfo Oracle connect:

oracle.localdomain/OCELLUS

instead of writing the SERVICE_NAME only, which is OCELLUS.

Thank you all for help!

//Ammar

[Eric Blasenheim]

Ammar,

That is not usual at all. You must have some interesting setup in your TNSNAMES.ORA. 

Having used this with MapInfo Professional for maybe 15 years, I have never had to enter any of the prefix information.  So glad you are working but this is not a general solution.

 

Eric Blasenheim

Pitney Bowes Software

On Tuesday, April 23, 2013 10:38:48 AM UTC-4, Ammar wrote: