用于 Oracle 更改会话查询的 Java 准备语句参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5513975/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Java prepared statement parameters for oracle alter session query
提问by Andy Dufresne
I tried executing the oracle alter session query for changing the language settings but it fails with an error "ORA-01036: illegal variable name/number".
我尝试执行 oracle alter session 查询以更改语言设置，但失败并显示错误“ORA-01036：非法变量名称/编号”。
preparedStatement = connection.prepareStatement("ALTER SESSION SET NLS_SORT = ?");
preparedStatement.setString(1, "BINARY_CI");
preparedStatement.execute();
Oracle does not allow to bind variables in ddl statements. Since bind variables have a performance gain (in my use case this alter session query would be executed on every connection used in the web application) and it also prevents the application from SQL injection I wanted to use them. If not bind variables is there any other optimized way of executing the above alter session query?
Oracle 不允许在 ddl 语句中绑定变量。由于绑定变量具有性能提升（在我的用例中，此更改会话查询将在 Web 应用程序中使用的每个连接上执行），并且它还可以防止应用程序受到 SQL 注入，我想使用它们。如果没有绑定变量，是否还有其他优化的方式来执行上述更改会话查询？
回答by Mat
Bind variables have a performance gain because query parse/optimizing is done only once, and not at each execution.
绑定变量具有性能增益，因为查询解析/优化只执行一次，而不是每次执行时。
There is no parsing done on that alter sessioncall, using a bind variable here will not gain you anything in terms of performance.
没有对该alter session调用进行解析，在此处使用绑定变量不会在性能方面获得任何好处。
As for SQL injection, just validate the name of the collation against a list of collations you support.
至于 SQL 注入，只需根据您支持的排序规则列表验证排序规则的名称。
回答by 9000
ALTER SESSIONdoes not require a query plan (which is slow to build), so it must be excuted fast even unprepared.
ALTER SESSION不需要查询计划（构建速度很慢），因此即使没有准备也必须快速执行。
If you use any form of connection pooling (and you probably do) this statement needs to execute even less frequently.
如果您使用任何形式的连接池（您可能会这样做），则该语句的执行频率甚至更低。
Use the hardcoded literal form or check the collation name against a list of known collations.
使用硬编码的文字形式或根据已知排序规则列表检查排序规则名称。
