Severe pitfall when using OracleCommandBuilder
Posted: Tue 06 May 2014 17:47
If OracleCommandBuilder instance is created using a parameterless constructor, its property Quoted is false and QuotePrefix and QuoteSuffix are both empty. Thus calling QuoteIdentifier() for that instance doesn't actually quote the supplied identifier. When the (unquoted) result is than used for constructing a query or used as a table name for OracleLoader, the error "ORA-00911: invalid character" can be raised. It means the following (very obvious) construct has a severe pitfall:
If, however, a command builder instance is obtained from a provider factory, its property Quoted is true and QuotePrefix/QuoteSuffix are both set. It means the following code will work without errors:
Please eliminate this pitfall by setting Quoted property as true per default.
By the way: OracleLoader could be more intelligent and fool-proof by quoting its table name itself (if the name is not quoted already). SqlClient.SqlBulkCopy does offer such comfort.
Code: Select all
new OracleLoader(new OracleCommandBuilder().QuoteIdentifier("_log"));
Code: Select all
new OracleLoader(new OracleProviderFactory().CreateCommandBuilder().QuoteIdentifier("_log"));
By the way: OracleLoader could be more intelligent and fool-proof by quoting its table name itself (if the name is not quoted already). SqlClient.SqlBulkCopy does offer such comfort.