Data Insert Issue from Coldfusion to Oracle

Question

I am reading a text file and have created a string as below to insert data into the Database (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

The coldfusion page is timing out while inserting the above query at once. The query works fine for 100 records.

I'm really not sure if this is an Oracle or Coldfusion limitation since Toad is also not responding for the same query.

In case its a coldfusion issue, please advise if there is an alternate way of inserting the data than hitting the database for each insert statement.

Edit 1: I can use a flag and do an insert every n rows, if no other solution exists.

Answer 1

I would recommend 2 actions. First set the request time out to override your default settings. Second, Load up your insert statements into an array, loop over them, and commit at regular intervals. Here is a simple example that demonstrates the behavior. Expected results are that it would insert 10 letters into the tmp table before an error on the 12th record. The first 10 would be committed.

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