R-sig-DB Digest, Vol 71, Issue 3

F
F
Njrycnkb
Jcucf
F
Kc
Kvkt
Ig
Ikukt
B
Kk
SvWebmaster

Sent from my iPhone

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
***************************************