'Executing a long insert statement through JDBC

I have a backup file structured as INSERT statements. My approach was writing a method to load theses statements from the file and use JDBC template to execute them.

public void restoreFile(File f) throws Exception {
    LineIterator it = FileUtils.lineIterator(f, "UTF-8");
    try {
        while (it.hasNext()) {
            String insertStatement = it.nextLine();
            if (!insertStatement.startsWith("--") && !insertStatement.isEmpty()) {
                insertStatement = insertStatement.replace(";", "");
                executeSqlStatement(insertStatement);
            }
        }
    } finally {
        it.close();
    }
}

public void executeSqlStatement(String sqlStatement) throws Exception {

    PreparedStatementCreator statement = new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement statement = connection.prepareStatement(sqlStatement);
            return statement;
        }
    };
    getJdbcTemplate().update(statement);
}

This seemed to work fine in a few cases. However, some of the values I'm inserting are giant XML strings (some XMLs being above 10K characters).

When I run the update function, I get the following error with several files:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-01704: string literal too long

On the other hand, if I run the same script in SQL Developer, this error doesn't happen.

I'm aware that I should be using the prepared statement with bind vars but since the demand was having the file stored as inserts, I'd rather not having to parse the entire file. I want to simply execute each line as it is.

Is there a workaround for this?

EDIT: Also, how does SQL Developer deal with these long string literals?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source