Hi Ronald,
I was scanning the mail archives for
ORA-02005 errors because I got this error
when I run 'make test' installing Oracle DBD 1.06.
Yours was the most recent.
Here is how I fixed the problem.
Perl 5.6 provides 64 bit support, but most Perl Modules don't.
If Perl was built with use64bitint or use64bitall defined,
it uses 64 bit values. Things don't work correctly when
64 bit values are passed to DBI. I rebuilt Perl 5.6 with
both variables undefined. Then I rebuild DBI and DBD Oracle
pointing to the Perl without 64 bit support.
This solved the problem.
Maybe in the future, DBI and DBD::Oracle can provide 64 bit support.
thank you.
Albert Tom
1465E Enea Circle, Suite 1050
Concord, California 94520
925-677-2214
[EMAIL PROTECTED]
-----Original Message-----
From: Warden, Ronald MCF:EX [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 04, 2001 1:36 PM
To: '[EMAIL PROTECTED]'
Subject: Problems moving date fields between systems using DBI
Hello,
I am attempting to move data from one database to another using perl. Things
work fine until I run into date fields. I have checked the fine manual as
well as Tim's book and I haven't found much of help in either. There is some
dicussion of how an insert works, where you hand craft the insert and have a
date.
I have created a small test script that shows the problem.
#!/usr/bin/perl
use DBI qw( neat_list );
$ora_uid = 'XXXX';
$ora_pw = 'YYYY";
$ora_dbase = 'DDDD";
$ora_DSN = join ':','dbi','Oracle',$ora_dbase;
$ora_dbh = DBI->connect($ora_DSN, $ora_uid, $ora_pw)
|| die "Cannot connect to $DBI: $DBI::errstr\n" unless $ora_dbh;
$ora_dbh -> {RaiseError} = 1;
$ora_dbh -> {PrintError} = 1;
my $ora_sqld="ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'";
$ora_dbh->do($ora_sqld);
###########
#
# Create a test table to check out date inserts
#
###########
$ora_stmt = qq{create table test_date (test_id Number(2), test_dt date)
PCTFREE 5
PCTUSED 90
TABLESPACE sws_test
STORAGE (
INITIAL 10K
NEXT 5K
MINEXTENTS 1
MAXEXTENTS 3
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1)};
$ora_dbh->do($ora_stmt);
##########
#
# Test 1
#
##########
my $ora_sqld=qq{insert into test_date (test_id, test_dt) values (1,
to_date('2001-01-01', 'YYYY-MM-DD'))};
eval {
my $row = $ora_dbh->do($ora_sqld);
};
if ($@) {
print "Failed Test 1\n\t $DBI::errstr\n";
} else {
print "Passed Test 1\n";
}
print "Rows affected $row\n";
##########
#
# Test 2
#
##########
my $ora_sqld=qq{insert into test_date (test_id, test_dt)
values ( 2, to_date(NULL,'YYYY-MM-DD'))};
eval {
$ora_dbh->do($ora_sqld);
};
if ($@) {
print "Failed Test 2\n\t $DBI::errstr\n";
} else {
print "Passed Test 2\n";
}
##########
#
# Test 3
#
##########
my $test_3 = qq['2003-03-03','YYYY-MM-DD'];
my $ora_sqld=qq{insert into test_date (test_id, test_dt)
values ( 3, to_date($test_3))};
eval {
$ora_dbh->do($ora_sqld);
};
if ($@) {
print "Failed Test 3\n\t $DBI::errstr\n";
} else {
print "Passed Test 3\n";
}
##########
#
# Test 4
#
##########
my $t4_value = ' ';
my $ora_sqld=qq{insert into test_date (test_id, test_dt)
values ( 4, to_date(?, 'YYYY-MM-DD'))};
$ora_sth = $ora_dbh->prepare($ora_sqld);
$ora_sth->execute($t4_value) || warn "Failed Test 4\n\t $DBI::errstr\n";
print "Passed Test 4\n";
##########
#
# Test 5
#
##########
my $t5_value = '2005-05-05';
my $ora_sqld=qq{insert into test_date (test_id, test_dt)
values ( 5, to_date(?, 'YYYY-MM-DD'))};
$ora_sth = $ora_dbh->prepare($ora_sqld);
$ora_sth->execute($t5_value) || warn "Failed Test 5\n\t $DBI::errstr\n";
print "Passed Test 5\n";
##########
#
# Test 6#
##########
my $t6_value = undef;
my $ora_sqld=qq{insert into test_date (test_id, test_dt)
values ( 6, to_date(?, 'YYYY-MM-DD'))};
$ora_sth = $ora_dbh->prepare($ora_sqld);
$ora_sth->execute($t6_value) || warn "Failed Test 6\n\t $DBI::errstr\n";
print "Passed Test 6\n";
$ora_sth->finish();
$result =$ora_dbh->disconnect;
exit;
The script produces the following output:
test_date.pl
Passed Test 1
Rows affected
Passed Test 2
Passed Test 3
Passed Test 4
Passed Test 5
DBD::Oracle::st execute failed: ORA-02005: implicit (-1) length not
valid for this bind or define datatype (DBD ERROR: OCIBindByName) at
test_date.pl line 138.
Test 6 represents my problem. I read a date field 6 date fields out of 22
fields in total 4 out of the 6 date fields are NULL, that is they come back
from my source database as undef's. When I attempt to insert them I get my
ORA-02005 ERROR.
Have I missed something in the fine manual or Tim's excellent book to have
the NULL inserted (please note Test 2 where SQL let's you insert a NULL
value).
My environment is:
Sun Solaris 2.8
Perl v5.6.0
DBI 1.14
DBD Oracle 1.06
Oracle 8.1.7
Thanks for the help.
---
Ronald Warden
Corporate Data Manager
250.356.0466
Data Services Branch
Ministry for Children and Families
