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
