How can I deploy a .NET stored procedure without Visual Studio?

user1584631

Oracle supports stored procedures that are written in .NET and can be deployed to Oracle using Visual Studio as described by the manual in Developing and Deploying .NET Stored Procedures.

I'm in the process of doing some technical evaluations and I would like to know if there is a straightforward way for deploying the .NET procedures without Visual Studio, as a part of a standalone installer.

I have already searched it from Google but I have been unable to find neither a definite yes nor a definite no.

So again, does anyone here know if there is a way to deploy .NET stored procedures into Oracle without Visual Studio? If there isn't, please do not hesitate to share your knowledge.

Christian.Shay -Oracle

No there's no API. You have to use the UI to generate the packages.

At the last UI screen there is an option to save the package to disk. Once you have that package, then you need to execute it with SQLPlus or similar and then copy the DLLs to the Oracle Database Extenstions home which defaults to ORACLE_HOME/bin.

Since you are evaluating this, I will say that this is a niche product meant for a small amount of situations where it is required. In most cases, I would not recommend using it and instead suggest keeping  the .NET dlls in the middle tier, or use PL/SQL for your stored procedures.

Christian.Shay -Oracle

Oracle Developer Tools for Visual Studio generates obfuscated PL/SQL packages that get loaded into the database. You can deploy these packages by script or manually (along with the DLLs) but they must always be generated by Oracle Developer Tools - and there's no command line interface to generate them.

So if your question is purely about deployment, then yes it is possible. Just write a script that executes the PL/SQL and copies the DLLs.

If you are talking about modifications to the deployment or to the stored procedures, then no. There's the requirement of using the UI for that.

user1584631

Thank you! A short follow-up question: if packages must always be generated by ODT then does it have some sort of a public API? I mean, if the API is simple and if the licence allows it, then I can certainly consider the option of creating the command line interface myself. All I would need in that case would be some documentation.

user1584631

I was just trying to understand what are the limitations. The deployment option is sufficient.

But where on the disk do the PL/SQL packages and DLL-s end up before the UI deploys them? And where exactly should the DLL-s be copied? The PL/SQL part I understand, or at least I think I do.

Christian.Shay -Oracle

No there's no API. You have to use the UI to generate the packages.

At the last UI screen there is an option to save the package to disk. Once you have that package, then you need to execute it with SQLPlus or similar and then copy the DLLs to the Oracle Database Extenstions home which defaults to ORACLE_HOME/bin.

Since you are evaluating this, I will say that this is a niche product meant for a small amount of situations where it is required. In most cases, I would not recommend using it and instead suggest keeping  the .NET dlls in the middle tier, or use PL/SQL for your stored procedures.

user1584631

Understood. Unfortunately the PL/SQL skillsets of my current team are relatively modest when compared to what I can do with .NET.

I actually have a somewhat acceptable solution already where a couple of small parts are written in PL/SQL but the majority of the business logic lives outside of the database.

The main drawback is that the application has to do constant roundtrips to the database which could be avoided if the functionality lived inside the database itself.

Very roughly, the use case I am facing is that I am consuming an Oracle Advanced Queue which contains command messages about data retrieval tasks to be performed on one or more tables. Unfortunately I cannot do parallel processing when performing those tasks as there is a requirement that the time order of task completions must be exactly the same as the queue order of the original command messages, so a major factor in maximizing the performance is getting the processing logic as close to the database as possible.

I do admit my knowledge of Oracle and its advanced features are quite limited, so it's possible the same result could be achieved more easily using some other approach.

Either way, I am accepting your second answer as the correct one, but if there's anything you'd like to add, feel free to reply.