如何在 Oracle 11g 中存储无限字符?
我们在 Oracle 11g 中有一个带有 varchar2 列的表.我们使用专有编程语言，其中此列被定义为字符串.我们最多可以在此列中存储 2000 个字符(4000 个字节).现在的要求是，该列需要存储超过 2000 个字符(实际上是无限个字符).出于维护原因，DBA 不喜欢 BLOB 或 LONG 数据类型.
We have a table in Oracle 11g with a varchar2 column. We use a proprietary programming language where this column is defined as string. Maximum we can store 2000 characters (4000 bytes) in this column. Now the requirement is such that the column needs to store more than 2000 characters (in fact unlimited characters). The DBAs don't like BLOB or LONG datatypes for maintenance reasons.
我能想到的解决办法是把这个列从原来的表中去掉，给这个列一个单独的表，然后把每个字符存成一行，这样才能得到无限的字符.此表将与原始表连接以进行查询.
The solution that I can think of is to remove this column from the original table and have a separate table for this column and then store each character in a row, in order to get unlimited characters. This tble will be joined with the original table for queries.
有没有更好的办法解决这个问题?
Is there any better solution to this problem?
更新:专有编程语言允许定义字符串和 blob 类型的变量，没有 CLOB 选项.我理解给出的答复，但我无法接受 DBA.我知道偏离 BLOB 或 LONG 将是开发人员的噩梦，但仍然无法控制.
UPDATE: The proprietary programming language allows to define variables of type string and blob, there is no option of CLOB. I understand the responses given, but I cannot take on the DBAs. I understand that deviating from BLOB or LONG will be developers' nightmare, but still cannot help it.
更新 2:如果我需要的最大字符数是 8000 个字符，我可以再添加 3 列，这样我就有 4 列，每列有 2000 个字符以获得 8000 个字符.因此，当第一列已满时，值将溢出到下一列，依此类推.这种设计会不会有什么不好的副作用?请提出建议.
UPDATE 2: If maximum I need is 8000 characters, can I just add 3 more columns so that I will have 4 columns with 2000 char each to get 8000 chars. So when the first column is full, values would be spilled over to the next column and so on. Will this design have any bad side effects? Please suggest.
推荐答案
如果 blob 是您所需要的，请说服您的 dba 这就是您所需要的.这些数据类型的存在是有原因的，任何推出你自己的实现都会比内置类型更糟糕.
If a blob is what you need convince your dba it's what you need. Those data types are there for a reason and any roll your own implementation will be worse than the built in type.
此外，您可能还想查看 CLOB 类型，因为它可以满足您的需求还不错.
Also you might want to look at the CLOB type as it will meet your needs quite well.
相关文章