Create and compile "on the fly" stored procedure in oracle from C#/.Net

I know this has been asked before but those solutions seems not to work with me.

I'm currently trying to do this:

1. Build a "CREATE PROCEDURE" script string for oracle
2. Create a SQL Command using enterprise library 5.0 and pass this string as parameter
3. Have the procedure saved in the oracle database

So point #3 seems to work so far, if I open SQL Developer and expand the procedure tree list then I can actually see the procedure but with the red cross circle which means it has compiler errors though running this same code, which is pretty small, in a SQL Developer query window works fine and compiles it. I'm not sure in SQL Developer does something under the hood because running that "CREATE PROCEDURE" script throws no error whatsoever, I can then see and execute the procedure but not so doing it from C#.

I read in the link posted in the beginning of this that I should call IDBCommand.Prepare(), but that method is only relevant if your SQLCommand.CommandType is of type StoredProcedure and since I'm not calling a procedure ac开发者_如何学运维tually but running pure sql to CREATE the procedure then this wouldn't work, nevertheless, I, just for the sake of testing, created a new command which I assigned to StoredProcedure and then called the Prepare() method on that command, finally the ExecuteNonQuery() method which throws an exception of the object (the stored procedure) not being available which makes sense since it hasn't been compiled...

So, I wonder if anybody actually knows how you can create a procedure and compile it from C#, we are currently using enterprise library but I wouldn't mind "hard coding" something directly to the oracle provider as long as it works.

Btw, before telling me that creating a procedure from the code is a bad practice I tell you that unfortunely this is a customer's project and we had no decisions on the making, actually we are trying to migrate the thing from SQL Server to Oracle.

I'm a bit rusty and have no Oracle access right now, but I think I know what your'e asking for. You want to use the ADO.NET OracleCommand class and the Oracle EXECUTE IMMEDIATE statement. Something like this:

        OracleConnection conn = new OracleConnection("Data Source=something; user id=something; Password=something;");
        conn.Open();
        OracleCommand command = new OracleCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = "execute immediate 'create or replace procedure [...you know this part...]' ";
        command.Connection = conn;
        command.ExecuteNonQuery();
        conn.Close();
    }

That should work. But note that I've always used ODP.NET (Oracle's own data provider) instead of the stock ADO.NET one, so there may be some slight differences if you're just using the stock library.

As Pete suggested you could also use dynamic SQL to the same effect. You'd pass the whole CREATE PROCEDURE statement as a parameter to an existing stored procedure which then calls EXECUTE IMMEDIATE for you. However I don't believe that you have to do it that way. The above should work if you want to keep it minimal.

One other thing. I don't know why this would be happening, but if Oracle is telling you the procedure is invalid even though you know it's fine, try executing

EXECUTE IMMEDIATE ALTER PROCEDURE schemaname.procedurename COMPILE;

I've often found Oracle to be a bit fragile with regards to invalidating objects unnecessarily.

If you want to execute DDL statements dynamically in either SQL Server or Oracle, you have to pass the DDL that you create in C# to a procedure in Oracle that will then use EXECUTE IMMEDIATE to run the string as a command and create the procedure.

This page from Oracle mentions that you'll have to use dynamic sql if "You want to execute DDL statements and other SQL statements that are not supported in purely static SQL programs".

I hope this helps you.

What's the final character ?

Oracle's original SQL*Plus uses a slash character to execute the current command. A lot of IDEs either use the same or interpret the slash as not being part of the procedure/package

So if you have a slash (on a new line) as the end of your 'CREATE ...' string, then it would be an error. However if loaded by the IDE and recompiled, the IDE might strip it out for you.

Do a check of USER_ERRORS after the load by C# and see if anything is reported as wrong with that program unit.