search

oracle / oracle-r2dbc

R2DBC Driver for Oracle Database
https://oracle.com
Other
201 stars 42 forks source link

feature: add initSql support #153

Closed kano5252 closed 1 month ago

kano5252 commented 2 months ago

Is it a good idea to add initSql support for each connection from the pool? which means after creating connection, use this connection to run initSql, and put it into pool, in this case, every connection has run the initSql and ready to use.

Here is the real case: I want to use like search, but the Oracle value is case sensitive, you can't get result with keyword "%smith%" while actual record is "SMITH". Changing server side can solve this, but it cost too much.

also using function like "Upper" convert to upper case isn't fit, due to I'm using spring-r2dbc and spring-data-relational (Criteria and Query....) which doesn't support function on field level.

On connection level, we can use 

ALT SESSION SET NLS_COMP=LINGUISTIC
ALT SESSION SET NLS_SORT=BINARY_CI

it works on connection session level, which needs every connection in the pool has run it. so it came up to me with this idea, please kindly advise and if any better solution will be appreciated.

kano5252 commented 2 months ago

Oh, my bad, seems spring-data-relational(version 3.3.3) for Criteria there's a ignoreCase(true) method for this requirement.

after using ignoreCase(true) field like condition will be convert to Upper(field) so no need to set it on connection level.

But InitSql will it help in other cases?

Michael-A-McMahon commented 2 months ago

I see r2dbc-pool supports a "postAllocate" function: https://github.com/r2dbc/r2dbc-pool?tab=readme-ov-file#lifecycle-support

Is this what you need?

Michael-A-McMahon commented 1 month ago

Hope this was resolved. Otherwise, let me know if I can help.