1 person found this helpful
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.
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.
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.
1 person found this helpful
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.
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.