Is there a way to run an arbitrary sql script through ODP.NET? I'd like to do something like this:
SomeOracleObject.RunFile("myfile.sql");
In other words, I want to obviate the need for sqlplus.exe.
-
Normally, a SQL script in Oracle contains SQL and PL/SQL as well as SQLPlus commands. Do you need to support scripts that include SQLPlus commands (SPOOL, BREAK, COLUMN, etc.)? Or do you just want to run a file that contains SQL and PL/SQL commands?Justin Cave– Justin Cave2009-07-27 22:06:47 +00:00Commented Jul 27, 2009 at 22:06
-
Just SQL and PL/SQL commands. These scripts are expressly written by us for this .NET code to run.Josh Kodroff– Josh Kodroff2009-07-29 18:53:37 +00:00Commented Jul 29, 2009 at 18:53
-
So I guess that's not really arbitrary... ;)Josh Kodroff– Josh Kodroff2009-07-29 20:02:32 +00:00Commented Jul 29, 2009 at 20:02
Add a comment
|
2 Answers
I have been looking for a similar solution to the problem. I posted on the Oracle forums for ODP.NET but it appears no one from Oracle actually participates in the forums. The closest thing I have seen to this is DevArt's dotConnect for Oracle's OracleScript class. Ideally I want the same functionality I have inside the SQL Server Management Objects, but for Oracle. If I had my choice, I would move back to SQL Server because of things like this - but it's not my choice to make. :(
Comments
Here's a snippet of code I use to do exactly what you're asking.
using (OracleConnection conn = CreateConnection(confConnString))
{
try
{
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = scriptFileContents;
cmd.ExecuteNonQuery();
