Executing SQL Plus Batches with Bind Variables from .Net

by Brandon 23. April 2010 21:43

In an earlier post I talked about bind variables in SQL Plus and PL/SQL and only linked to an OTN forum post to show how these bind variables related to .net.  I didn't realize there were a couple of mistakes in that post so i thought I go thru the entire process here.

Using bind variables normally doesn't amount to much more than using the SQL parameters you're probably already familiar with.  The only difference I can think of is the use of a colon instead of the '@' character and that you only use the colon when using it in a parameterized query (you do not use it for stored proc params).  However, with very little SQL Plus experience, i found executing a batch of statements from .net a little tricky.

I suggest starting with a raw SQL Plus / PL/SQL batch and get that working.  In the following example, i wanted to call a stored proc with fewer params than it asked for which required a lookup in a separate table.

var badge_no varchar2(15);
var p_return_success number;
var p_return_string varchar2(4000);
exec :badge_no := '1234';
exec :p_return_success := -1
declare
  l_myKey varchar(50);
begin
  select rf_key into l_myKey from rf_security where badge_no=:badge_no;
  CHECK_BADGE(
    P_BADGE_NO => :badge_no,
    P_KEY => l_myKey,
    P_RETURN_SUCCESS => :p_return_success,
    P_RETURN_STRING => :p_return_string);
exception
  when NO_DATA_FOUND then
    :p_return_success := 0;
    :p_return_string := 'User not found';
end;
/
print badge_no
print p_return_success
print p_return_string

For those of you that aren't familiar with SQL Plus and PL/SQL, the PL/SQL portion of this starts with the 'declare' and ends with end; 

Note: One minor thing that bit me.  If you're using SQL Developer as your query tool, I found that it's a bit buggy with bind variables and output.  For example, I originally had the ':p_return_success := -1 ' inside of the 'begin' block.  That worked as expected in SQL Plus but not in SQL Developer (which is much easier to use).

When you add a parameter like this:

cmd.Parameters.Add("P_BADGE_NO", OracleDbType.Varchar2).Value = AuthHeader.Token.UserId.ToString();

That gets translated into 'var badge_no varchar2' by the provider.  So the statement trimmed down for .net is (assuming you're using Oracle.OracleClient from ODP.net and not System.Data.OracleClient):

      OracleCommand cmd = new OracleCommand(
        @"
        declare
          l_myKey varchar(50);
        begin
          select rf_key into l_myKey from rf_security where badge_no=:badge_no;
          CHECK_BADGE(
            P_BADGE_NO => :badge_no,
            P_KEY => l_myKey,
            P_RETURN_SUCCESS => :p_return_success,
            P_RETURN_STRING => :p_return_string);
        exception
          when NO_DATA_FOUND then
            :p_return_success := 0;
            :p_return_string := 'User not found,';
        end;
        ";
      cmd.Parameters.Add("P_BADGE_NO", OracleDbType.Varchar2).Value = AuthHeader.Token.UserId.ToString();
      cmd.Parameters.Add("P_RETURN_SUCCESS", OracleDbType.Decimal).Direction = ParameterDirection.Output;
      cmd.Parameters.Add("P_RETURN_STRING", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;

      using (cmd.Connection = new OracleConnection(AppConfig.General.ConnectionString))
      {
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
      }

Notice that we left l_myKey variable in.  This variable is only used locally within the batch and we do not need it's value to go in or out.  That's why we declared it as pl/sql variable inside of the declare instead of a SQL Plus variable outside.

However, as is, this will blow up with the following error:

ORA-06550: line 1, column 1:
PLS-00103: Encountered the symbol "" when expecting one of the following

It turns out that the very old SQL Plus has a problem with the windows newline.  I just added a .Replace to my string and it worked from there.

      OracleCommand cmd = new OracleCommand(
        @"
        declare
          l_myKey varchar(50);
        begin
          select rf_key into l_myKey from rf_security where badge_no=:badge_no;
          CHECK_BADGE(
            P_BADGE_NO => :badge_no,
            P_KEY => l_myKey,
            P_RETURN_SUCCESS => :p_return_success,
            P_RETURN_STRING => :p_return_string);
        exception
          when NO_DATA_FOUND then
            :p_return_success := 0;
            :p_return_string := 'User not found,';
        end;
        ".Replace(Environment.NewLine, " "));

 Good Luck,

B

About the authors

Three brothers (yes we're really brothers) all writing and architecting .net solutions.  Our original inspiration for this blog was simply to give the same useful advice we receive from other blogs on a very regular basis.  Lets face it, thanks to the Internet and its contributors, we're a long way away from the days of shifting through a 900 page reference book to find that one line of code.  But lately we've also noticed the shift in the view of application development.   Where once we were the heros of optimization and efficiency, we're now seen a bunch of budget blowing goof-offs that nobody trusts.  We've got some ideas to change that and we'll try to share those thoughts between the short snips of code.