A useful but not widely publicized feature of the Oracle.ODP database provider for .NET is the ability to execute SQL scripts as a single command. It's quite easy to use the capability but there are a few caveats that are worth being aware of to save time and frustration.

Let's start by looking at a simple example of executing a single SQL statement using ODP:

using System.Configuration;
using Oracle.DataAccess.Client;

namespace OracleODP
{
    public class SingleStatementSample
    {
        public static void Main(string[] args)
        {
            var sql = "INSERT INTO TEST_TABLE( NKEY, STEXT ) VALUES( 123, 'HELLO WORLD' )";

            var connString =  ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
            using (var conn = new OracleConnection(connString))
            using (var command = new OracleCommand(sql, conn))
            {
                conn.Open();
                command.ExecuteNonQuery();
            }           
            
        }
    }
}
 

Nothing terribly surprising here. We get a connection string from configuration, create a new OracleConnection, and a new OracleCommand with a simple INSERT statement as the command text.

So how do we run multiple statements in a single command? Well, a reasonable first guess may be to add multiple statements directly to our SQL string and separate them with the typical ";" separator:

  var sql = @"INSERT INTO TEST_TABLE( NKEY, STEXT ) VALUES( 123, 'HELLO WORLD' );
              INSERT INTO TEST_TABLE( NKEY, STEXT ) VALUES( 456, 'HELLO AGAIN' );";
 

Unfortunately, this won't work with Oracle; try it and you'll get an error that looks something like:

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

Informative, isn't it? Fortunately, the solution is quite simple: surround the statements in an anonymous PL/SQL block:

 var sql = @"BEGIN
            INSERT INTO TEST_TABLE( NKEY, STEXT ) VALUES( 123, 'HELLO WORLD' );
            INSERT INTO TEST_TABLE( NKEY, STEXT ) VALUES( 456, 'HELLO AGAIN' );
            END;";
 

Can it really be that simple? Well, yes ... and no. Using anonymous blocks comes with some considerations that you should be aware of.

Line Break Considerations

Surprisingly, Oracle can be quite picky about what characters it accepts as legitimate line breaks. In Windows, a line break is typically a carriage return (CR), line-feed pair (LF) pair; while on Unix and Linux platforms, line breaks are represented just by a carriage return. Unfortunately, depending on where your script content originates, you may inadvertently have CR/LF pairs in it. Most often, this would be the case if you read your SQL script from a file or an embedded resource - where the normal encoding would use CR/LF. 

Unfortunately, the error message you get from Oracle isn't very helpful if you run into this situation:

  Initialization method Tests.AnonymousBlock.TestScript threw exception:
    Oracle.DataAccess.Client.OracleException:
    Oracle.DataAccess.Client.OracleException: ORA-06550: line 1, column 6:

  PLS-00103: Encountered the symbol "" when expecting one of the following:

   begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe.
 

While I haven't been able to find any definitive documentation, empirical testing shows that Oracle will accept a CR as line break but not necessarily a CR/LF pair. To be safe, I replace CR/LF pairs with CRs before using them:

  command.CommandText = sql.Replace("\r\n", "\n");
  

DDL vs. DML Considerations

Another factor that can come into play is when trying to execute DDL statements in a PL/SQL block rather than DML statements. DDL consists of statements that describe or modify data structure rather than their contents. Oracle does not support executing DDL statements directly within an anonymous block. Surprisingly, some statements that you may think are DML are actually DDL - the best example of this the TRUNCATE command:

  TRUNCATE TABLE IBS_ROUTED_EVENT;
 

