DBD::Oracle 1.19 fails to find Oracle version, problem and solution

Hi,

Just attempted to install DBD::Oracle 1.19 on an Ubuntu Server 7.04
Feisty machine running Perl v5.8.8 (and instant client
10.2.0.3-20061115) and hit a problem with the Makefile.PL not being able
to determine my Oracle version. The symptom is:

==========
WARNING: Setting ORACLE_HOME env var to /home/XXX/instantclient_10_2/
for you.
WARNING: The tests will probably fail unless you set ORACLE_HOME yourself!
Using Oracle in /home/XXX/instantclient_10_2/
Error 6 initializing SQL*Plus
Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory



I'm having trouble finding your Oracle version number... trying harder

WARNING: I could not determine Oracle client version so I'll just
default to version 8.0.0.0. Some features of DBD::Oracle may not work.
Oracle version based logic in Makefile.PL may produce erroneous results.
You can use "perl Makefile.PL -V X.Y.Z" to specify a your client version.

Oracle version 8.0.0.0 (8.0)
Looks like an Instant Client installation, okay
==========

The real problem is that if you set ORACLE_HOME (which Makefile.PL does)
sqlplus does not work e.g.,

# echo $ORACLE_HOME
# ./instantclient_10_2/sqlplus -S /nolog \@/tmp/x.sql 2>&1
DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)
# ORACLE_HOME=/home/XXX/instantclient_10_2/ ./instantclient_10_2/sqlplus
-S /nolog \@/tmp/x.sql 2>&1
Error 6 initializing SQL*Plus
Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Contrary to the SP2-0750 error and description, Oracle's advice (on
their web page) for using InstantClient is NOT to set ORACLE_HOME but to
set LD_LIBRARY_PATH (or configure the dynamic linker using ldconfig
etc). I can't remember now exactly what the issue was but I've had other
problems using instant client with ORACLE_HOME set before. I don't think
DBD::Oracle's Makefile.PL should set ORACLE_HOME if it detects instant
client however, it is so ingrained in the Makefile.PL it might
compromise some other installation path to remove it. As a workaround (I
know not pretty) I changed Makefile.PL in get_client_version() around
line 1473 as follows:

open FH, ">define.sql" or warn "Can't create define.sql: $!";
print FH "DEFINE _SQLPLUS_RELEASE\nQUIT\n";
close FH;
my $sqlplus_release = `$sqlplus_exe -S /nolog \@define.sql 2>&1`;
# +MJE
if ($sqlplus_release =~ /SP2-0750/) {
my $x = $ENV{ORACLE_HOME};
delete $ENV{ORACLE_HOME};
$sqlplus_release = `$sqlplus_exe -S /nolog \@define.sql 2>&1`;
$ENV{ORACLE_HOME} = $x;
}
# -MJE
unlink "define.sql";

Originally, I just deleted ORACLE_HOME but it is used all over the place
later and led to use of uninitialised variable warnings.

Once change made the outcome is:

==========
WARNING: Setting ORACLE_HOME env var to /home/XXX/instantclient_10_2/
for you.
WARNING: The tests will probably fail unless you set ORACLE_HOME yourself!
Using Oracle in /home/XXX/instantclient_10_2/
DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)
Oracle version 10.2.0.3 (10.2)
Looks like an Instant Client installation, okay
Your LD_LIBRARY_PATH env var is set to '/home/XXX/instantclient_10_2/'
==========

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com


--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: DBD::Oracle 1.19 fails to find Oracle version, problem and solution

Yes it is a rather delicate subject as if we make it work for one it
will break for everyone else.

The real root of the problem is that Oracle in its divine wisdom has
compacted all the .so files into one so there is no way you c compiler
can get them but of SQL on the Ubuntu Server it could be the case that
the Makefile fort 1.19 is not recognizing it as a Linux server or alike.
We could try to debug it but I would need access to your server for an
hour or so or if you have the time we can debug it together as I can
send you a Makefile.PL with a little more debugging into? I have time
tomorrow if you are interested Martin

