When using an Anonymous PL/SQL block in your ODP.NET application code, one "gotcha" that may arise is setting the CommandType property of an OracleCommand object to an incorrect value. You may be tempted to set the value to CommandType.StoredProcedure. After all, we are talking about PL/SQL, and PL/SQL code means a stored procedure, function, package, etc. of some kind, right? Well, not exactly...
For details on PL/SQL and Anonymous Blocks, I suggest reviewing Chapter 1: Overview of PL/SQL in the Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) available for free on the Oracle Technology Network website. In a nutshell, an Anonymous PL/SQL block is a block of PL/SQL code that is not stored in the database, rather, it is created (or generated) in a client application and submitted to the database-side PL/SQL engine. As such, it is a block of text and not stored code. What is the appropriate CommandType to use when submitting a block of text to the database? CommandType.Text of course!
What happens when you accidentally do set CommandType to CommandType.StoredProcedure? You will see an ugly and somewhat confusing error message similar to:
ORA-06550: line 1, column 246:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
begin case declare end exception 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
The symbol "case" was substituted for "(" to continue.
ORA-06550: line 1, column 261:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
* & = - + < / > at in is mod remainder not rem when
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between overlaps mult
If you wish to see this in action, simply uncomment the following line of code in the sample application code below:
// cmd.CommandType = CommandType.StoredProcedure;
The sample application code uses an Anonymous PL/SQL block to perform a "select into" operation. This code does not use typical objects such as a data reader, etc. Instead, it uses parameter objects in the ODP.NET application code that correspond to the bind variables used in the Anonymous PL/SQL block.
The Sample C# Code:
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace SelectIntoTest
{
class Program
{
// connection string using the "hr" sample user/schema
// be sure to modify as necessary for your environment
const string constr = "User Id=hr;" +
"Password=hr;" +
"Data Source=otndemo;" +
"Enlist=false;" +
"Pooling=false";
// anonymous pl/sql to perform a select into
// bind variables are used for the "output values"
// as well as in the "country_id" in the "where clause"
const string anonymous_block = "begin " +
" select a.country_name, " +
" b.region_name " +
" into :p_country_name, " +
" :p_region_name " +
" from countries a, " +
" regions b " +
" where a.country_id = :p_country_id " +
" and a.region_id = b.region_id; " +
"end;";
static void Main(string[] args)
{
// create and open the connection
OracleConnection con = new OracleConnection(constr);
con.Open();
// create parameter objects for the bind variables in the anonymous pl/sql block
OracleParameter p_country_name = new OracleParameter("p_country_name",
OracleDbType.Varchar2,
40,
"",
ParameterDirection.Output);
OracleParameter p_region_name = new OracleParameter("p_region_name",
OracleDbType.Varchar2,
25,
"",
ParameterDirection.Output);
OracleParameter p_country_id = new OracleParameter("p_country_id",
OracleDbType.Varchar2,
"UK",
ParameterDirection.Input);
// create command object and set CommandText
// CommandType defaults to Text which is correct
// for an anonymous pl/sql block
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = anonymous_block;
// it is incorrect to set CommandType = CommandType.StoredProcedure
// cmd.CommandType = CommandType.StoredProcedure;
// add the parameters to the collection for the command object
cmd.Parameters.Add(p_country_name);
cmd.Parameters.Add(p_region_name);
cmd.Parameters.Add(p_country_id);
// execute the anonymous pl/sql block
cmd.ExecuteNonQuery();
// write the values to the console window
Console.WriteLine("Country Id '{0}' is country '{1}' located in region '{2}'\n",
p_country_id.Value.ToString(),
p_country_name.Value.ToString(),
p_region_name.Value.ToString());
// keep the console window from automatically closing
// such as when running from Visual Studio environment
Console.WriteLine("ENTER to continue...");
Console.ReadLine();
// clean up
p_country_id.Dispose();
p_region_name.Dispose();
p_country_name.Dispose();
cmd.Dispose();
con.Dispose();
}
}
}
Sample Application Output:
Country Id 'UK' is country 'United Kingdom' located in region 'Europe'
ENTER to continue...