The above code looks like regular data manipulation language, but it's actually DDL and cannot be executed in an anonymous block. If you try you'll get something like:

 Oracle.DataAccess.Client.OracleException:  
   Oracle.DataAccess.Client.OracleException: ORA-06550: line 4, column 12:

   PLS-00103: Encountered the symbol "TABLE" when expecting one of:
     := . ( @ % ;
 

...another case of a less than helpful error message. Fortunately, all is not lost. It's possible to execute DDL within an anonymous block using the EXECUTE statement:

  EXECUTE IMMEDIATE 'TRUNCATE TABLE IBS_ROUTED_EVENT';
 

The DDL statements in Oracle include: ALTER, COMMENT, CREATE, DROP, RENAME, REPLACE, and TRUNCATE. The EXECUTE IMMEDIATE statement accepts a dynamic SQL statement as a string, hence the need for single quotes around the SQL to run.

One important consideration when executing DDL (whether in an anonymous block or otherwise) is that DDL always performs in implicit commit. This is a critical fact to keep in mind - when you execute DDL any pending work will automatically be committed. Once this happens, you cannot roll back or undo anything prior to the DDL statement. Consequently, I generally advise developers to avoid using DDL in SQL - there's usually a better alternative.

Returning Values From Anonymous Blocks

The last topic we will look at is how to return results from an anonymous block. It turns out, Oracle.ODP makes this relatively easy, if a bit unintuitive.

The OracleCommand class allows parameters to be attached to the executed statement - parameters may be either input, output, or both. Adding a parameter can be done directly through the OracleCommand object. Here's an example that uses both input and output parameters:

    public static void Main(string[] args)
    {
        var sql = @"BEGIN 
                      INSERT INTO TEST_TABLE( NKEY, STEXT ) VALUES( SEQ1.NEXTVAL, :value1 )
                        RETURNING NKEY INTO :key; 
                    END;";

        var connString = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
        using (var conn = new OracleConnection(connString))
        using (var command = new OracleCommand(sql, conn))
        {
            conn.Open();
            
            // add parameters
            var p1 = command.Parameters.Add(":value1", "Hello World");
            // make the :key parameter an output param
            var p2 = command.Parameters.Add(":key",null);
            p2.Direction = ParameterDirection.Output;
            p2.OracleDbType = OracleDbType.Int32;

            command.ExecuteNonQuery();

            // print the returned value...
            Console.WriteLine("The new record's key is {0}", p2.Value);
        }

    }

The example above uses the RETURNING ... INTO ... syntax to allow a sequence-assigned value to be returned to the caller. This is a useful technique that allows any value computed at the database to be returned to your .NET code. Sweet!

Why Execute Statements as a Script?

Now that we've looked at how we can execute SQL scripts, let's take a brief look at why we may choose to do so. Executing multiple SQL statements as a script offers a number of advantages:

• It performs all of the work in a single trip to the database
• It automatically treats all of the work as a single transaction
• It simplifies the .NET code you write to execute several commands
• It allows bind variables to apply to multiple statements
• It allows results of a computation or SQL operation to be cached
• It allows you to mix regular SQL with PL/SQL statements

Most of these benefits should be self-explanatory - but a few deserve a little more.

When code is combined into a single anonymous block, any bind variable are available in all SQL statement within that block. As a result, it's possible to reuse a single bind variable - which simplifies both the SQL code and the .NET code that prepares and executes it. If you do choose to reuse bind variable, make sure that they are input-only. Output bind variables cannot be reused.

An anonymous blocks may optional include declarations of local variables - variables that may be initialized or set by statements within the block. Not to be confused with bind variables, local variables must be defined in their own DECLARE section which appears before BEGIN:

 DECLARE
   -- declare record variable
   dept_rec dept%ROWTYPE;  
   -- declare cursor variable
   CURSOR c1 IS SELECT ename, sal, hiredate, job FROM emp;
   -- declare simple variable
   empSalary REAL(7,2);
 

Mixing SQL and PL/SQL in an anonymous block is convenient, however, generally what we want to do is repeat the same operation several times with different values. An example is inserting several records into the database in one trip. While we can do this by expanding the SQL directly into the anonymous block, a better approach is to use array binding to pass multiple parameters to the database. This technique will be the topic of a future blog post. For now, take a look at the linked Oracle article, and keep in mind that bind variables are the preferred technique to pass parameters to Oracle - they reduce SQL injection risks and allow the database to avoid parsing SQL statements over and over again.

The Wrap Up

So let's review what we've learned :

1. Oracle ODP allows you to execute multiple statements together in a single database using an anonymous block.
2. Anonymous blocks are sensitive to line-break encoding, so replace your CR/LF pairs within a single CR character to avoid problems.
3. Anonymous blocks support mixing SQL and PL/SQL statements, and can even include DDL statements using the EXECUTE IMMEDIATE.
4. Anonymous blocks can use local variables defined in the DECLARE section, allowing expensive data to be computed once and cached.
5. Anonymous blocks support both input and output parameters, allows results to be returned back to the caller.