.NET /甲骨文：如何使用DDL语句编程执行脚本 [英] .NET / Oracle: How to execute a script with DDL statements programmatically

问题描述

我想要做一些架构的程序化操作对在C＃中的Oracle数据库。因此，我有一些基本问题作斗争。

的DDL SQL语句位于一个脚本文件。我不想用SQLPLUS.EXE，但我想用的OracleCommand出ODP.NET组件（System.Oracle.DataAccess）的。这里是我的脚本文件的例子：

script.sql：

  DROP TABLE ABCDEF; 
 
 DROP TABLE GHIJKL; 
  

我想指出的：

• 脚本中包含DDL语句（数据定义语言）


• 脚本中包含的空行


• 脚本中包含的多个语句

下面的代码应该执行我的脚本：

  VAR内容= File.ReadAllText（script.sql）; 
 
使用（VAR的OracleConnection =新的OracleConnection（_connectionString））
 {
 oracleConnection.Open（）; 
 
使用（VAR命令=新的OracleCommand（内容）{连接=的OracleConnection}）
 {
 command.CommandType = CommandType.Text; 
 command.ExecuteNonQuery（）; 
} 
} 
  

执行这段代码，我得到Oracle错误：

Oracle.DataAccess.Client.OracleException：ORA-00911：无效字符

也许有一些问题与报表的格式，我想。任何暗示表示赞赏。谢谢你。

---编辑---

要总结一下我的以简单的方式的需求：我搜索的方法来执行任意SQL / DDL脚本，即通过SQL Plus可执行文件，以编程方式使用C＃

解决方案

由于@Steve说，分号是造成你的错误。而且你不能完成整个文件到一个单一的立即执行命令，因为这只能同时执行一个语句。你需要分析文件，并自行执行每个命令，去除delinates命令分号。您的分析将要处理字符串，正如你提到的，它除了含有分号也可以包含两个单引号（''），（），它开始和结束字符串的单引号'。

I want to do some programmatical schema manipulation against an oracle database in C#. Therefore, I struggle with some basic issues.

The ddl sql statements are located in a script file. I do not want to use sqlplus.exe, but I want to use OracleCommand out of the ODP.NET assemblies (System.Oracle.DataAccess). Here's an example of my script file:

script.sql:

DROP TABLE ABCDEF; 

DROP TABLE GHIJKL;

I want to point out:

• The script contains DDL statements (data definition language)
• The script contains empty lines
• The script contains more than one statement

The following code should execute my script:

var content = File.ReadAllText("script.sql");

using (var oracleConnection = new OracleConnection(_connectionString))
{
     oracleConnection.Open();

     using (var command = new OracleCommand(content) { Connection = oracleConnection })
     {
          command.CommandType = CommandType.Text;
          command.ExecuteNonQuery();
     }
}

Executing this code, I do get an oracle error:

Oracle.DataAccess.Client.OracleException: ORA-00911: invalid character

Maybe there is some issue with the formatting of the statements, I think. Any hint is appreciated. Thank you.

---EDIT---

To summarize my needs in a simple way: I search for an approach to execute any sql/ddl script, that is executable by SQL Plus, programmatically with C#.

解决方案

As @Steve said, the semicolons are causing your error. And you can't wrap the entire file into a single execute immediate command, since that can only execute one statement at a time. You will need to parse your file and execute each command on its own, removing the semicolon that delinates commands. Your parsing will have to deal with string literals, as you noted, which in addition to containing semicolons may also contain doubled single quotes ('') within the single quotes (') that begin and end the string literal.

这篇关于.NET /甲骨文：如何使用DDL语句编程执行脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！