Oracle认为空字符串为NULL，而SQL Server则不认为NULL-最佳处理方式是什么? [英] Oracle considers empty strings to be NULL while SQL Server does not - how is this best handled?

问题描述

我必须编写一个组件来在Oracle数据库中重新创建SQL Server表(结构和数据).该组件还必须获取输入到Oracle数据库中的新数据，然后将其复制回SQL Server.

I have to write a component that re-creates SQL Server tables (structure and data) in an Oracle database. This component also has to take new data entered into the Oracle database and copy it back into SQL Server.

将数据类型从SQL Server转换为Oracle没问题.但是，Oracle和SQL Server之间的关键区别引起了严重的麻烦. SQL Server认为空白字符串(")与NULL值不同，因此可以将char列定义为NOT NULL，但仍在数据中包含空白字符串.

Translating the data types from SQL Server to Oracle is not a problem. However, a critical difference between Oracle and SQL Server is causing a major headache. SQL Server considers a blank string ("") to be different from a NULL value, so a char column can be defined as NOT NULL and yet still include blank strings in the data.

Oracle认为空白字符串与NULL值相同，因此，如果将char列定义为NOT NULL，则不能插入空白字符串.每当NOT NULL char列在原始SQL Server数据中包含空白字符串时，这都会导致我的组件损坏.

Oracle considers a blank string to be the same as a NULL value, so if a char column is defined as NOT NULL, you cannot insert a blank string. This is causing my component to break whenever a NOT NULL char column contains a blank string in the original SQL Server data.

到目前为止，我的解决方案是在我的任何镜像Oracle表定义中都不使用NOT NULL，但是我需要一个更强大的解决方案.这必须是一种代码解决方案，因此答案不可能是使用某某的SQL2Oracle产品".

So far my solution has been to not use NOT NULL in any of my mirror Oracle table definitions, but I need a more robust solution. This has to be a code solution, so the answer can't be "use so-and-so's SQL2Oracle product".

您将如何解决此问题?

这是我到目前为止提出的唯一解决方案，它可能有助于说明问题.因为Oracle在NOT NULL列中不允许使用"，所以我的组件可以拦截来自SQL Server的任何此类值，并将其替换为"@"(例如).

here is the only solution I've come up with so far, and it may help to illustrate the problem. Because Oracle doesn't allow "" in a NOT NULL column, my component could intercept any such value coming from SQL Server and replace it with "@" (just for example).

当我向Oracle表中添加新记录时，如果我确实要插入"，则我的代码必须写"@"，并且当我的代码将新行复制回SQL Server时，它必须拦截"@"，而是输入".

When I add a new record to my Oracle table, my code has to write "@" if I really want to insert a "", and when my code copies the new row back to SQL Server, it has to intercept the "@" and instead write "".

我希望有一种更优雅的方式.

I'm hoping there's a more elegant way.

是否可能有一个更简单的解决方案，例如Oracle中的某些设置，使它可以将空白字符串与所有其他主要数据库相同地对待?而且该设置在Oracle Lite中是否也可用?

Edit 2: Is it possible that there's a simpler solution, like some setting in Oracle that gets it to treat blank strings the same as all the other major database? And would this setting also be available in Oracle Lite?

推荐答案

对此，我看不到任何简单的解决方案.

I don't see an easy solution for this.

也许您可以将值存储为一个或多个空白-> ' '(在Oracle中不是NULL)，或者通过额外的字段/表和适配器层来跟踪这种特殊情况.

Maybe you can store your values as one or more blanks -> ' ', which aren't NULLS in Oracle, or keep track of this special case through extra fields/tables, and an adapter layer.

这篇关于Oracle认为空字符串为NULL，而SQL Server则不认为NULL-最佳处理方式是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！