Invoking an Oracle stored procedure and getting out parameter values

Original:

Http://tech.it168.com/oldarticle/2006-04-02/200604021512359.shtml

Http://www.cnblogs.com/m-cnblogs/archive/2012/02/23/2364906.html

1. Create a test stored procedure:
sql> Create or Replace procedure Proc_test (P1 in varchar2,p2 off varchar2) is
2 begin
3 SELECT p1 into P2 from dual;
4 End Proc_test;
5/

Process is created

2. Main C # code and note points:
Using ORAC = System.Data.OracleClient;
private void Button1_Click (object sender, System.EventArgs e)
{
Try
{
String str_sql = @ "Call Proc_test (:p 1,:p 2)";
/* Not available: Call Proc_test (?,?) or call Proc_test (@p1, @p2), this will be reported ORA-01036: illegal variable name/number error */
ORAC. OracleCommand cmd = new ORAC. OracleCommand (Str_sql,this.oracleconnection1);
/*cmd.commandtype = commandtype.storedprocedure;-Note This method calls the stored procedure and cannot specify CommandType as StoredProcedure */

ORAC. OracleParameter pram1 = new ORAC. OracleParameter ("P1", ORAC. oracletype.varchar,10);
Pram1. Value = "Test";

Cmd. Parameters.Add (PRAM1);

ORAC. OracleParameter pram2 = new ORAC. OracleParameter ("P2", ORAC. oracletype.varchar,10);
Pram2. Direction =parameterdirection.output;

Cmd. Parameters.Add (PRAM2);

if (this.oracleConnection1.State = = System.Data.ConnectionState.Closed)
{
This.oracleConnection1.Open ();
}

Cmd. ExecuteNonQuery ();

This.textBox1.Text = cmd. PARAMETERS[1]. Value.tostring ();
}
catch (Exception ex)
{
MessageBox.Show (ex. Message);
}
Finally
{
This.oracleConnection1.Close ();
}
}

Parameter description

Parameter. Value= Assignment Value

When this value is NULL, cannot be directly assigned, the responsible page will be error: The object is not applied to an instance of the object

Convert to System.DBNull.Value when NULL is required

Invoking an Oracle stored procedure and getting out parameter values