Martin J. Evans wrote:
> Hi,
>
> Just attempted to install DBD::Oracle 1.19 on an Ubuntu Server 7.04
> Feisty machine running Perl v5.8.8 (and instant client
> 10.2.0.3-20061115) and hit a problem with the Makefile.PL not being
> able to determine my Oracle version. The symptom is:
>
> ==========
> WARNING: Setting ORACLE_HOME env var to /home/XXX/instantclient_10_2/
> for you.
> WARNING: The tests will probably fail unless you set ORACLE_HOME
> yourself!
> Using Oracle in /home/XXX/instantclient_10_2/
> Error 6 initializing SQL*Plus
> Message file sp1.msb not found
> SP2-0750: You may need to set ORACLE_HOME to your Oracle software
> directory
>
>
>
> I'm having trouble finding your Oracle version number... trying harder
>
> WARNING: I could not determine Oracle client version so I'll just
> default to version 8.0.0.0. Some features of DBD::Oracle may not work.
> Oracle version based logic in Makefile.PL may produce erroneous results.
> You can use "perl Makefile.PL -V X.Y.Z" to specify a your client version.
>
> Oracle version 8.0.0.0 (8.0)
> Looks like an Instant Client installation, okay
> ==========
>
> The real problem is that if you set ORACLE_HOME (which Makefile.PL
> does) sqlplus does not work e.g.,
>
> # echo $ORACLE_HOME
> # ./instantclient_10_2/sqlplus -S /nolog \@/tmp/x.sql 2>&1
> DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)
> # ORACLE_HOME=/home/XXX/instantclient_10_2/
> ./instantclient_10_2/sqlplus -S /nolog \@/tmp/x.sql 2>&1
> Error 6 initializing SQL*Plus
> Message file sp1.msb not found
> SP2-0750: You may need to set ORACLE_HOME to your Oracle software
> directory
>
> Contrary to the SP2-0750 error and description, Oracle's advice (on
> their web page) for using InstantClient is NOT to set ORACLE_HOME but
> to set LD_LIBRARY_PATH (or configure the dynamic linker using ldconfig
> etc). I can't remember now exactly what the issue was but I've had
> other problems using instant client with ORACLE_HOME set before. I
> don't think DBD::Oracle's Makefile.PL should set ORACLE_HOME if it
> detects instant client however, it is so ingrained in the Makefile.PL
> it might compromise some other installation path to remove it. As a
> workaround (I know not pretty) I changed Makefile.PL in
> get_client_version() around line 1473 as follows:
>
> open FH, ">define.sql" or warn "Can't create define.sql: $!";
> print FH "DEFINE _SQLPLUS_RELEASE\nQUIT\n";
> close FH;
> my $sqlplus_release = `$sqlplus_exe -S /nolog \@define.sql 2>&1`;
> # +MJE
> if ($sqlplus_release =~ /SP2-0750/) {
> my $x = $ENV{ORACLE_HOME};
> delete $ENV{ORACLE_HOME};
> $sqlplus_release = `$sqlplus_exe -S /nolog \@define.sql 2>&1`;
> $ENV{ORACLE_HOME} = $x;
> }
> # -MJE
> unlink "define.sql";
>
> Originally, I just deleted ORACLE_HOME but it is used all over the
> place later and led to use of uninitialised variable warnings.
>
> Once change made the outcome is:
>
> ==========
> WARNING: Setting ORACLE_HOME env var to /home/XXX/instantclient_10_2/
> for you.
> WARNING: The tests will probably fail unless you set ORACLE_HOME
> yourself!
> Using Oracle in /home/XXX/instantclient_10_2/
> DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)
> Oracle version 10.2.0.3 (10.2)
> Looks like an Instant Client installation, okay
> Your LD_LIBRARY_PATH env var is set to '/home/XXX/instantclient_10_2/'
> ==========
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>
>

Re: DBD::Oracle 1.19 fails to find Oracle version, problem and solution

