DBD::Oracle: "" vs NULL, and a possible change to the ChopBlanks attribute

It's well know that Oracle#s state of the art database server can rarely tell the difference between an empty string and a NULL
(I presume that's not changed recently, though I'd be very happy
to be told otherwise).

So people who don't want empty strings being stored as NULLs in their fields tend to use some specific non-empty string insead, a single space being very common.

The DBI has an attribute called ChopBlanks that, when set true, enables the automatic removal of trailing spaces from fetched CHAR field data (which Oracle has 'helpfully' padded out to the declared fixed width).

I'm considering extending the definition of ChopBlanks to include VARCHAR type fields. This has been requested several times over the years by DBI users. The change would neatly hide the use of a space to represent an empty string.

I'd like to get some feedback on this idea from DBD::Oracle users.

I'm especially interested in the risk of changing the behaviour of existing code. That would _only_ happen if you explicitly set ChopBlanks, and fetch VARCHAR data that has trailing spaces, and the removal of those spaces would change the behaviour of your application.

An alternative approach would be to add a new private attribute that just translates a single space value into an empty value. That would be "safer" but less generically useful.

Tim

p.s. I've sent this to both oracle-l_at_fatcity.com and dbi-users_at_perl.org. Please delete at least one of these addresses when replying. Thanks.

p.p.s. A corresponding mechanism to optionally automatically treat empty strings bound to placeholders as a single space will probably also be added to DBD::Oracle and enabled via private attribute.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Bunce
  INET: Tim.Bunce_at_pobox.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).