oracle - 导出为插入语句 : But in SQL Plus the line overrides 2500 characters!

Question

我必须将 Oracle 表导出为 INSERT STATEMENTS。

但是INSERT STATEMENTS 如此生成，覆盖了2500 个字符。

我不得不在 SQL Plus 中执行它们，所以我收到一条错误消息。

这是我的 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)
);

假设:

a) 我有义务将表数据导出为 INSERT STATEMENTS；我被允许使用 UPDATE 语句，以避免 SQL*Plus 错误“sp2-0027 input is too long(>2499 characters)”；

b) 我有义务使用 SQL*Plus 来执行如此生成的脚本。

c) 请假设每条记录都可以包含特殊字符:CHR(10)、CHR(13) 等；

d) 我不能使用 SQL Loader；

e) 我无法导出然后导入表:我只能通过 SQL Plus 使用 INSERT/UPDATE 语句添加“增量”。

Answer 1

哇，这些限制非常有限，但我认为可能有办法解决它。我认为您可能必须为此编写自己的小脚本。

我自己会在 JDBC 中使用 Java(但任何可以连接和读取数据库并输出字符串的语言都可以)，编写一个小程序来检索数据库中每一行的记录集。然后，对于这些行中的每一行:

用完整的数据构造一个插入语句。如果这少于 2,000 字节，则只需将其输出到文件并移至下一行。

否则为每个字段创建一个插入语句，但保留 c13字段为 '' (空的)。

那么，只要您的c13input字符串大于2000个字符，输出"update tbl set c13 = c13 || '" + c13input.substring (0,2000) + "' where ..."形式的更新语句(追加接下来的 2000 个字符)然后执行 c13input = c13input.substring(2000)从字符串中去除这些字符。

曾经c13input长度小于或等于2000个字符，只需输出一个最终更新以将其添加到末尾。

这使您可以将各个 SQL 语句保持在 2000 个字符左右，并有效地执行正确的 SQL 以重新填充另一个数据库表。

这是我正在谈论的事情类型(对于仅包含主键 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

显然，您可能需要对字符串进行变形以允许插入特殊字符 - 我不确定 Oracle 期望这些格式是什么，但希望它是传递字符串的简单问题( r.get("c13") 如果完整的长度插入小于 2000、 f.substring(0,2000) 和 f(如果您也在构建更新)到辅助函数来执行此操作。

如果该变形可能会增加打印的行的大小，为了安全起见，您可能希望将阈值降低回 1000，以确保变形字符串不会导致超过 PL/SQL 限制的行。

对不起，如果这看起来令人费解，但您所说的限制让我们有点受阻。可能有更好的方法，但我想不出满足您所有标准的方法。

更新:看起来你比原先想象的更受挫:如果你不得不限制自己使用 SQL 来生成脚本以及运行它，那么有一种方法，尽管它很折磨人。

您可以使用 SQL 生成 SQL。将上述表格与 c1 一起使用和 c13 ， 你可以做:

select
    'insert into newtable (c1,c13) values ("' ||
    c1 ||
    '","");'
from oldtable;
# Xlates to: insert into newtable (c1,c13) values ("[c1]","");

这将为您提供所有基线 insert复制除 c13 之外的所有内容的语句柱子。

然后你需要做的是生成更多的语句来设置 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

然后，到 update c13 适用于 1001 到 2000 个字符之间的所有值(设置然后附加):

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.

对于长度为 2001 到 3000 和 3001 到 4000 的那些，依此类推。

可能需要进行一些调整。我很高兴给你一个解决它的方法，但我想要完成这样一个怪物的愿望充其量是最小的:-)

它会完成工作吗？是的。漂亮吗？我会说这是一个响亮的“不!”但是，鉴于您的限制，这可能是您所能希望的最好结果。

作为概念证明，这里有一个 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;

这会生成以下几行:

> 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';

打破输出线，我们得到:

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';

这应该为您提供原始行，尽管是以迂回的方式。

这大约是我在不费脑筋的情况下对任何一个问题所能付出的努力，所以除非有人向我指出任何严重的错误，否则我会向你告别。

祝你的项目好运，并祝你好运。