如何在 Oracle 11g 中存储无限字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2976154/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to store unlimited characters in Oracle 11g?
提问by user32262
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.
我们在 Oracle 11g 中有一个带有 varchar2 列的表。我们使用专有编程语言，其中此列被定义为字符串。我们最多可以在此列中存储 2000 个字符（4000 个字节）。现在的要求是，该列需要存储超过 2000 个字符（实际上是无限个字符）。出于维护原因，DBA 不喜欢 BLOB 或 LONG 数据类型。
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?
这个问题有没有更好的解决方案？
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.
更新：专有编程语言允许定义字符串和 blob 类型的变量，没有 CLOB 选项。我理解给出的答复，但我不能接受 DBA。我知道偏离 BLOB 或 LONG 将是开发人员的噩梦，但仍然无法帮助它。
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.
更新 2：如果我需要的最大字符数是 8000 个字符，我可以再添加 3 列，这样我就有 4 列，每列有 2000 个字符以获得 8000 个字符。因此，当第一列已满时，值将溢出到下一列，依此类推。这种设计会不会有什么不好的副作用？请建议。
回答by dkackman
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 ownimplementation will be worse than the built in type.
如果 blob 是您所需要的，请说服您的 dba，这正是您所需要的。这些数据类型的存在是有原因的，任何滚动你自己的实现都会比内置类型更糟糕。
Also you might want to look at the CLOBtype as it will meet your needs quite well.
此外，您可能还想查看CLOB类型，因为它可以很好地满足您的需求。
回答by Thomas Jones-Low
You could follow the way Oracle stored their stored procedures in the information schema. Define a table called text columns:
您可以按照 Oracle 在信息模式中存储其存储过程的方式进行操作。定义一个名为 text columns 的表：
CREATE TABLE MY_TEXT (
IDENTIFIER INT,
LINE INT,
TEXT VARCHAR2 (4000),
PRIMARY KEY (INDENTIFIER, LINE));
The identifier column is the foreign key to the original table. The Line is a simple integer (not a sequence) to keep the text fields in order. This allows keeping larger chunks of data
标识符列是原始表的外键。Line 是一个简单的整数（不是序列），用于保持文本字段的顺序。这允许保留更大的数据块
Yes this is not as efficient as a blob, clob, or LONG (I would avoid LONG fields if at all possible). Yes, this requires more mainenance, buf if your DBAs are dead set against managing CLOB fields in the database, this is option two.
是的，这不如 blob、clob 或 LONG 有效（如果可能，我会避免 LONG 字段）。是的，这需要更多的维护，如果您的 DBA 对管理数据库中的 CLOB 字段一无所知，那么这是选项二。
EDIT:
编辑：
My_Table below is where you currently have the VARCHAR column you are looking to expand. I would keep it in the table for the short text fields.
下面的 My_Table 是您当前要扩展的 VARCHAR 列的位置。对于短文本字段，我会将其保留在表格中。
CREATE TABLE MY_TABLE (
INDENTIFER INT,
OTHER_FIELD VARCHAR2(10),
REQUIRED_TEXT VARCHAR(4000),
PRIMERY KEY (IDENTFIER));
Then write the query to pull the data join the two tables, ordering by LINE in the MY_TEXT field. Your application will need to split the string into 2000 character chunks and insert them in line order.
然后编写查询以将数据连接到两个表中，在 MY_TEXT 字段中按 LINE 排序。您的应用程序需要将字符串拆分为 2000 个字符块并按行顺序插入它们。
I would do this in a PL/SQL procedure. Both insert and select. PL/SQL VARCHAR strings can be up to 32K characters. Which may or may not be large enough for your needs.
我会在 PL/SQL 过程中执行此操作。插入和选择。PL/SQL VARCHAR 字符串最多可以有 32K 个字符。这可能足以满足您的需求，也可能不够大。
But like every other person answering this question, I would strongly suggest making a case to the DBA to make the column a CLOB. From the program perspective this will be a BLOB and therefore simple to manage.
但是就像回答这个问题的其他人一样，我强烈建议向 DBA 提出一个案例，使该列成为 CLOB。从程序的角度来看，这将是一个 BLOB，因此易于管理。
回答by Jeff
You said no BLOB or LONG... but what about CLOB? 4GB character data.
你说没有 BLOB 或 LONG ......但是 CLOB 呢？4GB 字符数据。
回答by Mark Baker
Is BFILE a viable alternative datatype for your DBAs?
BFILE 是您的 DBA 可行的替代数据类型吗？
回答by Gary Myers
I don't get it. A CLOB is the appropriate database datatype. If your weird programming language will deal with strings of 8000 (or whatever) characters, what stops it writing those to a CLOB.
我不明白。CLOB 是合适的数据库数据类型。如果您奇怪的编程语言将处理 8000 个（或其他）字符的字符串，那么是什么阻止它将这些字符串写入 CLOB。
More specifically, what error do you get (from Oracle or your programming language) when you try to insert an 8000 character string into a column defined as a CLOB.
更具体地说，当您尝试将 8000 个字符串插入定义为 CLOB 的列时，您会得到什么错误（来自 Oracle 或您的编程语言）。
回答by Amirshk
BLOB is the best solution. Anything else will be less convenient and a bigger maintenance annoyance.
BLOB 是最好的解决方案。其他任何事情都会不太方便，并且会带来更大的维护烦恼。
