I don't use Oracle, so I'm not sure, but it's quite likely (and common for many DBMS:es) that a non-SQL command like SET ROLE doesn't support parametrization.
If you need, you can access the "raw" connection from the session and execute using standard OracleClient stuff, and you can catch any exceptions to prevent logging. 2013/11/5 Sander Struijk <[email protected]> > *Why does not named or positional query parameters work with NHibernate in > my case?* > > Consider the following statements to be true: > > *On Oracle database X and Y version 11.2.0.3.0 the role "MyRole" exists* > *identified by "MyPassword" and is granted to the user I am connected* > *as.* > > Here is some code: > > public void SetRole(string roleName, string rolePassword) > { > if (HasRoleBeenSet) return; > try > { > session.CreateSQLQuery("SET ROLE ? IDENTIFIED BY ?") > .SetString(0, roleName) > .SetString(1, rolePassword) > .ExecuteUpdate(); > HasRoleBeenSet = true; > } > catch (Exception ex) > { > Console.WriteLine(ex.Message); > } > } > > > SetRole("MyRole", "MyPassword"); > > > Throws the following Exception: > > NHibernate.Exceptions.GenericADOException: > could not execute native bulk manipulation query: SET ROLE ? IDENTIFIED > BY ? > [SQL: SET ROLE :p0 IDENTIFIED BY :p1] ---> > System.Data.OracleClient.OracleException: ORA-01937: missing or invalid > role name > > > When i use SQLMonitor included in the Toad suite, the SQL sent to the > database looks like this *SET ROLE ? IDENTIFIED BY ?* with the error *Error > occurred: [1937] (ORA-01937: missing or invalid role name)* showing under. > > When I look at FNH's own generated queries with parameters they look like > this: > > SchemaName.errorHandler.logError(:v0); > > :1=['The error message'] > > > But thats not the case when I manually create the query with > CreateSQLQuery() > > okay the next code sample is this: > > ... > session.CreateSQLQuery("SET ROLE :roleName IDENTIFIED BY :rolePassword") > .SetString("roleName", roleName) > .SetString("rolePassword", rolePassword) > .ExecuteUpdate(); > ... > > > Which outputs the following error (The same error): > > NHibernate.Exceptions.GenericADOException: > could not execute native bulk manipulation query: SET ROLE :roleName > IDENTIFIED BY :rolePassword > [SQL: SET ROLE :p0 IDENTIFIED BY :p1] ---> > System.Data.OracleClient.OracleException: ORA-01937: missing or invalid > role name > > > Third code sample: > > ... > session.CreateSQLQuery(string.Format("SET ROLE {0} IDENTIFIED BY {1}", > roleName, > rolePassword)) > .ExecuteUpdate(); > ... > > > On Oracle Database X this works wonders, on Oracle Database Y this does > not work so well and gives me this error: > > NHibernate.Exceptions.GenericADOException: > could not execute native bulk manipulation query: SET ROLE MyRoleIDENTIFIED BY > MyPassword > [SQL: SET ROLE MyRole IDENTIFIED BY MyPassword] ---> > System.Data.OracleClient.OracleException: ORA-00933: SQL command notproperly > ended > > > I tried adding a semicolon *;* to the end of the statement but that gives > invalid character error. > If I add double quotes around the password like this it suddenly works for > Oracle Database Y also. > > ... > session.CreateSQLQuery(string.Format("SET ROLE {0} IDENTIFIED BY \"{1}\"", > roleName, > rolePassword)) > .ExecuteUpdate(); > ... > > > The problem is this is not a very good solution, as FNH now spills out the > password in the exception which gets logged. I have no idea whats the > problem here, there is no clean question here because i don't know what to > ask other then scream for help and hope somebody can shed some light on > this. > > ... > session.CreateSQLQuery(string.Format("SET ROLE {0} IDENTIFIED BY > :rolePassword", > roleName)) > .SetString("rolePassword", rolePassword) > .ExecuteUpdate(); > ... > > > I tried with both :named and ? (positional) parameters, with single > quotes, double quotes, nothing seems to do the trick. > > This code throws the famous > ORA-00933: SQL command not properly ended error > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/nhusers. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers. For more options, visit https://groups.google.com/groups/opt_out.
