Question , Is any way to set V$Session module, Action , Clientinfo before connection cursor created

[koasi]

Hey Guys
I have a situsation, that we have to set V$session.client_identifier .client_identifier or V$session.PROGRAM when connection created.

I had study few thing

1. in the oracledb sorce code V$session.PROGRAM is get from sys.executable, seems no possible to revise.

2. Set connection.identifier , seems it only alter V$session.client_identifier after cursor created.

[cjbj]

• What's your actual use case for the life of the values? Do they change (when?) or are they fixed for each connection or Python binary invocation?

• Can you use Thin mode?

• 'program' will not be settable in Thick mode, because the OCI libraries don't support this. Technically we could change python-oracledb Thin mode to allow it to be set. The number of 'hackers' out there that want to fake the program name is wearing me down, so we may look at this in some future version.

• python-oracledb lets the driver_name be set in Thick mode via init_oracle_client(). Our task list includes extending this to Thin mode, probably via an oracledb.defaults attribute.

• Regarding setting client_identifier at connection time, it seems a reasonable request for standalone connections but will have to investigate. For pooled connections, since client_identifier values are supposed to be associated with unique 'end users', and connections could be idle in the pool (the value doesn't get reset when connections are released to the pool) I see less value. Thick mode seems to set it on the OCI session attribute, and the value is piggy-backed on future DB requests (this piggy-back is for performance reasons). In Thin mode, @anthony-tuininga did a quick test and found one connection scenario it didn't seem possible to piggyback the value at time of connection. Currently you could execute some round trip (e.g a ping) to send the value across to the DB, however this is going to impact connection time.

[koasi]

1. just wants set system key into Program, action , client_identifier . let database recognize if it's valid and it will not change during connection.
2. I was faced some error , not resolved yet. anyway i can try.
3. thanks for feedback, it's same as my consideration.
4. I had tried driver_name, it's work success to set value when connection created, but due to following reason, i cannot use that

a. limited length of driver_name columns. our sys key is very long.
b. DBA seems disagree to use that due to unknow reason.

5. "standalone connections but will have to investigate"good to hear that, I see the hope, can I expect a possible release date?
   about session_pool, I think it's bind with session should be reason able, also fit our purpose because we plan to use it to identify / verify application.

[cjbj]

Your best bet at the moment is to set the action & client_id after connection, and initiate a round trip to send the value to the DB. (Use a session callback if you are using a connection pool). Hopefully you are not connecting frequently, so the cost of the round trip will be amortized over the life of the connection. If you are creating standalone-connections frequently, then revise your design, perhaps to use a connection pool instead.

[koasi]

I also tried old cx_oracle by session_pool, when connection create, I also cannot set client id at 1st moment.
can only set client_id after pool.acquire().

[cjbj]

@koasi Support for setting program in Thin mode will land in the next release, see oracledb.defaults.program. This doesn't need the application to initiate a round-trip after connection. You can also now set driver_name in Thin mode.

You can download pre-built development wheels to try it out. You can also build from source if you prefer.

[koasi]

@cjbj thanks inform :)

[cjbj]

The planned changes landed in python-oracledb 2.5, which was just released.