How to prevent oracle from converting empty strings to null [duplicate]

Question

I have successfully converted an existing app's database from SqlServer to Oracle. Everything works fine, but empty strings.

The app normally stores empty strings into the not null nvarchar2 fields which Oracle silently converts them to null and causes the following error:

ORA-01400: cannot insert NULL

Is there any workaround for this?

Note: I'm using Oracle Managed Driver ODP.NET + NHibernate Castle Active Record

@Jeffrey I was aware of that but unfortunately the answers doesn't provide any working solution according these conditions. — Salar
– Salar, Commented Nov 22, 2013 at 9:49
That's because there is no solution - in Oracle empty strings are null. That's it. You'll just have to work around it in some way. — Jeffrey Kemp
– Jeffrey Kemp, Commented Nov 22, 2013 at 13:13
@Jeffrey, there got be a workaround even for NHibernate or .net — Salar
– Salar, Commented Nov 22, 2013 at 18:17
Yes, the duplicate question refers to VARCHAR2 but the same principle applies to NVARCHAR2. Empty strings are null. You have to look at your application and work out why your application treats an empty string as special, i.e. why would you want an empty string to bypass the NOT NULL constraint - what possible value is an empty string vs. null? If you can answer that, maybe we can help you with an alternative design for your application. — Jeffrey Kemp
– Jeffrey Kemp, Commented Nov 23, 2013 at 3:33

CanFil · Accepted Answer · 2013-11-22 07:08:04Z

-1

you can use nvl(column_name,'')

Good Luck

answered Nov 22, 2013 at 7:08

CanFil

3252 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

As stated above, '' is converted back to [NULL]. SELECT NVL(column_name, '') FROM DUAL never returns an empty string.