I see this has come up before, but looking at the archives it does not seem
to have been properly resolved.
Inserting a string containing only blanks into a NOT NULL column gives an
error, as in the example (note: the third insert is actually trying to
insert a single space character):
#!/u02/devel/bin/perl -w
#
use DBI;
my $db_user='****';
my $db_name='dwht01';
my $db_type='Oracle';
my $dbh=DBI->connect("dbi:$db_type:$db_name", $db_user, $db_user,
{AutoCommit => 0, PrintError => 0})
|| die "Cannot connect to $db_name: $DBI::errstr";
# create table with a null col
$dbh->do('DROP TABLE xyzzy')
|| warn "cannot drop table $DBI::errstr\n";
$dbh->do('CREATE TABLE xyzzy(a varchar(10) null, b varchar2(10) not null)')
|| die "cannot create table $DBI::errstr\n";
my $sql="INSERT INTO xyzzy(a,b) VALUES(?,?)";
my $sth=$dbh->prepare($sql)
|| die "cannot prepare $sql $DBI::errstr\n";
# normal insert
$sth->execute('1','a')
|| warn "cannot insert ['1','a'] $DBI::errstr\n";
# null insert
$sth->execute('2',undef)
|| warn "cannot insert ['2',undef] $DBI::errstr\n";
# blank insert
$sth->execute('3',' ')
|| warn "cannot insert ['3',' '] $DBI::errstr\n";
$sth->finish;
$sql="INSERT INTO xyzzy(a,b) VALUES(5,' ')";
$sth=$dbh->prepare($sql)
|| die "cannot prepare $sql $DBI::errstr\n";
$sth->execute
|| warn "cannot insert ['5',' '] $DBI::errstr\n";
$sth->finish;
$sql='SELECT * FROM xyzzy';
$sth=$dbh->prepare($sql)
|| die "cannot prepare $sql $DBI::errstr\n";
$sth->execute;
$sth->dump_results;
$sth->finish;
$dbh->commit;
$dbh->do('DROP TABLE xyzzy')
|| die "cannot drop table $DBI::errstr\n";
$dbh->disconnect;
which gives
cannot drop table ORA-00942: table or view does not exist (DBD ERROR:
OCIStmtExecute)
cannot insert ['2',undef] ORA-01400: cannot insert NULL into
("FOXM"."XYZZY"."B") (DBD ERROR: OCIStmtExecute)
cannot insert ['3',' '] ORA-01400: cannot insert NULL into
("FOXM"."XYZZY"."B") (DBD ERROR: OCIStmtExecute)
'1', 'a'
'5', ' '
2 rows
ie the 3rd insert unexpectedly fails
The platform is alpha OSF1, DBI 1.15 and DBD-Oracle 1.06, Oracle 8.0.4
Finally reading the Changes file, I found the database handle attribute
ora_ph_type, which sets the default binding (plus a request for testing
it!); I tried setting it to STRING with
{AutoCommit => 0, PrintError => 0, ora_ph_type => 5})
with no luck. I then tried CHAR using
{AutoCommit => 0, PrintError => 0, ora_ph_type => 96})
and it inserted correctly, producing:
cannot drop table ORA-00942: table or view does not exist (DBD ERROR:
OCIStmtExecute)
cannot insert ['2',undef] ORA-01400: cannot insert NULL into
("FOXM"."XYZZY"."B") (DBD ERROR: OCIStmtExecute)
'1', 'a'
'3', ' '
'5', ' '
3 rows
>From the documentation, I thing the STRING type should have worked.
Hopefully using CHAR will not create other side effects, although it has
correctly inserted a single space into a VARCHAR2(10) column in this
example.
Michael Fox
Australia Post
CAUTION
This e-mail and any files transmitted with it are privileged and confidential
information intended for the use of the addressee. The confidentiality and/or
privilege in this e-mail is not waived, lost or destroyed if it has been transmitted
to you in error. If you have received this e-mail in error you must (a) not
disseminate, copy or take any action in reliance on it; (b) please notify Australia
Post immediately by return e-mail to the sender; and (c) please delete the original
e-mail.