John Scoles wrote:
> Yes it is a rather delicate subject as if we make it work for one it
> will break for everyone else.

Don't agree in this case since a) the code is already not working and b)
the change only comes into play if sqlplus fails with SP2-0750.

> The real root of the problem is that Oracle in its divine wisdom has
> compacted all the .so files into one so there is no way you c compiler
> can get them but of SQL on the Ubuntu Server it could be the case that
> the Makefile fort 1.19 is not recognizing it as a Linux server or alike.

> We could try to debug it but I would need access to your server for an
> hour or so or if you have the time we can debug it together as I can
> send you a Makefile.PL with a little more debugging into? I have time
> tomorrow if you are interested Martin
>

I am happy to anything to help now, tomorrow or whenever. However, I
don't understand your response since the problem I am seeing is that
sqlplus will NOT run if ORACLE_HOME is defined and it does not seem
related to combining libraries into one. The actual code failing
(omitting comments) is:

sub get_client_version {
my ($force_version) = @_;
my $client_version_full = '';
my $sqlplus_exe = ($os eq 'Win32' || $os eq 'MSWin32' || $os eq
'cygwin') ? "sqlplus.exe" : "sqlplus";
my $OH_path = $OH;
chomp($OH_path = `/usr/bin/cygpath -u $OH_path`) if $os eq 'cygwin'
&& $OH;
local $ENV{PATH} = join $Config{path_sep}, "$OH_path/bin",
$OH_path, $ENV{PATH} if $OH;
print "PATH=$ENV{PATH}\n" if $::opt_v;

if (find_bin($sqlplus_exe)) {

# code gets here since find_bin finds sqlplus on my path

local $ENV{SQLPATH} = "";
open FH, ">define.sql" or warn "Can't create define.sql: $!";
print FH "DEFINE _SQLPLUS_RELEASE\nQUIT\n";
my $sqlplus_release = `$sqlplus_exe -S /nolog \@define.sql 2>&1`;
unlink "define.sql";
print $sqlplus_release; # the _SQLPLUS_RELEASE may not be on
first line:
if ($sqlplus_release =~ /DEFINE _SQLPLUS_RELEASE =
"(\d?\d)(\d\d)(\d\d)(\d\d)(\d\d)"/) {

# code does not get here since sqlplus returned an error
# instead of the version. It returns SP2-0750 because
# when it sees ORACLE_HOME is defined it expects to find
# message files which are not present with instant client
$client_version_full = sprintf("%d.%d.%d.%d", $1, $2, $3, $4);
}

My change (although ugly) should not break anyone else because all it
does is re-run sqlplus with ORACLE_HOME temporarily deleted from
environment IF sqlplus returns the error SP2-0750. i.e., I insert into
above before "unlink":

if ($sqlplus_release =~ /SP2-0750/) {
my $x = $ENV{ORACLE_HOME};
delete $ENV{ORACLE_HOME};
$sqlplus_release = `$sqlplus_exe -S /nolog \@define.sql
2>&1`;
$ENV{ORACLE_HOME} = $x;
}

After this minor addition it works fine.

If you still want me to run anything on my machine let me know. I cannot
provide access to it from outside our network but I can run anything you
like.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

> Martin J. Evans wrote:
>> Hi,
>>
>> Just attempted to install DBD::Oracle 1.19 on an Ubuntu Server 7.04
>> Feisty machine running Perl v5.8.8 (and instant client
>> 10.2.0.3-20061115) and hit a problem with the Makefile.PL not being
>> able to determine my Oracle version. The symptom is:
>>
>> ==========
>> WARNING: Setting ORACLE_HOME env var to /home/XXX/instantclient_10_2/
>> for you.
>> WARNING: The tests will probably fail unless you set ORACLE_HOME
>> yourself!
>> Using Oracle in /home/XXX/instantclient_10_2/
>> Error 6 initializing SQL*Plus
>> Message file sp1.msb not found
>> SP2-0750: You may need to set ORACLE_HOME to your Oracle software
>> directory
>>
>>
>>
>> I'm having trouble finding your Oracle version number... trying harder
>>
>> WARNING: I could not determine Oracle client version so I'll just
>> default to version 8.0.0.0. Some features of DBD::Oracle may not work.
>> Oracle version based logic in Makefile.PL may produce erroneous results.
>> You can use "perl Makefile.PL -V X.Y.Z" to specify a your client version.
>>
>> Oracle version 8.0.0.0 (8.0)
>> Looks like an Instant Client installation, okay
>> ==========
>>
>> The real problem is that if you set ORACLE_HOME (which Makefile.PL
>> does) sqlplus does not work e.g.,
>>
>> # echo $ORACLE_HOME
>> # ./instantclient_10_2/sqlplus -S /nolog \@/tmp/x.sql 2>&1
>> DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)
>> # ORACLE_HOME=/home/XXX/instantclient_10_2/
>> ./instantclient_10_2/sqlplus -S /nolog \@/tmp/x.sql 2>&1
>> Error 6 initializing SQL*Plus
>> Message file sp1.msb not found
>> SP2-0750: You may need to set ORACLE_HOME to your Oracle software
>> directory
>>
>> Contrary to the SP2-0750 error and description, Oracle's advice (on
>> their web page) for using InstantClient is NOT to set ORACLE_HOME but
>> to set LD_LIBRARY_PATH (or configure the dynamic linker using ldconfig
>> etc). I can't remember now exactly what the issue was but I've had
>> other problems using instant client with ORACLE_HOME set before. I
>> don't think DBD::Oracle's Makefile.PL should set ORACLE_HOME if it
>> detects instant client however, it is so ingrained in the Makefile.PL
>> it might compromise some other installation path to remove it. As a
>> workaround (I know not pretty) I changed Makefile.PL in
>> get_client_version() around line 1473 as follows:
>>
>> open FH, ">define.sql" or warn "Can't create define.sql: $!";
>> print FH "DEFINE _SQLPLUS_RELEASE\nQUIT\n";
>> close FH;
>> my $sqlplus_release = `$sqlplus_exe -S /nolog \@define.sql 2>&1`;
>> # +MJE
>> if ($sqlplus_release =~ /SP2-0750/) {
>> my $x = $ENV{ORACLE_HOME};
>> delete $ENV{ORACLE_HOME};
>> $sqlplus_release = `$sqlplus_exe -S /nolog \@define.sql 2>&1`;
>> $ENV{ORACLE_HOME} = $x;
>> }
>> # -MJE
>> unlink "define.sql";
>>
>> Originally, I just deleted ORACLE_HOME but it is used all over the
>> place later and led to use of uninitialised variable warnings.
>>
>> Once change made the outcome is:
>>
>> ==========
>> WARNING: Setting ORACLE_HOME env var to /home/XXX/instantclient_10_2/
>> for you.
>> WARNING: The tests will probably fail unless you set ORACLE_HOME
>> yourself!
>> Using Oracle in /home/XXX/instantclient_10_2/
>> DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)
>> Oracle version 10.2.0.3 (10.2)
>> Looks like an Instant Client installation, okay
>> Your LD_LIBRARY_PATH env var is set to '/home/XXX/instantclient_10_2/'
>> ==========
>>
>> Martin
>> --
>> Martin J. Evans
>> Easysoft Limited
>> http://www.easysoft.com
>>
>>
>
>

