Oracle Database as Session Store

Hi, All.

Purely for posterity, I wanted to capture the steps I’ve taken to get an Oracle database configured as a session store for our .NET project. I had to make a few tweaks to solve several problems along the way. This represents lots of research, tinkering and pouring over trace files, so I hope it helps someone else! Huge thanks to Mitch for getting started down the right path with all of this!

–David H., Dallas, TX

Drivers
I used the Oracle managed data access driver available via NuGet.

Table Definition
The trick here was to substitute a BLOB datatype instead of varbinary(MAX). RAW is too short and LONG RAW is depreciated.

CREATE TABLE SSOSessions (
SessionID varchar2(64) NOT NULL,
UpdateDateTime timestamp NOT NULL,
SessionObject blob NOT NULL
);

Web Config
I used the TNSNAME in the connect string, but you can also use a fully qualified descriptor if you want to avoid TNSNAMES all together.

In settings for the driver, TNS_ADMIN tells the driver to use the tnsnames.ora file from somewhere else on your drive.

This was the biggie. I was getting weird oracle datatype conversion errors. Looking through the driver’s trace files, I saw that the column parameter bindings where getting reversed. I was able to work around this by setting the BindByName option which forces the driver bind the parameters by name rather than by the order in which they’re passed.

Hi David,

Thank you very much for documenting and sharing this. I’m sure it will prove useful to others.