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#.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…