Consider adding Oracle support

[madelson]

Requested by user via nuget.org

Some promising stuff here: https://stackoverflow.com/questions/2627609/is-there-an-equivalent-to-sp-getapplock-sp-releaseapplock-in-oracle

Oracle has a free version for testing here: https://www.oracle.com/database/technologies/appdev/xe.html

[madelson]

May be easier after appveyor/ci#31

[odin568]

Would love to see someday 😀

[madelson]

@odin568 what has held me back in the past is not having a good way to install an Oracle db on my windows machine for doing development. Do you know if this is now supported or do you have another recommended workflow for this?

[odin568]

What should Work is Bildung a docker Container and Run it locally, as described here:
https://github.com/oracle/docker-images/tree/main/OracleDatabase/SingleInstance
Did that in the past for testing

[madelson]

Ok I think I have a basic local setup working:

Container start command:

docker run --name oracle-distributed-lock -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=???? oracle/database:18.4.0-xe

C# snippet:

var cs = new OracleConnectionStringBuilder {
	UserID = "SYSTEM",
	Password = ????,
	DataSource = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)))"
};
var conn = new OracleConnection(cs.ConnectionString);
conn.Open();

var command = conn.CreateCommand();
command.Parameters.Add(new OracleParameter("lockName", OracleDbType.Varchar2) { Value = "test" });
var outputParameter = new OracleParameter("returnCode", OracleDbType.Int32, ParameterDirection.Output);
command.Parameters.Add(outputParameter);
command.CommandText = @"
DECLARE
	lockHandle VARCHAR2(128);
BEGIN	
	DBMS_LOCK.ALLOCATE_UNIQUE(:lockName, lockHandle);
	:returnCode := DBMS_LOCK.REQUEST(
		lockHandle,
		DBMS_LOCK.X_MODE
	);
END;";
command.ExecuteNonQuery();
outputParameter.Value.Dump();

[odin568]

I am Happy to Support you with Testing, Just Ping me

[madelson]

Thanks @odin568 that would be great. It will take me a bit to actually code up the implementation but I'll let you know when a prerelease is available.

In the meantime, if you happen to know how to query for idle sessions (connections that are open but not doing running any commands) in Oracle that would be super helpful; I'll need it for my testing.

[odin568]

There are two main global tables which should have the information: gv$session s, v$process
Found here a page with good examples. Hope that helps :-)

[madelson]

Thanks @odin568!

Another Oracle question for you: do you typically call OracleCommand.Prepare() before executing commands? For some database drivers this is important while for others it isn't.

[odin568]

No, you don't need it, does not have any effect, see https://docs.oracle.com/html/E10927_01/OracleCommandClass.htm
This method is a no-op
Another source: https://stackoverflow.com/a/33667968

[madelson]

Running into issues getting command cancellation to work properly, see oracle/dotnet-db-samples#211

[odin568]

Hmm... Is the answer there a Blocker?

[madelson]

@odin568 sorry thought I had replied here earlier. No it is not a blocker and I'm continuing to progress with the implementation.

However, this will limit the functionality somewhat. The main thing we lose is the ability to cancel lock requests which is pretty valuable. As a workaround for that, I'm thinking of implementing longer lock requests using a loop with chances to check the cancellation token in between.

[madelson]

Update on this:

While I did get oracle working on my machine through docker, I've found the docker-based oracle to be incredibly unreliable. I have to restart the database frequently and constantly encounter issues like connection timeouts. Perhaps there is a fix for this (e. g. some configuration I'm missing). I'd be happy to try things but for now that is blocking further progress.

The other avenue I'm exploring is using oracle cloud's free tier. Unfortunately that is running into issues as well (see oracle/dotnet-db-samples#225). EDIT: Looks like this direction is promising.

For future reference, work done is here: https://github.com/madelson/DistributedLock/compare/oracle?expand=1