Re: DBD::Oracle 1.19 fails to find Oracle version, problem and solution

Apologies to anyone attempting to answer me directly on the original
post, I accidentally broke my From address which should be as in this
posting.

Martin J. Evans wrote:
> Hi,
>
> Just attempted to install DBD::Oracle 1.19 on an Ubuntu Server 7.04
> Feisty machine running Perl v5.8.8 (and instant client
> 10.2.0.3-20061115) and hit a problem with the Makefile.PL not being able
> to determine my Oracle version. The symptom is:
>
> ==========
> WARNING: Setting ORACLE_HOME env var to /home/XXX/instantclient_10_2/
> for you.
> WARNING: The tests will probably fail unless you set ORACLE_HOME yourself!
> Using Oracle in /home/XXX/instantclient_10_2/
> Error 6 initializing SQL*Plus
> Message file sp1.msb not found
> SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
>
>
>
> I'm having trouble finding your Oracle version number... trying harder
>
> WARNING: I could not determine Oracle client version so I'll just
> default to version 8.0.0.0. Some features of DBD::Oracle may not work.
> Oracle version based logic in Makefile.PL may produce erroneous results.
> You can use "perl Makefile.PL -V X.Y.Z" to specify a your client version.
>
> Oracle version 8.0.0.0 (8.0)
> Looks like an Instant Client installation, okay
> ==========
>
> The real problem is that if you set ORACLE_HOME (which Makefile.PL does)
> sqlplus does not work e.g.,
>
> # echo $ORACLE_HOME
> # ./instantclient_10_2/sqlplus -S /nolog \@/tmp/x.sql 2>&1
> DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)
> # ORACLE_HOME=/home/XXX/instantclient_10_2/ ./instantclient_10_2/sqlplus
> -S /nolog \@/tmp/x.sql 2>&1
> Error 6 initializing SQL*Plus
> Message file sp1.msb not found
> SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
>
> Contrary to the SP2-0750 error and description, Oracle's advice (on
> their web page) for using InstantClient is NOT to set ORACLE_HOME but to
> set LD_LIBRARY_PATH (or configure the dynamic linker using ldconfig
> etc). I can't remember now exactly what the issue was but I've had other
> problems using instant client with ORACLE_HOME set before. I don't think
> DBD::Oracle's Makefile.PL should set ORACLE_HOME if it detects instant
> client however, it is so ingrained in the Makefile.PL it might
> compromise some other installation path to remove it. As a workaround (I
> know not pretty) I changed Makefile.PL in get_client_version() around
> line 1473 as follows:
>
> open FH, ">define.sql" or warn "Can't create define.sql: $!";
> print FH "DEFINE _SQLPLUS_RELEASE\nQUIT\n";
> close FH;
> my $sqlplus_release = `$sqlplus_exe -S /nolog \@define.sql 2>&1`;
> # +MJE
> if ($sqlplus_release =~ /SP2-0750/) {
> my $x = $ENV{ORACLE_HOME};
> delete $ENV{ORACLE_HOME};
> $sqlplus_release = `$sqlplus_exe -S /nolog \@define.sql 2>&1`;
> $ENV{ORACLE_HOME} = $x;
> }
> # -MJE
> unlink "define.sql";
>
> Originally, I just deleted ORACLE_HOME but it is used all over the place
> later and led to use of uninitialised variable warnings.
>
> Once change made the outcome is:
>
> ==========
> WARNING: Setting ORACLE_HOME env var to /home/XXX/instantclient_10_2/
> for you.
> WARNING: The tests will probably fail unless you set ORACLE_HOME yourself!
> Using Oracle in /home/XXX/instantclient_10_2/
> DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)
> Oracle version 10.2.0.3 (10.2)
> Looks like an Instant Client installation, okay
> Your LD_LIBRARY_PATH env var is set to '/home/XXX/instantclient_10_2/'
> ==========
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>
>

