DBD:Oracle
Hi, thanks for your advice - I installed ActivePerl-5.10.0.100 now, and the DBD::Oracle - package works fine now. Frank Atos Origin GmbH, Theodor-Althoff-Str. 47, D-45133 Essen, Postfach 100 123, D-45001 Essen Telefon: +49 201 4305 0, Fax: +49 201 4305 689095, www.atosorigin.de ING Bank AG, Frankfurt/Main: Konto 001 014 0937, BLZ 500 210 00, Swift / BIC INGBDEFF, IBAN DE74 5002 1000 0010 1409 37 Geschäftsführer: Wilbert Kieboom, Peter 't Jong, Handelsregister Essen HRB 19354, Ust.-ID.-Nr.: DE147861238
Re: DBD::Oracle - Problem with LOB's (regression ?)
You might be running into this bug
http://rt.cpan.org//Ticket/Display.html?id=36069
Which is fixed in 1.22 which will be release either today or tomorrow.
cheers John Scoles
Steve Baldwin wrote:
I've just noticed an apparent regression/change_of_behaviour that
affects me.
We do a good deal of our DB access accessing tables via private
synonyms. That is, we connect via schema_X which has a private synonym
(lob_table) to schema_Y.lob_table.
On our 'current' machines which have DBD::Oracle ver 1.15, everything
works fine.
On our 'new' machines which have DBD::Oracle ver 1.21, if I attempt to
insert/update a lob column, connecting as schema_X, I get an error 942 -
table or view does not exist.
It would seem the error is not being caused by the insert/update, but
something DBD::Oracle is doing afterwards. Here is a snippet from the
trace ...
:
DBI 1.605-ithread default trace level set to 0x0/2 (pid 26352) at
sb3.plx line 79 via sb3.plx line 124
- prepare for DBD::Oracle::db (DBI::db=HASH(0x8e59c0c)~0x8e59738 '
insert
intosb1 (id, text_b)
values (100, :txt)
') thr#8153008
dbd_preparse scanned 1 distinct placeholders
- prepare= DBI::st=HASH(0x8edbeb4) at sb3.plx line 80
- bind_param for DBD::Oracle::st (DBI::st=HASH(0x8edbeb4)~0x8bef8c0
':txt' '���A..zip-usa.csv.Խ�.�:.�{�..��...' (type 0, attribs:
HASH(0x8153c28))
- bind_param= 1 at sb3.plx line 85
- execute for DBD::Oracle::st (DBI::st=HASH(0x8edbeb4)~0x8bef8c0)
thr#8153008
dbd_st_execute INSERT (out0, lob1)...
Statement Execute Mode is 0
dbd_st_execute INSERT returned (SUCCESS, rpc1, fn3, out0)
!! ERROR: '942' 'ORA-00942: table or view does not exist (DBD ERROR:
OCIStmtExecute/LOB refetch)' (err#1)
- execute= undef at sb3.plx line 87
- HandleError on DBI::st=HASH(0x8bef8c0) via CODE(0x8ed5ff4)
(undef)
:
If I either connect as schema_Y (which owns the table), or qualify the
table name with the schema name (e.g. schema_y.sb1), the error does not
occur.
If I wrap the insert/update with a BEGIN .. END; (i.e. turn it into an
anonymous pl/sql block), it works ok, but I'd rather not have to go
through all our code and do this.
Is this a regression, or intended new behaviour?
Thanks,
Steve
**
This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.
**
A Question about DBD-Informix
I'm running into a problem installing DBD-Informix. I'm running Perl version 5.8.2 on AIX against Informix 10 with the current DBI. The version of the Informix SDK is 2.01, with no option to upgrade since later releases enforce XA compliance and regrettably a lot of our other non-perl code isn't XA Compliant and breaks. The Makefile.PL logic says that our SDK is too old. So is there an earlier release of DBD-Informix out there we could attempt to use? Or does anyone know how to tweak the current release to make this work? Or any other suggestions? We've tried both DBD-Informix-2008.0513 DBD-Informix-2007.0914 without luck. Curtis
Why do I encounter this error ? ORA-12705 : Cannot access NLS data files or invalid environment specified
Hello
I do not understand why I encounter this error :
ORA-12705 : Cannot access NLS data files or invalid environment
specified
My env is :
ORACLE_BASE=/app/oracle
ORACLE_HOME=/app/oracle/product/10.2.0/enterprise64bit
ORACLE_SID=sidtest
NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
PATH=$PATH:$ORACLE_HOME/bin
LD_LIBRARY_PATH=/app/oracle/product/10.2.0/enterprise64bit/lib
export ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG PATH
LD_LIBRARY_PATH
My Perl script is :
use DBI;
my $dbh = DBI-connect( 'dbi:Oracle:sidtest', 'test', 'test', {
RaiseError = 1, AutoCommit = 0 } );
print $dbh-selectrow_array(qq{ SELECT 'Hello World from
DBI/DBD::Oracle' FROM DUAL }),\n;
$dbh-disconnect();
When I execute it, it fails with this error
DBI connect('sidtest','test',...) failed: ORA-12705: Cannot access NLS
data files or invalid environment specified (DBD ERROR: OCISessionBegin)
at test2.pl line 3
If I unset NLS_LANG it works fine. Why setting NLS_LANG disturbs the
script ?
Oracle strongly recommends that you set the NLS_LANG on the client
when you read this link :
http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20fa
q.htm#_Toc110410548
My Perl config is : perl 5.8.8 on Solaris 10 with DBI 1.58 and
DBD::Oracle 1.19
Thanks for your explanation
Regards
Pascal
Re: Why do I encounter this error ? ORA-12705 : Cannot access NLS data files or invalid environment specified
Had to give you a 100% answer to this but the good thing (at least to me
:-) ) is it not really a DBD::Oracle issue.
Looking at your env it seems your db is 64 bit in that case
DBD::Oracle is most likely compiled and running with the 32 client
someplace other than
ORACLE_HOME=/app/oracle/product/10.2.0/enterprise64bit
and Oracle cannot find the NLSdata files it was compiled against.
This is just a shot in the dark.
you might want to unset NLS_LANG and then set it from within your perl
script so it is the same value as the DB. When you do not set
NLS_LANG is simply uses the DBs default with might work here as well.
cheers
John Scoles
[EMAIL PROTECTED] wrote:
Hello
I do not understand why I encounter this error :
ORA-12705 : Cannot access NLS data files or invalid environment
specified
My env is :
ORACLE_BASE=/app/oracle
ORACLE_HOME=/app/oracle/product/10.2.0/enterprise64bit
ORACLE_SID=sidtest
NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
PATH=$PATH:$ORACLE_HOME/bin
LD_LIBRARY_PATH=/app/oracle/product/10.2.0/enterprise64bit/lib
export ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG PATH
LD_LIBRARY_PATH
My Perl script is :
use DBI;
my $dbh = DBI-connect( 'dbi:Oracle:sidtest', 'test', 'test', {
RaiseError = 1, AutoCommit = 0 } );
print $dbh-selectrow_array(qq{ SELECT 'Hello World from
DBI/DBD::Oracle' FROM DUAL }),\n;
$dbh-disconnect();
When I execute it, it fails with this error
DBI connect('sidtest','test',...) failed: ORA-12705: Cannot access NLS
data files or invalid environment specified (DBD ERROR: OCISessionBegin)
at test2.pl line 3
If I unset NLS_LANG it works fine. Why setting NLS_LANG disturbs the
script ?
Oracle strongly recommends that you set the NLS_LANG on the client
when you read this link :
http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20fa
q.htm#_Toc110410548
My Perl config is : perl 5.8.8 on Solaris 10 with DBI 1.58 and
DBD::Oracle 1.19
Thanks for your explanation
Regards
Pascal
Re: Why do I encounter this error ? ORA-12705 : Cannot access NLS data files or invalid environment specified
[EMAIL PROTECTED] wrote: Hello John I think you shot very well and very fast :-) I compiled DBD::Oracle with the 32 client of Oracle 9i that is installed in 64bit too. So because NLS data in Oracle 9i are stored by default in /app/oracle/product/9.2.0/enterprise64bit/ocommon/nls/admin/data I set the ORA_NLS33 to this path (I set ORA_NLS33 because I am using within Perl the Oracle 9i client library). And then it works ! I am glad It was sort of a wisdom guess on my part. I have never used 32/64 bit boxes but I have seen lots of posts on this vain So I am happy : it works but I am confused now. Did I do something wrong ? No nothing these configuration snafus happen all the time especially when working on 32/64 bit boxes My environment is a mix of Oracle 9i and Oracle 10g installed in 64bit on different Unix I decided to build DBD::Oracle with Oracle 9i, perhaps would it be better to compile with Oracle 10g. And for Perl scripts, perhaps is it better to set variables inside the script itself instead of using variable of environment ? I test that too it works. Do you have recommendations in regards with your experience ? The instant client might be what you it has a very small footprint. As for setting the variables in the script I think that will make you code work for a entire environment and removes possiable users problems who might have different setting that override or confuse DBD::Oracle or Oracle. Of course it all depends on what you are going to use the script for and who is using it. So short answer Yes with a But, Long Answer No with a Maybe. Cheers
Re: A Question about DBD-Informix
On Thu, Jul 31, 2008 at 8:16 AM, Curtis Leach [EMAIL PROTECTED] wrote:
I'm running into a problem installing DBD-Informix.
I'm running Perl version 5.8.2 on AIX against Informix 10 with the
current DBI.
Well, given what you say below, I shouldn't carp too much about 5.8.2 being
rather ancient.
The version of the Informix SDK is 2.01, with no option to upgrade since
later releases enforce XA compliance and regrettably a lot of our other
non-perl code isn't XA Compliant and breaks.
CSDK 2.01 -- that would be from 1996 or 1997, I believe.
You have at least one upgrade option - install a modern CSDK (3.00, 3.50)
into a new INFORMIXDIR (separate from where you have CSDK 2.01 installed),
and build DBD::Informix against that new version. You would copy (or,
better, link) the sqlhosts file from the INFORMIXDIR containing the archaic
CSDK to the INFORMIXDIR containing the new code. You might want to futz
INFORMIXDIR when running Perl to point to the new INFORMIXDIR, but it should
mostly work even if you use the old one by accident.
Failing that, disable the testing -- edit Makefile.PL to let CSDK 2.01
through. I've not consciously put anything in that will break - as long as
you get the ESQL/C version number right (9.14).
However, it most certainly won't be supported.
The check is:
elsif ($effvernum = 900 $effvernum 916)
{
# ESQL/C 9.0x and 9.10 or 9.11 were pre-releases of the ESQL/C for
# the Informix Universal Server (IUS) - since renamed several times.
# ESQL/C 9.12 was released as ESQL/C 9.12.
# ESQL/C 9.13 was released in DevSDK 2.00
# ESQL/C 9.14 was released in ClientSDK 2.01
# ESQL/C 9.15 was released in ClientSDK 2.02
# All of these are long obsolete and are no longer supported.
dbd_ix_die nlws(qq%
This version of ESQL/C ($infversion) is obsolete.
Please upgrade to ClientSDK version 2.70 or later.
%);
}
Comment out the dbd_ix_die() call, or replace dbd_ix_die with warn.
You could go back and get a really old DBD::Informix and a matching DBI -- I
wouldn't recommend it, but you could do it.
I also won't guarantee that everything will compile - much less work - but
there's a semi-decent chance. The code does still compile with ESQL/C 5.20
(which is much older than the 9.x versions of ESQL/C, and also older than
the 2.x and 3.x versions of ESQL/C, where 3.50 is the current vesion - don't
ask, because I don't need my blood-pressure to rise again), so the
functionality missing in 9.14 that is present in the latest versions (3.50)
should also be excluded for 9.14.
The Makefile.PL logic says that our SDK is too old.
The Makefile.PL logic is correct - your SDK is too old. I probably declared
CSDK 2.01 obsolete about 5 or 6 years ago.
So is there an earlier release of DBD-Informix out there we could
attempt to use? Or does anyone know how to tweak the current release to
make this work? Or any other suggestions?
We've tried both DBD-Informix-2008.0513 DBD-Informix-2007.0914 without
luck.
If you can't find old versions out on backpan.cpan.org let me know which
version you want to experiment with - and provide me with a convenient way
to get files to you (FTP site, for example).
--
Jonathan Leffler [EMAIL PROTECTED] #include disclaimer.h
Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org
Blessed are we who can laugh at ourselves, for we shall never cease to be
amused.
Solaris 10 - problem with connect to Oracle 10.2.0.2 with ORA_SYSDBA
Sorry for my poor English.
After connect in SYSDBA to an Oracle 10.2.0.2 Database, all system
command return '-1' (No child processes).
Problem detect on a Solaris 10 x86 server :
Details :
perl -MDBI -e 'DBI-installed_versions'
Perl : 5.008006 (i86pc-solaris-thread-multi)
OS : solaris (2.10)
DBI : 1.42
DBD::Sponge : 11.10
DBD::Proxy : 0.2004
DBD::Oracle : 1.15
DBD::File : 0.30
DBD::ExampleP : 11.12
DBD::DBM : 0.01
It's a production server, I can't upgrade DBD::Oracle for the moment.
It's the first application on the server to use perl,others only use
shell/sqlplus
This code will show the problem :
use DBD::Oracle qw(:ora_session_modes);
my $dsn = dbi:Oracle:; # no dbname here
print TWO_TASK not defined\n if (not defined($ENV{TWO_TASK}));
print ORACLE_SID : $ENV{ORACLE_SID} \n;
my $dbh = DBI-connect($dsn, , , { ora_session_mode =
ORA_SYSDBA });
$dbh-{RaiseError} = 1;
my $sth = $dbh-prepare( select sysdate from dual );
$sth-execute();
my $date=$sth-fetchrow();
print Date : $date \n;
$sth-finish();
$dbh-disconnect();
my $ret=system(date);
print retour : $ret;
Result on my machine :
TWO_TASK not defined
ORACLE_SID : KJD00
Date : 30-JUL-08
mercredi, 30 juillet 2008, 14:32:22 MEST
retour : -1
Get you the same result as me?
RE: A Question about DBD-Informix
Thanks for your insight. We'll see how things go.
Curtis
From: Jonathan Leffler [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 31, 2008 2:42 PM
To: Curtis Leach
Cc: dbi-users@perl.org
Subject: Re: A Question about DBD-Informix
On Thu, Jul 31, 2008 at 8:16 AM, Curtis Leach [EMAIL PROTECTED]
wrote:
I'm running into a problem installing DBD-Informix.
I'm running Perl version 5.8.2 on AIX against Informix 10 with
the
current DBI.
Well, given what you say below, I shouldn't carp too much about 5.8.2
being rather ancient.
The version of the Informix SDK is 2.01, with no option to
upgrade since
later releases enforce XA compliance and regrettably a lot of
our other
non-perl code isn't XA Compliant and breaks.
CSDK 2.01 -- that would be from 1996 or 1997, I believe.
You have at least one upgrade option - install a modern CSDK (3.00,
3.50) into a new INFORMIXDIR (separate from where you have CSDK 2.01
installed), and build DBD::Informix against that new version. You would
copy (or, better, link) the sqlhosts file from the INFORMIXDIR
containing the archaic CSDK to the INFORMIXDIR containing the new code.
You might want to futz INFORMIXDIR when running Perl to point to the new
INFORMIXDIR, but it should mostly work even if you use the old one by
accident.
Failing that, disable the testing -- edit Makefile.PL to let CSDK 2.01
through. I've not consciously put anything in that will break - as long
as you get the ESQL/C version number right (9.14).
However, it most certainly won't be supported.
The check is:
elsif ($effvernum = 900 $effvernum 916)
{
# ESQL/C 9.0x and 9.10 or 9.11 were pre-releases of the ESQL/C for
# the Informix Universal Server (IUS) - since renamed several times.
# ESQL/C 9.12 was released as ESQL/C 9.12.
# ESQL/C 9.13 was released in DevSDK 2.00
# ESQL/C 9.14 was released in ClientSDK 2.01
# ESQL/C 9.15 was released in ClientSDK 2.02
# All of these are long obsolete and are no longer supported.
dbd_ix_die nlws(qq%
This version of ESQL/C ($infversion) is obsolete.
Please upgrade to ClientSDK version 2.70 or later.
%);
}
Comment out the dbd_ix_die() call, or replace dbd_ix_die with warn.
You could go back and get a really old DBD::Informix and a matching DBI
-- I wouldn't recommend it, but you could do it.
I also won't guarantee that everything will compile - much less work -
but there's a semi-decent chance. The code does still compile with
ESQL/C 5.20 (which is much older than the 9.x versions of ESQL/C, and
also older than the 2.x and 3.x versions of ESQL/C, where 3.50 is the
current vesion - don't ask, because I don't need my blood-pressure to
rise again), so the functionality missing in 9.14 that is present in the
latest versions (3.50) should also be excluded for 9.14.
The Makefile.PL logic says that our SDK is too old.
The Makefile.PL logic is correct - your SDK is too old. I probably
declared CSDK 2.01 obsolete about 5 or 6 years ago.
So is there an earlier release of DBD-Informix out there we
could
attempt to use? Or does anyone know how to tweak the current
release to
make this work? Or any other suggestions?
We've tried both DBD-Informix-2008.0513 DBD-Informix-2007.0914
without
luck.
If you can't find old versions out on backpan.cpan.org let me know which
version you want to experiment with - and provide me with a convenient
way to get files to you (FTP site, for example).
--
Jonathan Leffler [EMAIL PROTECTED] #include disclaimer.h
Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org
Blessed are we who can laugh at ourselves, for we shall never cease to
be amused.
Re: Solaris 10 - problem with connect to Oracle 10.2.0.2 with ORA_SYSDBA
olwin wrote:
Sorry for my poor English.
After connect in SYSDBA to an Oracle 10.2.0.2 Database, all system
command return '-1' (No child processes).
Problem detect on a Solaris 10 x86 server :
Details :
perl -MDBI -e 'DBI-installed_versions'
Perl : 5.008006 (i86pc-solaris-thread-multi)
OS : solaris (2.10)
DBI : 1.42
DBD::Sponge : 11.10
DBD::Proxy : 0.2004
DBD::Oracle : 1.15
DBD::File : 0.30
DBD::ExampleP : 11.12
DBD::DBM : 0.01
It's a production server, I can't upgrade DBD::Oracle for the moment.
It's the first application on the server to use perl,others only use
shell/sqlplus
This code will show the problem :
use DBD::Oracle qw(:ora_session_modes);
my $dsn = dbi:Oracle:; # no dbname here
print TWO_TASK not defined\n if (not defined($ENV{TWO_TASK}));
print ORACLE_SID : $ENV{ORACLE_SID} \n;
my $dbh = DBI-connect($dsn, , , { ora_session_mode =
ORA_SYSDBA });
$dbh-{RaiseError} = 1;
my $sth = $dbh-prepare( select sysdate from dual );
$sth-execute();
my $date=$sth-fetchrow();
print Date : $date \n;
$sth-finish();
$dbh-disconnect();
my $ret=system(date);
print retour : $ret;
Result on my machine :
TWO_TASK not defined
ORACLE_SID : KJD00
Date : 30-JUL-08
mercredi, 30 juillet 2008, 14:32:22 MEST
retour : -1
Get you the same result as me?
At first glance it seemed to me that this has nothing to do with
DBD::Oracle or DBI and did not belong on this list. However, you are
using the system command and given it returns -1 it failed. If you use
the system command like above, it forks, issues your command and does a
wait for the child to exit (this may depend on SIGCHILD signal). The
oracle client libraries fiddle with SIGCHILD handling - presumably
because it wants to know a SIGCHILD signals. You probably want to return
SIGCHILD behaviour back to the default but you should realise this may
impact on the oracle client libraries. The following link might help
explain some of this but I cannot recollect a definite solution for
system in perl and Oracle:
http://www.easysoft.com/support/kb/kb00967.html
This link looks promising:
http://coding.derkeiler.com/Archive/Perl/perl.dbi.users/2008-06/msg00042.html
which mentions
BEQUEATH_DETACH = yes
Martin
