0

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

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
Salar
  • 2,088
  • 21
  • 26
  • @Jeffrey I was aware of that but unfortunately the answers doesn't provide any working solution according these conditions. – Salar Nov 22 '13 at 09:49
  • 1
    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 Nov 22 '13 at 13:13
  • The answer in the duplicate question says use `varchar2` ? – Rippo Nov 22 '13 at 13:46
  • @Jeffrey, there got be a workaround even for NHibernate or .net – Salar Nov 22 '13 at 18:17
  • 1
    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 Nov 23 '13 at 03:33
  • Well if I could, I would do that. The app contains about 140 tables and 80 views and the sqlServer version is already in use. The problem is not just an issue for one table. – Salar Nov 23 '13 at 04:45
  • Jeffrey is correct that oracle itself doesn't recognize an empty string. It's stores them as null. This is just how oracle operates. The question jeffrey asks is why would you want to do this. Well an empty string could denote that a value was given while a null means something was never populated. Also null <> null so you need to use a coalesce around it. – Fran Nov 23 '13 at 19:05
  • I know '' is popular in some other RDBMSs, but personally I cannot see the point of a `not null` constraint if you can just insert another kind of non-value. Great, now you have two kinds of emptiness and an application-level convention on what each one means. I'm glad Oracle never went down that path. – William Robertson Nov 15 '15 at 11:53
  • I was looking for a Nhibernate workarround to this issue, but thanks to the moderators, I won't find it here. I hope someday they choose to be either fool or drastic but not the two at the same time. – Serge Profafilecebook Feb 04 '16 at 09:40

1 Answers1

-1

you can use nvl(column_name,'')

Good Luck

CanFil
  • 335
  • 2
  • 8
  • 1
    As stated above, `''` is converted back to `[NULL]`. `SELECT NVL(column_name, '') FROM DUAL` never returns an empty string. – Koen Nov 24 '22 at 16:04