[R-sig-DB] RODBC: Oracle Connectivity questions from Windows Box

[Prof Brian Ripley]

These are questions about the Oracle ODBC driver, nothing to do with R 
nor RODBC.  Please ask Oracle support.

On Wed, 22 Sep 2010, matt.pettis using thomsonreuters.com wrote:

> UPDATE:
>
> I was able to get rid of the gibberish and get a list of my TNS service names in tnsnames.ora by setting the env variable TNS_ADMIN.  I found this at: http://dbaspot.com/forums/oracle-server/85888-tns-service-name-selector-odbc-configuration-windows-2000-shows-trash-instant-client-10g.html.
>
> However, when I hit 'Test' for this on the 'Data Sources (ODBC)' Add... dialog page, it just hangs and never returns.  Same is true if I try to connect to it via RODBC in R.
>
> I do not have the env. variable ORACLE_HOME set.  If I do try to set it, and set it to instant client, it screws up my other applications that use a different Oracle client install on my machine to connect to the same databases with a native driver (apps like SAS and Toad), and it still hung when it was set.
>
> Any ideas?
>
> thanks,
> Matt
>
>
> -----Original Message-----
> From: r-sig-db-bounces using stat.math.ethz.ch on behalf of Pettis, Matthew (Legal)
> Sent: Wed 9/22/2010 9:46 PM
> To: r-sig-db using stat.math.ethz.ch
> Subject: [R-sig-DB] RODBC: Oracle Connectivity questions from Windows Box
>
> Hi,
>
> I am trying to set R up to connect to remote Oracle server (all >=10g) from a Windows XP box.  I want to use RODBC (unless someone has a persuasive reason to use ROracle, which I am totally unfamiliar with).  I have sucessfully connected to these servers before using native Oracle drivers via SAS and SQL*Plus tests from my box, as I have a correct tnsnames.ora file and user/passwords for this.  But I have never made an ODBC connection to Oracle (I have to SQL Server).  I need some help getting the connection set up.
>
> Questions:
>
> 1. Do I have to set up a connection in the 'Data Sources (ODBC)' from the Control panel, or can I bypass it and use Oracle drivers directly?  I'd rather not use the 'Data Sources (ODBC)', as using the sofware directly seems cleaner.
>
> 2. Failing that, I'm trying to set up a DSN in 'Data Sources (ODBC)'.  I downloaded the Oracle instant client and followed the directions here (http://szamtud-docs.uni-corvinus.hu/tananyagok/Alapkepzes/Portal/Archiv/4/oracle_ODBC/ODBC_IC_Readme_Win.html) to set it up.  It looked sucessful, and I was able to select it as a driver from 'Data Sources (ODBC)' Add button.  I got confused here when I tried to configure it.  The config window has the following 4 fields:
>
> Data Source Name
> Description
> TNS Service Name
> User ID
>
> This is where I get lost.  As I said, I have a tnsnames.ora file with connection strings in them, and I'm thinking I can make this wizard point to that file.  But none of the fields appears to be for a path to my tnsnames.ora file.
>
> Plus, I'm not sure if this matters, but my TNS Service Name field is a dropdown that has gibberish binary characters as options.  Is this an issue, or can I get by with out it?
>
> Can I get this wizard in 'Data Sources (ODBC)' to see and use my tnsnames.ora file?
>
> So given that I have a string in my tnsnames.ora file that looks like:
>
> MyDBIdentifier =
> (DESCRIPTION =
>    (ADDRESS_LIST =
>      (ADDRESS = (PROTOCOL = TCP)(HOST = phost1.com)(PORT = 1521))
>      (ADDRESS = (PROTOCOL = TCP)(HOST = phost2.com)(PORT = 1521))
>      (ADDRESS = (PROTOCOL = TCP)(HOST = phost3.com)(PORT = 1521))
>    )
>    (CONNECT_DATA =
>      (SERVICE_NAME = thedbvip.com)
>    )
>  )
>
> How can I configure either the 'Data Sources (ODBC)' to have the correct connection info or, better yet, get RODBC to interact directly with the Oracle client software, bypassing the 'Data Sources (ODBC)'?  To this point, if the instant clients aren't enough, I have downloaded (but not installed yet) ODAC112012.zip from http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html .
>
> Thanks,
> Matt
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>

-- 
Brian D. Ripley,                  ripley using stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595