oracle EXPORT AS INSERT STATEMENTS：但在 SQL Plus 中，该行覆盖了 2500 个字符！
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2735942/
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
EXPORT AS INSERT STATEMENTS: But in SQL Plus the line overrides 2500 characters!
提问by UltraCommit
I have to export an Oracle table as INSERT STATEMENTS.
我必须将 Oracle 表导出为 INSERT STATEMENTS。
But the INSERT STATEMENTS so generated, override 2500 characters.
但是INSERT STATEMENTS这样生成，覆盖了2500个字符。
I am obliged to execute them in SQL Plus, so I receive an error message.
我不得不在 SQL Plus 中执行它们，所以我收到一条错误消息。
This is my Oracle table:
这是我的 Oracle 表：
CREATE TABLE SAMPLE_TABLE
(
C01 VARCHAR2 (5 BYTE) NOT NULL,
C02 NUMBER (10) NOT NULL,
C03 NUMBER (5) NOT NULL,
C04 NUMBER (5) NOT NULL,
C05 VARCHAR2 (20 BYTE) NOT NULL,
c06 VARCHAR2 (200 BYTE) NOT NULL,
c07 VARCHAR2 (200 BYTE) NOT NULL,
c08 NUMBER (5) NOT NULL,
c09 NUMBER (10) NOT NULL,
c10 VARCHAR2 (80 BYTE),
c11 VARCHAR2 (200 BYTE),
c12 VARCHAR2 (200 BYTE),
c13 VARCHAR2 (4000 BYTE),
c14 VARCHAR2 (1 BYTE) DEFAULT 'N' NOT NULL,
c15 CHAR (1 BYTE),
c16 CHAR (1 BYTE)
);
ASSUMPTIONS:
假设：
a) I am OBLIGED to export table data as INSERT STATEMENTS; I am allowed to use UPDATE statements, in order to avoid the SQL*Plus error "sp2-0027 input is too long(>2499 characters)";
a) 我有义务将表数据导出为 INSERT STATEMENTS；我被允许使用 UPDATE 语句，以避免 SQL*Plus 错误“sp2-0027 input is too long(>2499 characters)”；
b) I am OBLIGED to use SQL*Plus to execute the script so generated.
b) 我有义务使用 SQL*Plus 来执行如此生成的脚本。
c) Please assume that every record can contain special characters: CHR(10), CHR(13), and so on;
c) 请假设每条记录都可以包含特殊字符：CHR(10)、CHR(13) 等；
d) I CAN'T use SQL Loader;
d) 我不能使用 SQL Loader；
e) I CAN'T export and then import the table: I can only add the "delta" using INSERT / UPDATE statements through SQL Plus.
e) 我不能导出然后导入表：我只能通过 SQL Plus 使用 INSERT / UPDATE 语句添加“delta”。
采纳答案by paxdiablo
Wow, those constraints are quite limiting but I think there may be a way around it. I think you may well have to write your own little script for this.
哇，这些限制非常有限，但我认为可能有办法解决它。我认为您可能必须为此编写自己的小脚本。
I would use Java with JDBC myself (but any language that can connect to and read the database, and output strings, will do), writing a little program which retrieved a record set of every row in the database. Then, for every one of those rows:
我自己会在 JDBC 中使用 Java（但任何可以连接和读取数据库并输出字符串的语言都可以），编写一个小程序来检索数据库中每一行的记录集。然后，对于这些行中的每一行：
Construct an insert statement with the full data. If this is less than 2,000 bytes, then just output it to the file and move on to the next row.
Otherwise create an insert statement for every field, but leave the
c13field as''(empty).Then, as long as your
c13inputstring is greater than 2000 characters, output an update statement of the form"update tbl set c13 = c13 || '" + c13input.substring (0,2000) + "' where ..."(appending the next 2000 characters) and then doc13input = c13input.substring(2000)to strip off those characters from your string.Once
c13inputis less than or equal to 2000 characters in length, just output one final update to tack it on the end.
用完整的数据构造一个插入语句。如果这小于 2,000 字节，则只需将其输出到文件并移至下一行。
否则为每个字段创建一个插入语句，但将该
c13字段保留为''（空）。然后，只要您的
c13input字符串大于 2000 个字符，就输出表单的更新语句"update tbl set c13 = c13 || '" + c13input.substring (0,2000) + "' where ..."（附加接下来的 2000 个字符），然后c13input = c13input.substring(2000)从字符串中去除这些字符。一次
c13input长度小于等于2000个字符，只输出一个最终更新，将其添加到最后。
This allows you to keep your individual SQL statements around the 2000-character mark and efficiently execute the correct SQL to repopulate another database table.
这使您可以将各个 SQL 语句保持在 2000 个字符左右，并有效地执行正确的 SQL 以重新填充另一个数据库表。
This is the type of thing I'm talking about (for a table containing just a primary key c1and a big honkin' varchar c13):
这是我正在谈论的事情类型（对于只包含一个主键c1和一个大 honkin' varchar 的表c13）：
rowset r = db.exec ("select * from oldtable");
while r.next != NO_MORE_ROWS:
string s = "insert into newtable (c1,c13) values ('" +
r.get("c1") + "','" + r.get("c13") + "')"
if s.len() < 2000:
print s
else:
s = "insert into newtable (c1,c13) values ('" + r.get("c1") + "','')"
print s
f = r.get("c13")
while f.len() > 2000:
s = "update newtable set c13 = c13 || '" + f.substring(0,2000) + ')"
f = f.substring(2000)
print s
endwhile
s = "update newtable set c13 = c13 || '" + f + ')"
print s
endif
endwhile
Obviously, you may need to morph the strings to allow inserts of special characters - I'm not sure what format Oracle expects these in, but it would hopefully be a simple matter of passing the strings (r.get("c13")if the length of the full insert is less than 2000, f.substring(0,2000)and fif you're constructing updates as well) to a helper function to do this.
显然，您可能需要对字符串进行变形以允许插入特殊字符 - 我不确定 Oracle 期望这些格式是什么，但希望它是传递字符串的简单问题（r.get("c13")如果完整插入的长度小于超过 2000 年，f.substring(0,2000)并且f如果您也在构建更新）到辅助函数来执行此操作。
If that morphing is likely to increase the size of the line printed, you may want to drop the threshold back to 1000 to be safe, to ensure the morphed string doesn't result in a line greater than the PL/SQL limit.
如果该变形可能会增加打印行的大小，为了安全起见，您可能希望将阈值降低回 1000，以确保变形字符串不会导致超过 PL/SQL 限制的行。
Sorry if that seems convoluted but the restrictions you've stated hamstring us a little bit. There may well be a better way but I can't think of one that meets allyour criteria.
对不起，如果这看起来令人费解，但您所说的限制让我们有点受挫。可能有更好的方法，但我想不出满足您所有标准的方法。
Update:It appears you're even morehamstrung than originally thought: if you have to limit yourself to SQL for generatingthe script as well as running it, there is a way, torturous though it is.
更新：看来您比原先想象的更加无所适从：如果您必须将自己限制在 SQL 来生成脚本以及运行它，那么有一种方法，尽管它很折磨人。
You can use SQL to generate SQL. Using my afore-mentioned table with c1and c13, you can do:
您可以使用 SQL 生成 SQL。使用我的上述表格c1和c13，您可以执行以下操作：
select
'insert into newtable (c1,c13) values ("' ||
c1 ||
'","");'
from oldtable;
# Xlates to: insert into newtable (c1,c13) values ("[c1]","");
That will give you all your baseline insertstatements for duplicating everything but the c13column.
这将为您提供insert用于复制除c13列之外的所有内容的所有基线语句。
What you then need to do is generate more statements for setting c13. To update c13for all values of length 1000 or less (simple set):
然后您需要做的是生成更多的设置语句c13。要更新c13所有长度为 1000 或更小的值（简单集）：
select
'update newtable set c13 = "' ||
c13 ||
'" where c1 = "' ||
c1 ||
'";'
from oldtable where length(c13) <= 1000;
# Xlates to: update newtable set c13 = "[c13]" where c1 = "[c1]";
# but only for rows where length([c13]) <= 1000
Then, to updatec13 for all values between 1001 and 2000 characters (set then append):
然后，对于update1001 到 2000 个字符之间的所有值，到c13（设置然后附加）：
select
'update newtable set c13 = "' ||
substring(c13,1,1000) ||
'" where c1 = "' ||
c1 ||
'";'
from oldtable where length(c13) > 1000 and length(c13) <= 2000;
select
'update newtable set c13 = c13 || "' ||
substring(c13,1001,1000) ||
'" where c1 = "' ||
c1 ||
'";'
from oldtable where length(c13) > 1000 and length(c13) <= 2000;
# Xlates to: update newtable set c13 = "[c13a]" where c1 = "[c1]";
# update newtable set c13 = c13 || "[c13b]" where c1 = "[c1]";
# but only for rows where length([c13]) > 1000 and <= 2000
# and [c13a]/[c13b] are the first/second thousand chars of c13.
And so on for the ones that are 2001-to-3000 and 3001-to-4000 in length.
对于长度为 2001 到 3000 和 3001 到 4000 的那些，依此类推。
There'll likely need to be some tweaking done. I'm happy to give you a way of solving it but my desire to work on such a monstrosity through to completion is minimal at best :-)
可能需要进行一些调整。我很高兴给你一个解决它的方法，但我对这样一个怪物工作直到完成的愿望充其量是最小的:-)
Will it get the job done? Yes. Is it pretty? I'd say that was a resounding "NO!" but, given your constraints, that may be the best you can hope for.
它会完成工作吗？是的。漂亮吗？我会说这是一个响亮的“不！” 但是，鉴于您的限制，这可能是您所能希望的最好结果。
As a proof of concept, here's an SQL script in DB2 (no special features though, it should work fine in any DBMS that has a lengthand substrequivalent):
作为概念证明，这里有一个 DB2 中的 SQL 脚本（虽然没有特殊功能，但它应该可以在任何具有length和substr等效项的DBMS 中正常工作）：
# Create table and populate.
DROP TABLE XYZ;
COMMIT;
CREATE TABLE XYZ (F1 VARCHAR(1),F2 VARCHAR(20));
COMMIT;
INSERT INTO XYZ VALUES ('1','PAX');
INSERT INTO XYZ VALUES ('2','GEORGE');
INSERT INTO XYZ VALUES ('3','VLADIMIR');
INSERT INTO XYZ VALUES ('4','ALEXANDRETTA');
SELECT * FROM XYZ ORDER BY F1;
# Create initial insert statem,ents.
SELECT 'INSERT INTO XYZ (F1,F2) VALUES (' || F1 ','''');'
FROM XYZ;
# Updates for 1-5 character F2 fields.
SELECT 'UPDATE XYZ SET F2 = ''' || F2 ||
''' WHERE F1 = ''' || F1 || ''';'
FROM XYZ WHERE LENGTH(F2) <= 5;
# Updates for 6-10 character F2 fields.
SELECT 'UPDATE XYZ SET F2 = ''' || SUBSTR(F2,1,5) ||
''' WHERE F1 = ''' || F1 || ''';'
FROM XYZ WHERE LENGTH(F2) > 5 AND LENGTH(F2) <= 10;
SELECT 'UPDATE XYZ SET F2 = F2 || ''' || SUBSTR(F2,6) ||
''' WHERE F1 = ''' || F1 || ''';'
FROM XYZ WHERE LENGTH(F2) > 5 AND LENGTH(F2) <= 10;
# Updates for 11-15 character F2 fields.
SELECT 'UPDATE XYZ SET F2 = ''' || SUBSTR(F2,1,5) ||
''' WHERE F1 = ''' || F1 || ''';'
FROM XYZ WHERE LENGTH(F2) > 10 AND LENGTH(F2) <= 15;
SELECT 'UPDATE XYZ SET F2 = F2 || ''' || SUBSTR(F2,6,5) ||
''' WHERE F1 = ''' || F1 || ''';'
FROM XYZ WHERE LENGTH(F2) > 10 AND LENGTH(F2) <= 15;
SELECT 'UPDATE XYZ SET F2 = F2 || ''' || SUBSTR(F2,11) ||
''' WHERE F1 = ''' || F1 || ''';'
FROM XYZ WHERE LENGTH(F2) > 10 AND LENGTH(F2) <= 15;
and this generates the following lines:
这会生成以下几行：
> DROP TABLE XYZ;
> COMMIT;
> CREATE TABLE XYZ (F1 VARCHAR(1),F2 VARCHAR(20));
> COMMIT;
> INSERT INTO XYZ VALUES ('1','PAX');
> INSERT INTO XYZ VALUES ('2','GEORGE');
> INSERT INTO XYZ VALUES ('3','VLADIMIR');
> INSERT INTO XYZ VALUES ('4','ALEXANDRETTA');
> SELECT * FROM XYZ;
F1 F2
-- ------------
1 PAX
2 GEORGE
3 VLADIMIR
4 ALEXANDRETTA
> SELECT 'INSERT INTO XYZ (F1,F2) VALUES (' || F1 || ','''');'
> FROM XYZ;
INSERT INTO XYZ (F1,F2) VALUES (1,'');
INSERT INTO XYZ (F1,F2) VALUES (2,'');
INSERT INTO XYZ (F1,F2) VALUES (3,'');
INSERT INTO XYZ (F1,F2) VALUES (4,'');
> SELECT 'UPDATE XYZ SET F2 = ''' || F2 ||
> ''' WHERE F1 = ''' || F1 || ''';'
> FROM XYZ WHERE LENGTH(F2) <= 5;
UPDATE XYZ SET F2 = 'PAX' WHERE F1 = '1';
> SELECT 'UPDATE XYZ SET F2 = ''' || SUBSTR(F2,1,5) ||
> ''' WHERE F1 = ''' || F1 || ''';'
> FROM XYZ WHERE LENGTH(F2) > 5 AND LENGTH(F2) <= 10;
UPDATE XYZ SET F2 = 'GEORG' WHERE F1 = '2';
UPDATE XYZ SET F2 = 'VLADI' WHERE F1 = '3';
> SELECT 'UPDATE XYZ SET F2 = F2 || ''' || SUBSTR(F2,6) ||
> ''' WHERE F1 = ''' || F1 || ''';'
> FROM XYZ WHERE LENGTH(F2) > 5 AND LENGTH(F2) <= 10;
UPDATE XYZ SET F2 = F2 || 'E' WHERE F1 = '2';
UPDATE XYZ SET F2 = F2 || 'MIR' WHERE F1 = '3';
> SELECT 'UPDATE XYZ SET F2 = ''' || SUBSTR(F2,1,5) ||
> ''' WHERE F1 = ''' || F1 || ''';'
> FROM XYZ WHERE LENGTH(F2) > 10 AND LENGTH(F2) <= 15;
UPDATE XYZ SET F2 = 'ALEXA' WHERE F1 = '4';
> SELECT 'UPDATE XYZ SET F2 = F2 || ''' || SUBSTR(F2,6,5) ||
> ''' WHERE F1 = ''' || F1 || ''';'
> FROM XYZ WHERE LENGTH(F2) > 10 AND LENGTH(F2) <= 15;
UPDATE XYZ SET F2 = F2 || 'NDRET' WHERE F1 = '4';
> SELECT 'UPDATE XYZ SET F2 = F2 || ''' || SUBSTR(F2,11) ||
> ''' WHERE F1 = ''' || F1 || ''';'
> FROM XYZ WHERE LENGTH(F2) > 10 AND LENGTH(F2) <= 15;
UPDATE XYZ SET F2 = F2 || 'TA' WHERE F1 = '4';
Breaking out the output lines, we get:
打破输出线，我们得到：
INSERT INTO XYZ (F1,F2) VALUES (1,'');
INSERT INTO XYZ (F1,F2) VALUES (2,'');
INSERT INTO XYZ (F1,F2) VALUES (3,'');
INSERT INTO XYZ (F1,F2) VALUES (4,'');
UPDATE XYZ SET F2 = 'PAX' WHERE F1 = '1';
UPDATE XYZ SET F2 = 'GEORG' WHERE F1 = '2';
UPDATE XYZ SET F2 = 'VLADI' WHERE F1 = '3';
UPDATE XYZ SET F2 = F2 || 'E' WHERE F1 = '2';
UPDATE XYZ SET F2 = F2 || 'MIR' WHERE F1 = '3';
UPDATE XYZ SET F2 = 'ALEXA' WHERE F1 = '4';
UPDATE XYZ SET F2 = F2 || 'NDRET' WHERE F1 = '4';
UPDATE XYZ SET F2 = F2 || 'TA' WHERE F1 = '4';
which should give you the original rows, albeit in a roundabout way.
这应该为您提供原始行，尽管是以迂回的方式。
And that's about as much effort as I can put into any one question without my brain frying, so I'll bid you adieu unless any serious errors are pointed out to me.
这大约是我在不费脑筋的情况下对任何一个问题所能付出的努力，所以除非有人向我指出任何严重的错误，否则我会向你告别。
Good luck with your project, and best wishes.
祝你的项目好运，并祝你好运。
回答by RDoubleYou
You may use Jailer tool (http://jailer.sf.net) to export table data as INSERT STATEMENTS.
您可以使用 Jailer 工具 ( http://jailer.sf.net) 将表数据导出为 INSERT STATEMENTS。
