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