adding blank to varchar2 NOT NULL field in DBD::Oracle

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

I have a situation where I want to add a record to an Oracle 8.1.7 table. One of the fields is declared as VARCHAR2 NOT NULL, but I want its value to be a space.

DBD::Oracle 1.06 notes in the Changes file that as of 1.05, it strips trailing spaces from VARCHAR2 fields. This appears to be true on all operations, including SELECT.

Of course, if you strip trailing spaces from a field whose value is ONLY spaces, then the value becomes null (as far as Oracle is concerned).

The only workaround I've found thus far is to use NVL(?,' ') in the preparation of all statements which use this field.

Is there a better workaround? Please??

--Glenn

Comment on adding blank to varchar2 NOT NULL field in DBD::Oracle

Replies are listed 'Best First'.
Re: adding blank to varchar2 NOT NULL field in DBD::Oracle by Cubes (Pilgrim) on Jul 16, 2001 at 05:22 UTC
This doesn't answer your question at all, but please consider the effect of your actions on the poor souls who might have to deal with this data in the future. If the field is NOT NULL, it's probably because the database designer felt it very important to have a useful value in that field. Perhaps it would be worthwhile to investigate the reason, and either get the field changed or put the right data in there. For the sake of future users of this database, think about using some obviously bogus value like 'This field intentionally left blank', or at the very least some visible character like a dash or an x or something. Keep in mind that while your code can easily tell the difference between a null value and a space, to users of many GUI interfaces, they'll look exactly the same.	[reply]
Re: Re: adding blank to varchar2 NOT NULL field in DBD::Oracle by wardk (Deacon) on Jul 16, 2001 at 18:15 UTC
I upvoted this reply. Rather than attempt to defeat the purpose of the column constraint, perhaps anonymous monk should speak the the table owner and find out why this constraint exists. If the constraint is not needed, then remove the constraint instead of hacking it with bogus data.	[reply]
Re: adding blank to varchar2 NOT NULL field in DBD::Oracle by runrig (Abbot) on Jul 16, 2001 at 04:23 UTC
What happens if you bind the parameters as a STRING or CHAR or CHARZ? Though I think its funny trying to store single spaces in a field anyway.	[reply]