Deploying Oracle PL/SQL Packages using a .Net deployment project

Question

I am trying to put together a deployment package for a psolution which needs to configure an Oracle database schema. The schema contains most of the Oracle object types in one form or another.

I want to run the deployment entirely from the deployment application, and have created a custom installation task to perform most of the actions.

I can create tables and views without any problem whatsoever, but I can't create PL/SQL packages through the .Net data access tools.

Can anyone tell me how they have overcome this problem using .Net Framework 2.0, and which Oracle Data Client was used?

Edit: I currently run SQL scripts from the command line, but call these from the installer. This doesn't give the level of feedback I'm looking for, though it does allow me to quickly deploy the application in most circumstances.

I have also had this method fail me on account of the schema I was deploying to not having rights to create triggers.

Answer 1

[Not the answer you are looking for]

My experience with Oracle is that they are "protected" by DBAs. Every DBA I have ever met would baulk at the way you are creating objects in their database.

You may want to keep this is mind and actually do all of the Oracle object creation through straightforward SQL scripts. DBAs like these as they can check them before they run them, then ask you lots of questions as they have no idea what the business requirements are which explains why you have that table design.

This is a really simple solution - I'm sure you're trying to get your install working in one pass through which I understand. You may be expending a lot of effort before someone turns round and tells you that you can't do it that way.

Answer 2

I tturns out the Oracle Data Access component, from Oracle, handles creating packages much better than the Microsoft component.

If I run the appropriate code using the ExecuteNonQuery command, a package, function or procedure can be created.

I am still unable to run full scripts, but I should be able to parse script files to run only the appropriate SQL without a great deal of effort.