Java prepared statement parameters for oracle alter session query

Question

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".

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?

score 5 · Accepted Answer · answered Apr 01 '11 at 13:27

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 session call, using a bind variable here will not gain you anything in terms of performance.

As for SQL injection, just validate the name of the collation against a list of collations you support.

score 1 · Answer 2 · answered Apr 01 '11 at 13:42

ALTER SESSION does not require a query plan (which is slow to build), so it must be excuted fast even unprepared.

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.

Java prepared statement parameters for oracle alter session query

2 Answers2