从 C#/.Net 在 oracle 中创建和编译“即时”存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5999576/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create and compile "on the fly" stored procedure in oracle from C#/.Net
提问by Gustavo Rubio
I know this has been asked beforebut those solutions seems not to work with me.
我知道以前有人问过这个问题，但这些解决方案似乎对我不起作用。
I'm currently trying to do this:
我目前正在尝试这样做：
- Build a "CREATE PROCEDURE" script string for oracle
- Create a SQL Command using enterprise library 5.0 and pass this string as parameter
- Have the procedure saved in the oracle database
- 为 oracle 构建“CREATE PROCEDURE”脚本字符串
- 使用企业库 5.0 创建 SQL 命令并将此字符串作为参数传递
- 将程序保存在oracle数据库中
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#.
所以到目前为止，第 3 点似乎可以工作，如果我打开 SQL Developer 并展开过程树列表，那么我实际上可以看到该过程，但带有红色十字圆圈，这意味着它虽然运行相同的代码却有编译器错误，这是非常小的, 在 SQL Developer 查询窗口中工作正常并编译它。我不确定 SQL Developer 是否在幕后做了一些事情，因为运行“CREATE PROCEDURE”脚本不会引发任何错误，然后我可以查看并执行该过程，但不能从 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 actually 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...
我在本文开头发布的链接中阅读了我应该调用的内容IDBCommand.Prepare()，但是该方法仅在您的 SQLCommand.CommandType 类型为 StoredProcedure 时才相关，并且由于我实际上没有调用过程而是运行纯 sql 来创建过程然后这行不通，不过，为了测试，我创建了一个新命令，将其分配给 StoredProcedure，然后在该命令上调用 Prepare() 方法，最后是ExecuteNonQuery()抛出对象异常的方法（存储过程）不可用，这是有道理的，因为它尚未编译...
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.
所以，我想知道是否有人真的知道如何创建一个过程并从 C# 编译它，我们目前正在使用企业库，但我不介意直接向 oracle 提供者“硬编码”一些东西，只要它有效。
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.
顺便说一句，在告诉我从代码创建过程是一种不好的做法之前，我告诉你不幸的是，这是一个客户的项目，我们没有做出任何决定，实际上我们正试图将事物从 SQL Server 迁移到 Oracle。
采纳答案by Igby Largeman
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:
我有点生疏，现在没有 Oracle 访问权限，但我想我知道您的要求。您想使用 ADO.NET OracleCommand 类和 Oracle EXECUTE IMMEDIATE 语句。像这样的东西：
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.
那应该有效。但请注意，我一直使用ODP.NET（Oracle 自己的数据提供程序）而不是库存的 ADO.NET，因此如果您只是使用库存库，可能会有一些细微的差异。
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 todo it that way. The above should work if you want to keep it minimal.
正如皮特所建议的，您也可以使用动态 SQL 达到相同的效果。您将整个 CREATE PROCEDURE 语句作为参数传递给现有存储过程，然后该存储过程为您调用 EXECUTE IMMEDIATE。但是，我不相信您必须那样做。如果你想保持最小，上面应该可以工作。
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
另一件事。我不知道为什么会发生这种情况，但是如果 Oracle 告诉您该过程无效，即使您知道它很好，请尝试执行
EXECUTE IMMEDIATE ALTER PROCEDURE schemaname.procedurename COMPILE;
I've often found Oracle to be a bit fragile with regards to invalidating objects unnecessarily.
我经常发现 Oracle 在不必要地使对象无效方面有点脆弱。
回答by Pete
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.
如果要在 SQL Server 或 Oracle 中动态执行 DDL 语句，则必须将在 C# 中创建的 DDL 传递给 Oracle 中的过程，然后该过程将使用 EXECUTE IMMEDIATE 作为命令运行字符串并创建过程。
This pagefrom 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".
Oracle 的这个页面提到，如果“您想执行纯静态 SQL 程序不支持的 DDL 语句和其他 SQL 语句”，则必须使用动态 sql。
I hope this helps you.
我希望这可以帮助你。
回答by Gary Myers
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
Oracle 的原始 SQL*Plus 使用斜杠字符来执行当前命令。许多 IDE 要么使用相同的斜杠，要么将斜杠解释为不是程序/包的一部分
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.
因此，如果您在“CREATE ...”字符串的末尾有一个斜杠（在新行上），那么这将是一个错误。但是，如果由 IDE 加载并重新编译，IDE 可能会为您删除它。
Do a check of USER_ERRORS after the load by C# and see if anything is reported as wrong with that program unit.
在 C# 加载后检查 USER_ERRORS 并查看该程序单元是否报告有任何错误。
