R-sig-DB Digest, Vol 71, Issue 3
F F Njrycnkb Jcucf F Kc Kvkt Ig Ikukt B Kk SvWebmaster Sent from my iPhone
On Sep 23, 2010, at 6:00 PM, r-sig-db-request at stat.math.ethz.ch wrote:
Send R-sig-DB mailing list submissions to r-sig-db at stat.math.ethz.ch To subscribe or unsubscribe via the World Wide Web, visit
https://stat.ethz.ch/mailman/listinfo/r-sig-db or, via email, send a message with subject or body 'help' to r-sig-db-request at stat.math.ethz.ch You can reach the person managing the list at r-sig-db-owner at stat.math.ethz.ch When replying, please edit your Subject line so it is more specific than "Re: Contents of R-sig-DB digest..." Today's Topics: 1. RODBC: Oracle Connectivity questions from Windows Box (matt.pettis at thomsonreuters.com) 2. Re: RODBC: Oracle Connectivity questions from Windows Box (matt.pettis at thomsonreuters.com) 3. Re: RODBC: Oracle Connectivity questions from Windows Box (Prof Brian Ripley) ---------------------------------------------------------------------- Message: 1 Date: Wed, 22 Sep 2010 21:46:13 -0500 From: <matt.pettis at thomsonreuters.com> To: <r-sig-db at stat.math.ethz.ch> Subject: [R-sig-DB] RODBC: Oracle Connectivity questions from Windows Box Message-ID: <4698336393F47347A088FB9F99FF1EBA13483D at TLRUSMNEAGMBX26.ERF.THOMSON.COM Content-Type: text/plain; charset="iso-8859-1" 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 ------------------------------ Message: 2 Date: Wed, 22 Sep 2010 22:37:22 -0500 From: <matt.pettis at thomsonreuters.com> To: <matt.pettis at thomsonreuters.com>, <r-sig-db at stat.math.ethz.ch> Subject: Re: [R-sig-DB] RODBC: Oracle Connectivity questions from Windows Box Message-ID: <4698336393F47347A088FB9F99FF1EBA13483E at TLRUSMNEAGMBX26.ERF.THOMSON.COM Content-Type: text/plain; charset="iso-8859-1" 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 at stat.math.ethz.ch on behalf of Pettis, Matthew (Legal) Sent: Wed 9/22/2010 9:46 PM To: r-sig-db at 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 at stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-db ------------------------------ Message: 3 Date: Thu, 23 Sep 2010 07:11:06 +0100 (BST) From: Prof Brian Ripley <ripley at stats.ox.ac.uk> To: matt.pettis at thomsonreuters.com Cc: r-sig-db at stat.math.ethz.ch Subject: Re: [R-sig-DB] RODBC: Oracle Connectivity questions from Windows Box Message-ID: <alpine.LFD.2.00.1009230710230.17794 at gannet.stats.ox.ac.uk> Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed 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 at 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 at stat.math.ethz.ch on behalf of Pettis, Matthew (Legal) Sent: Wed 9/22/2010 9:46 PM To: r-sig-db at 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 at 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 at stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-db -- Brian D. Ripley, ripley at 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 ------------------------------ _______________________________________________ R-sig-DB mailing list R-sig-DB at stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-db End of R-sig-DB Digest, Vol 71, Issue 3 ***************************************