Unfortunately changing the table to allow nulls isn't an option. It takes something like an act of congress to get things changed around here so that option is out. I actually tried this tactic but was shot down by the powers that be as something 'not worth the cost to implement'. I'm not sure why all spaces would be considered null by DBI, it is a value and has meaning in my system (I didn't design it and I can't change it so spaces has no value).
I need some solution that will force DBI to read all spaces literally as all spaces and not null while not making the field too big to fit in the column. Thanks, Gordon -----Original Message----- From: Kokarski, Anton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 05, 2002 1:00 PM To: Rhea, Gordon Subject: RE: Inserting spaces into a NOT NULL column Well an easy way woult be change the column to null and insert null where your variable is all spaces. on the other hand that column is set to be not null for a reason. You need to look at it from design point of view as well. Good luck, Anton -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 05, 2002 10:57 AM To: [EMAIL PROTECTED] Subject: Inserting spaces into a NOT NULL column Here's my problem: I am interfacing an Oracle 8i system with DBI. I want to insert a record into a table that I got from a file. The column that I want to insert into the table is all spaces. When DBI passes the insert statement to Oracle I get an error saying 'cannot insert NULL into' error. However when I call the quote method on the variable I get an 'inserted value too large for column' error. >From checking the dbitrace log file I see that the value passed to oracle is '<data>'. It seems that the field is counting the quotes of the field when computing the field size. The only solution I have come up with is to do a search/replace on spaces and put another character in place of them. However, a field of all spaces is a valid data condition for my application and needs to be there. code: $sth = $DBH->prepare("INSERT INTO $table VALUES(?, ?, ?, ?, ?, ?, ?, ?)") or die log_message("ERROR: Prepare: " . $DBH->errstr()); while(<INFILE>) { # foreach used to quote all space only fields. # I've tried it with and without this giving the two errors above foreach $item (@data) { if ($item =~ /^\s+$/) { $item = $DBH->quote($item); } } @data = split /$DELIMITER/, $_; $sth->execute(@data) or die log_message("ERROR executing insert: " . $DBH->errstr()); } If anyone has any ideas on how to solve this problem let me know. Thanks, Gordon
