Oracle DBI quote

Cine has asked for the wisdom of the Perl Monks concerning the following question:

Does anyone here know how to get a field set to the empty string in Oracle?
My problems is that I'm porting some data from a mysql DB to an Oracle DB. However Oracle seems to think that '' IS NULL (how stupid is that!) so how do I get it to accept SOME value which is close to the empty strin?
My current approach is this, but it doesnt seem to work:

my $odbh = DBI->connect("dbi:Oracle:stuff","xxx","x", { RaiseError => 
+1, PrintError => 1, ChopBlanks => 0 });
my $mdbh = DBI->connect("dbi:mysql:kultur","xxx","x", { RaiseError => 
+1, PrintError => 1 });
$odbh->{ChopBlanks} = 0;
my $msth = $mdbh->prepare("SELECT * FROM atable limit 1");
my $osth = $odbh->prepare("INSERT INTO atable (".(join",",@atablelist)
+.") VALUES (".(join ",",map {"?"} @atablelist).")");
$msth->execute() || die $mdbh->errstr;
while (my $db=$msth->fetchrow_hashref) {
  $osth->execute(map {defined $$db{$_} && $$db{$_} eq '' ? " " : $$db{
+$_}} @atablelist) || die $odbh->errstr;
}
[download]

Which gives the excellent message:

DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into ("X
+XX"."ATABLE"."ARROVERSKRIFTSPROG") (DBD ERROR: OCIStmtExecute)
[download]

T I M T O W T D I

Comment on Oracle DBI quote Select or Download Code

Replies are listed 'Best First'.
Re: Oracle DBI quote by mpeppler (Vicar) on Oct 25, 2002 at 21:06 UTC
Oracle's conversion of '' to NULL is a well-known problem. Of course, other systems (Sybase for example) convert '' to ' ' (i.e. a single space) if the value is a varchar() - essentially because a zero length string is considered the same thing as NULL. I'm pretty sure that this behavior (i.e. inability to store a zero length string) is mandated by the SQL standard. In any case it's been discussed to death on the dbi-users mailing list, and in other lists/newsgroups as well. Michael	[reply]
Re: Re: Oracle DBI quote by Cine (Friar) on Oct 25, 2002 at 22:53 UTC
Probably ;) However, I dont really care if there is a single space, but I cant get that to work and that is my real problem. T I M T O W T D I	[reply] [d/l]
Re: Re: Re: Oracle DBI quote by mpeppler (Vicar) on Oct 25, 2002 at 23:27 UTC
I think that you can do something like this (culled from the dbi-users mailing list): `use DBD::Oracle qw( :ora_types ); ... $sth->bind_param($param_num, ' ', { ora_type => ORA_CHAR });` [download] I'm not an Oracle user at all so I can't guarantee that this is correct. Michael	[reply] [d/l]
Re: Oracle DBI quote by demerphq (Chancellor) on Oct 25, 2002 at 19:22 UTC
What happens if you don't use placeholders? Maybe this is bogus, but im guessing that its the DBI/DBD layer thats causing this problem. --- demerphq my friends call me, usually because I'm late....	[reply]
Re: Oracle DBI quote by runrig (Abbot) on Oct 25, 2002 at 19:50 UTC
What happens if you insert the empty string into the same type of field from sqlplus (Update: actually, I would expect the same results as demerphq's suggesstion above, but you never know)? If you get the same result, then its not DBI/DBD (maybe the empty string is interpreted as null in a char column?). If you get different results, then you could post a DBI trace to the DBI mailing list and ask for help there.	[reply]
Re: Re: Oracle DBI quote by Cine (Friar) on Oct 25, 2002 at 22:58 UTC
It is a well known fact that Oracle (among others?) thinks that an empty string is NULL, so the problem resides in all tools which doesn't do anything special about it. However I do try to do something special about it. I use a string with only a single space and turn off ChopBlanks, but this is not working for some reason. T I M T O W T D I	[reply] [d/l]


XP is just a number
	PerlMonks