'ORA-00911: invalid character' when SQL statement has semi-colon in the middle of the string

Question

Has anyone encountered java.sql.SQLSyntaxErrorException: ORA-00911: invalid character when making sql.execute('...') (1) call with SQL statement that has ';' (semi-colon) characters in the middle of it. I'm aware that trailing semi-colon causes ORA-00911 as suggested here 2 so am already stripping them out)

Here's an example of one such SQL statement:

INSERT INTO metadata_property_qualifier(id,version,metadata_property_id,name,overridable,propagated,to_instance,to_subclass,translatable,type,value,value_type)
    VALUES (mpr_qualifier_sequence.nextval,0,(select id from metadata_property where metadata_class_id
    IN (select id from metadata_class where name = 'UIM_DXTPMLanSwitchError')
    AND metadata_property.name='CollisNumberValidity'),'ValueMap',0,0,0,0,0,'[Ljava.lang.String;',unistr('0\00a31'),19)

Note that [Ljava.lang.String; has ; character in it. Is there a way to escape semi-colon or workaround it somehow?

Code snippet:

sqlFile.eachLine() {
    log.info it
    if(!it.startsWith('--') && !it.trim().isEmpty()) {
        try {                       
            sql.execute(it - ';')
        } catch(ex) {
            log.error ex
            log.info "Rolling back the transaction..."
            sql.dataSource.connection.rollback()
        }
    }
}

Answer 1

Using - (minus) only replaces the first occurrence. If you have multiple semicolons in your SQL, you'll want to use replaceAll().