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

我必须将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输入太长(> 2499个字符)”；

b)我不得不使用SQL * Plus执行如此生成的脚本。

c)请假定每个记录都可以包含特殊字符:CHR(10)，CHR(13)等；

d)我不能使用SQL Loader；

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

最佳答案

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

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

使用完整数据构造一个插入语句。如果小于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的语句。要为长度为1000或更短(简单集)的所有值更新c13，请执行以下操作:

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

然后，将1001到2000个字符之间的所有值添加到update 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.

对于长度在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';

这应该会给您原始行，尽管采用环岛的方式。

这是我不需动脑筋就能解决任何一个问题的最大努力，因此，除非有任何严重的错误向我指出，否则我将尽力而为。

祝您项目顺利，万事如意。

关于oracle - 导出为插入语句: But in SQL Plus the line overrides 2500 characters!，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2735942/