C # calling the Oracle Stored Procedure

This article mainly describes how to use C # To Call The Oracle stored procedures, as well as the returned results, related result sets, and functions. First, we will briefly describe how to create a PACKAGE and package body in Oracle, the function and stored procedure return result set are defined here.

1: Create a PACKAGE:

 
 

  
  
CREATE OR REPLACE package SCOTT.pk_wt  
  
  
is  
  
  
type mytype is ref cursor;  
  
  
procedure p_wt(mycs out mytype);  
  
  
function f_get(str in varchar2)  
  
  
return varchar2;  
  
  
end;   
  
  
/ 
 
 

Note: PACKAGE is just a declaration. Here we define an Oracle stored procedure to return a collection and a function to return a string.

2: Create a package body:

 
 

  
  
CREATE OR REPLACE package BODY SCOTT.pk_wt  
  
  
is  
  
  
procedure p_wt(mycs out mytype)  
  
  
is  
  
  
begin  
  
  
open mycs for select * from test;  
  
  
end p_wt;  
  
  
function f_get(str varchar2)  
  
  
return varchar2  
  
  
is  
  
  
str_temp varchar2(100) := 'good luck!';   
  
  
begin  
  
  
str_temp := str_temp || str;  
  
  
return str_temp;  
  
  
end f_get;  
  
  
end pk_wt;  
  
  
/  
 
 

Note: the creation of the package body here is a specific description and use, and how will the package body be implemented.

C # section:

In C #, the code is divided into two parts: Using Functions and using result sets.

Define a connection and get it from WEBCONFIG:

 
 

  
  
private OracleConnection orcn=new 
OracleConnection(System.Configuration.
ConfigurationSettings.AppSettings["scott"]); 
 
 

C # Call Oracle functions:

 
 

  
  
OracleCommand cmd=new OracleCommand("pk_wt.f_get",orcn);  
  
  
cmd.CommandType=CommandType.StoredProcedure;  
  
  
OracleParameter p1=new OracleParameter("str",OracleType.VarChar,10);  
  
  
p1.Direction=System.Data.ParameterDirection.Input;  
  
  
p1.Value=this.TextBox1.Text;  
  
  
OracleParameter p2=new OracleParameter("result",OracleType.VarChar,100);  
  
  
p2.Direction=System.Data.ParameterDirection.ReturnValue;  
  
  
cmd.Parameters.Add(p1);  
  
  
cmd.Parameters.Add(p2);  
  
  
orcn.Open();  
  
  
cmd.ExecuteNonQuery();  
  
  
orcn.Close();  
  
  
this.Button_function.Text=p2.Value.ToString(); 
 
 

The RESULT is the return variable of the system-defined function. Note that the return type of the function parameter must be specified, and the return type of the COMMAND type must also be specified, in addition, there is no difference with General Oracle stored procedures.

C # Call the Oracle return result set:
 

 
 

  
  
OracleCommand cmd=new OracleCommand("pk_wt.p_wt",orcn);  
  
  
cmd.CommandType=CommandType.StoredProcedure;  
  
  
OracleParameter p1=new OracleParameter("mycs",OracleType.Cursor);  
  
  
p1.Direction=System.Data.ParameterDirection.Output;  
  
  
cmd.Parameters.Add(p1);  
  
  
OracleDataAdapter da=new OracleDataAdapter(cmd);  
  
  
DataSet ds=new DataSet();  
  
  
da.Fill(ds,"test");  
  
  
this.DataGrid1.DataSource=ds;  
  
  
this.DataGrid1.DataBind(); 
 
 

The above content is the description of C # calling the Oracle stored procedure, hoping to help you in this regard.