从冷融合到Oracle的数据插入问题

Question

我正在读取一个文本文件，并创建了如下所示的字符串，以将数据插入数据库(Oracle 11g)。

INSERT ALL
INTO INDEX_DATA (DATETIME, COMMODITY, IDX, IDX_VALUE, VCDATE) VALUES ('15-May-2014', 'FN1 Comdty', 'PX_LA', 44.75, SYSDATE)
INTO INDEX_DATA (DATETIME, COMMODITY, IDX, IDX_VALUE, VCDATE) VALUES ('15-May-2014', 'FN1 Comdty', 'PX_H', 45.1, SYSDATE)
INTO INDEX_DATA (DATETIME, COMMODITY, IDX, IDX_VALUE, VCDATE) VALUES ('15-May-2014', 'FN1 Comdty', 'PX_L', 44.47, SYSDATE)
and so on ...7000 rows
select * from dual

coldfusion页面正在超时，同时立即插入上述查询。该查询可用于100条记录。

我真的不确定这是Oracle还是Cold聚变限制，因为蟾蜍也没有响应相同的查询。

如果是coldfusion问题，请告知是否有一种插入数据的替代方法，而不是为每个insert语句访问数据库。

编辑1:如果没有其他解决方案，我可以使用一个标志并插入每个n行。

Answer 1

我建议采取两种行动。首先，将请求超时设置为覆盖默认设置。其次，将insert语句加载到数组中，循环它们，并定期提交。下面是一个简单的示例，演示了这种行为。预期的结果是，在第12条记录出现错误之前，它将在tmp表中插入10个字母。前十名将被承诺。

<cfsetting requesttimeout="3600"><!--- This is the number of seconds that before coldfusion will timeout your request.  Change it to an appropriate value for your situation.  It can't override timeouts on the database side --->
<cfscript>
    sqlStatements=[
"INSERT INTO tmp (letter) values ('a')"
,"INSERT INTO tmp (letter) values ('b')"
,"INSERT INTO tmp (letter) values ('c')"
,"INSERT INTO tmp (letter) values ('d')"
,"INSERT INTO tmp (letter) values ('e')"
,"INSERT INTO tmp (letter) values ('f')"
,"INSERT INTO tmp (letter) values ('g')"
,"INSERT INTO tmp (letter) values ('h')"
,"INSERT INTO tmp (letter) values ('i')"
,"INSERT INTO tmp (letter) values ('j')"
,"INSERT INTO tmp (letter) values ('k')"
,"INSERT INTO tmp (letter) values ('l')"
,"INSERT INTO tmp (letter) values ('m')"
,"INSERT INTO tmp (letter) values ('n')"
,"INSERT INTO tmp (letter) values ('o')"
,"INSERT INTO tmp (letter) values ('p')"
,"INSERT INTO tmp (letter) values ('q')"
,"INSERT INTO tmp (letter) values ('r')"
,"INSERT INTO tmp (letter) values ('s')"
,"INSERT INTO tmp (letter) values ('t')"
];
commitafter="5";
throwafter="12";
qryInsert= new Query();
qryInsert.setDatasource("yourdatasourcename");
transaction{
    statementCount=0;
for (sqlStatement in sqlStatements){
        statementCount=statementCount+1;
        qryInsert.setSQL(sqlStatement);
        qryInsert.execute();
        if (statementCount eq throwafter) {
            throw (message="manual throw for testing");
        }
        if (statementCount MOD commitafter eq 0){
            transaction action="commit";
        }
    }
    transaction action="commit";
}
</cfscript>