Oracle SQL hints are ignored as comments in ScriptUtils [SPR-15389]

Oded Shopen opened SPR-15389 and commented

ScriptUtils executeSqlScript() method has a default comment delimiter of /* and */.

This markers are used by Oracle for hints. For example:

SELECT /*+ DRIVING_SITE (BA) leading (BA S SR) FULL(BA) PARALLEL(BA 4) PARALLEL(S 4) PARALLEL(SR 4) */ BA.CUSTOMER FROM.....

These hints, when added, are crucial for db performance in production. We recently noticed in a production environment that these hints in an SQL file executed by ScriptUtils was ignored. After looking at the code, we saw that the hints were simply removed.

We solved this issue by calling the overloaded executeSqlScript method that takes specific delimiters, and used "fake" delimiters (##/* and */##) in order to get the hints working again. However, I feel this should not be the correct approach as default.

Suggestions would be:

• Implement a different behavior based on the JDBC driver being used: In case of Oracle, don't ignore these "comments".

• Alternatively, Take into account the full /*+ marker as not being a comment (although that might not be a solution for all Oracle hints).

Reference URL: http://stackoverflow.com/questions/33144628/springs-scriptutils-ignores-code-containing-comments-in-dump

Issue Links:

• #17809 Support single quotes nested in double quotes in SQL scripts
• #19999 org.springframework.jdbc.datasource.init.ScriptUtils#splitSqlScript cannot deal with semicolons in stored procedures

Start at ScriptUtils.executeSqlScript() and trace how the default /* and / delimiters process Oracle /+ hints. Review the SQL-script parsing and execution path, then verify that Oracle hints are preserved while ordinary comments still behave as expected. Done means the default path no longer removes these hints.