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

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]
      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]
        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]
      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]

Back to Seekers of Perl Wisdom