Topic: Handling nils with Oracle

I am using the OCI layer with Rails and Oracle.  I have some fields limited to say varchar(5) and am attempting to import a CSV field that is nil (ie - ,, as the field in the line of the file) using FasterCSV.  But, the issue is, that I get an ORA error that I am attempting to insert a length of eight when the field is limited to 5.  Why would a nil be a length of 8?

I have done this as an interim work around, but it is ugly, any other ideas?:

107:       #Check for nil, as when attempting to add a nil, for some reason the field appears
108:       #longer in Oracle and throws an ORA error.
109:       row.each do |field|
110:         if row[cnt] != nil
111:           case cnt
112:             when 0
113:               lead.sourceid       = field
114:             when 1
115:               lead.name           = field
116:             when 2
117:               lead.capturingagent = field
118:             when 3
119:               lead.phone          = field
120:             when 4
121:               lead.phone1         = field
122:             when 5
123:               lead.phone2         = field
124:             when 6
125:               lead.phone3         = field
126:             when 7
127:               lead.phonedesc1     = field
128:             when 8
129:               lead.phonedesc2     = field
130:             when 9
131:               lead.phonedesc3     = field
132:             when 11
133:               lead.comments       = field
134:           end
135:         end
136:         cnt += 1
137:       end

Re: Handling nils with Oracle

Suggest you create a quick before insert trigger on the table and raise an error.  You can trap the value being passed with the trigger.  Alternatively simply enable auditing on your account and you should get logging of the DML statements issued.  Make sure your character set semantics are properly set, i.e. you aren't sending 4 multi-byte characters into a varchar2(5) that is set to byte semantics.