I want to change the NLS_SORT and NLS_COMP parameters for every query to this particular schema, but there are other users on this system as well that want to keep the original values, so using ALTER SYSTEM SET is a no-no in this case. I also don't want to change every query to this particular schema.
Is there a way to either put these values in an initialization file that is particular to only this schema or can I somehow add a trigger that sets these values on the session whenever a session is started to this schema?
I am running Oracle Express 11G R2 and the solution does not need to be backwards compatible.
My goal is to not have to run the ALTER SESSION SET rows before running the SELECT-LIKE-statement and having it produce two results rather than one. Here is the Java sample code that I've used to examine what values I actually want the NLS_COMP and NLS_SORT values to have:
public class OracleCaseTest {
public static void main(String[] args) throws SQLException {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
dataSource.setUrl("jdbc:oracle:thin:@localhost:1521:xe");
dataSource.setUsername("CASETEST");
dataSource.setPassword("casetest");
Connection conn = null;
PreparedStatement createStatement = null;
PreparedStatement populateStatement = null;
PreparedStatement comparisonAlterSessionStatement = null;
PreparedStatement sortAlterSessionStatement = null;
PreparedStatement queryStatement = null;
PreparedStatement deleteStatement = null;
ResultSet rs = null;
conn = dataSource.getConnection();
createStatement = conn
.prepareStatement("CREATE TABLE CollationTestTable ( Name varchar(255) )");
createStatement.execute();
try {
// comparisonAlterSessionStatement = conn
// .prepareStatement("ALTER SESSION SET NLS_COMP=LINGUISTIC");
// comparisonAlterSessionStatement.execute();
//
// sortAlterSessionStatement = conn.prepareStatement("ALTER SESSION SET NLS_SORT=BINARY_CI");
// sortAlterSessionStatement.execute();
String[] names = { "pepe", "pépé", "PEPE", "MEME", "mémé", "meme" };
for (String name : names) {
populateStatement = conn
.prepareStatement("INSERT INTO CollationTestTable VALUES (?)");
populateStatement.setString(1, name);
populateStatement.execute();
}
queryStatement = conn
.prepareStatement("SELECT Name FROM CollationTestTable WHERE NAME LIKE 'pe%'");
rs = queryStatement.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
}
} finally {
deleteStatement = conn.prepareStatement("DROP TABLE CollationTestTable");
deleteStatement.execute();
}
}
}
I'm aware of the problem with full table scans without linguistic indexes that this might create, but ignore that for this question.
UPDATE: This is the statement I used to create my trigger from the SQL command-line interface (after connecting and logging in with my user):
create or replace trigger nls_settings
after logon on schema
begin
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=LINGUISTIC';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=BINARY_CI';
end nls_settings;
/
Also changed the original question to indicate that by "database", in the Oracle world I really meant "schema"/"user".