Re: DBD::Oracle 1.19 fails to find Oracle version, problem and solution

Ok Martin we can pick this up on the morrow. I will add a few debug
extra debug stuff about the Makefile and get you email me the results
directly.

Talk to you tomorrow

Cheers John Scoles

Martin Evans wrote:
> John Scoles wrote:
>> Yes it is a rather delicate subject as if we make it work for one it
>> will break for everyone else.
>
> Don't agree in this case since a) the code is already not working and
> b) the change only comes into play if sqlplus fails with SP2-0750.
>
>> The real root of the problem is that Oracle in its divine wisdom has
>> compacted all the .so files into one so there is no way you c
>> compiler can get them but of SQL on the Ubuntu Server it could be the
>> case that the Makefile fort 1.19 is not recognizing it as a Linux
>> server or alike.
>
>> We could try to debug it but I would need access to your server for
>> an hour or so or if you have the time we can debug it together as I
>> can send you a Makefile.PL with a little more debugging into? I have
>> time tomorrow if you are interested Martin
>>
>
> I am happy to anything to help now, tomorrow or whenever. However, I
> don't understand your response since the problem I am seeing is that
> sqlplus will NOT run if ORACLE_HOME is defined and it does not seem
> related to combining libraries into one. The actual code failing
> (omitting comments) is:
>
> sub get_client_version {
> my ($force_version) = @_;
> my $client_version_full = '';
> my $sqlplus_exe = ($os eq 'Win32' || $os eq 'MSWin32' || $os eq
> 'cygwin') ? "sqlplus.exe" : "sqlplus";
> my $OH_path = $OH;
> chomp($OH_path = `/usr/bin/cygpath -u $OH_path`) if $os eq
> 'cygwin' && $OH;
> local $ENV{PATH} = join $Config{path_sep}, "$OH_path/bin",
> $OH_path, $ENV{PATH} if $OH;
> print "PATH=$ENV{PATH}\n" if $::opt_v;
>
> if (find_bin($sqlplus_exe)) {
>
> # code gets here since find_bin finds sqlplus on my path
>
> local $ENV{SQLPATH} = "";
> open FH, ">define.sql" or warn "Can't create define.sql: $!";
> print FH "DEFINE _SQLPLUS_RELEASE\nQUIT\n";
> my $sqlplus_release = `$sqlplus_exe -S /nolog \@define.sql 2>&1`;
> unlink "define.sql";
> print $sqlplus_release; # the _SQLPLUS_RELEASE may not be on
> first line:
> if ($sqlplus_release =~ /DEFINE _SQLPLUS_RELEASE =
> "(\d?\d)(\d\d)(\d\d)(\d\d)(\d\d)"/) {
>
> # code does not get here since sqlplus returned an error
> # instead of the version. It returns SP2-0750 because
> # when it sees ORACLE_HOME is defined it expects to find
> # message files which are not present with instant client
> $client_version_full = sprintf("%d.%d.%d.%d", $1, $2, $3,
> $4);
> }
>
> My change (although ugly) should not break anyone else because all it
> does is re-run sqlplus with ORACLE_HOME temporarily deleted from
> environment IF sqlplus returns the error SP2-0750. i.e., I insert into
> above before "unlink":
>
> if ($sqlplus_release =~ /SP2-0750/) {
> my $x = $ENV{ORACLE_HOME};
> delete $ENV{ORACLE_HOME};
> $sqlplus_release = `$sqlplus_exe -S /nolog
> \@define.sql 2>&1`;
> $ENV{ORACLE_HOME} = $x;
> }
>
> After this minor addition it works fine.
>
> If you still want me to run anything on my machine let me know. I
> cannot provide access to it from outside our network but I can run
> anything you like.
>
> Martin

Re: DBD::Oracle 1.19 fails to find Oracle version, problem and solution

On Thu, Sep 27, 2007 at 11:17:54AM +0100, Martin J. Evans wrote:
>
> Contrary to the SP2-0750 error and description, Oracle's advice (on
> their web page) for using InstantClient is NOT to set ORACLE_HOME but to
> set LD_LIBRARY_PATH (or configure the dynamic linker using ldconfig
> etc). I can't remember now exactly what the issue was but I've had other
> problems using instant client with ORACLE_HOME set before. I don't think
> DBD::Oracle's Makefile.PL should set ORACLE_HOME if it detects instant
> client however, it is so ingrained in the Makefile.PL it might
> compromise some other installation path to remove it.

I think there's a good case for refactoring the Makefile.PL logic out
into something like Makefile_ic.PL and Makefile_full.PL and have
Makefile.PL delegate to Makefile_ic.PL if it detects an instant client
installation. Or something like that.

(Naturally common code should be moved out into private modules.)

Tim.