I changed {Microsoft ODBC for Oracle} to {Oracle in instantclient_11_1} as
specified in ODBC_IC_Readme_Win.html file. And I added sid=ORADB (my database
name). I'm reading the instruction in the ODBC_IC_Readme_Win.html file and here
are my question:
How do you set up the TNS_ADMIN? I don't seem to be able to find any file with
extension .ora in my ORACLE_HOME directory. Should I create one myself? Sorry
if this seems to be a dummy question. I'm really a starter at setting up this
whole thing.
* Define the TNS_ADMIN to point the .ora files directory for OCI.
* This needs to be set for OCI to resolve the TNS name.
* If TNS_ADMIN is not set, OCI will examine an operating system
dependent set of directories to find tnsnames.ora. This search path includes
looking in the directory %ORACLE_HOME%/network/admin/tnsnames.ora. This is the
only reason to set the ORACLE_HOME environment variable for Oracle Instant
Client. If ORACLE_HOME is set when running Instant Client applications, it must
be set to a directory where Instant Client exists (in this case
C:\Oracle\instantclient_11_1).
After I run the program with the changes, I got the following error:
DBI connect('driver={Oracle in
instantclient_11_1};server=10.1.6.9:1521;sid=ORADB;uid=oradbuser;pwd=oradbpassword;','',...)
failed: [Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified (SQL-IM002) at testOracle.pl line 31
Thanks a lot for your help.
Ming
----- Original Message ----
From: "pmonsch...@groupe-casino.fr" <pmonsch...@groupe-casino.fr>
To: my600...@yahoo.com; dbi-users@perl.org
Sent: Tuesday, July 28, 2009 9:21:02 AM
Subject: RE: Perl DBI and DBD::ODBC for Oracle on Windows
Hello
1) By adding sid=xxxx in the DSN, you should be able to point to the correct
SID.
So in your case :
my $DSN = 'driver={Microsoft ODBC for
Oracle};server=10.1.6.9:1521;sid=XXXXXX;uid=oradbuser;pwd=oradbpassword;';
2) You should not use "Microsoft ODBC for Oracle" that is a very old driver,
that perhaps works for Oracle 7 or 8.
If you already installed Oracle InstantClient SQLPLUS package, then I would
recommend you to download the components : Basic and ODBC and extract them in
the same folder as sqlplus.
Then follow instructions in README files and specially in
"ODBC_IC_Readme_Win.html" file so that you should be able to use the driver
provide by ODBC instantclient package. It should be something like "Oracle in
instantclient_xx_y"
Best Regards
Pascal
> -----Message d'origine-----
> De : Ming Yu [mailto:my600...@yahoo.com]
> Envoyé : mardi 28 juillet 2009 14:14
> À : dbi-users@perl.org
> Objet : Perl DBI and DBD::ODBC for Oracle on Windows
>
> Hi,
>
> I've been trying to connect to a remote Oracle database through Windows
> XP but couldn't make it work after a few days' effort. This is what
> I've done:
>
> 1. Install ActivePerl on Windows.
> 2. Install DBI module.
> 3. Install DBD::ODBC module.
> 4. Inside my perl code, I choose to connect without DSN:
>
> use DBI;
> use DBD::ODBC;
>
> my $database = "ORADB";
>
> my $DSN = 'driver={Microsoft ODBC for
> Oracle};server=10.1.6.9:1521;uid=oradbuser;pwd=oradbpassword;';
>
> my $dbh = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n";
>
> My first question: in the $DSN string, where can I put the database
> name? I found this syntax from the internet but it didn't say anything
> about the database name location.
>
> My second question: after I run the script, here is the error I got:
>
> DBI connect('driver={Microsoft ODBC for
> Oracle};server=10.1.6.9:1521;uid=oradbuser;pwd=oradbpassword;','',...)
> failed: [Microsoft][ODBC driver for Oracle][Oracle]
> ORA-12514: TNS:listener does not currently know of service requested in
> connect descriptor (SQL-NA000) [state was NA000 now 01000]
> [Microsoft][ODBC Driver Manager] The driver doesn't support the version
> of ODBC behavior that the application requested (see SQLSetEnvAttr).
> (SQL-01000) at testOracle.pl line 31
>
> The database connection works ok because I can connect to the datasbase
> using SQLPLUS that comes with Instance Client for Microsoft Windows 32.
>
> Thanks a lot!
>
> Ming
>
>
>
>
