In the previous article, we introduced the method of creating PLSQL package. This paper introduced the method of calling stored functions and stored procedures in packages in C. Taking get_emp_func function encapsulated in emp_pack ages as an example, we introduced the method of calling this function in C.

First, emp_pack inclusions are defined as follows:

create or replace 
package body emp_pack
as
  function get_emp_func(p_dno dept.deptno%type) return sys_refcursor
  as
   v_cur sys_refcursor;
  begin
   open v_cur for select * from emp where deptno=p_dno;
   return v_cur;
  end;
  procedure insert_emp(v_empno emp.empno%type,v_ename emp.ename%type,v_job emp.job%type,v_mgr emp.mgr%type,v_sal emp.sal%type,v_deptno emp.deptno%type)
as 
 v_count number;
begin
  select count(*) into v_count from emp where empno=v_empno;
  if v_count>0 then
   raise_application_error(-20789,'Increase failure, the sector already exists');
  else
   insert into emp values(v_empno,v_ename,v_job,v_mgr,sysdate,v_sal,null,v_deptno);
  end if;
  commit;
exception
  when others then
      dbms_output.put_line('sqlerrm='||sqlerrm);
      rollback;
end;
end;

In our package, get_emp_func is defined as a function that receives a numbered department number as a parameter and returns the reference cursor queried in the EMP table. The effect we achieve in C# is to call the function, get the cursor, and read the data set data managed by the cursor. Refer to the following code:

         try
            {
                OracleParameter[] parameters = {
                   new OracleParameter("ReturnValue", OracleDbType.RefCursor, 0, ParameterDirection.ReturnValue, true, 0, 0, "",DataRowVersion.Default, Convert.DBNull ),
                   new OracleParameter("p_dno", OracleDbType.Int32,1)
                };
                parameters[1].Value = deptno;
                OracleCommand orc = conn.CreateCommand();
                orc.CommandType = CommandType.StoredProcedure;
                orc.CommandText = "EMP_PACK.GET_EMP_FUNC";
                foreach (OracleParameter param in parameters)
                    orc.Parameters.Add(param);
                orc.ExecuteNonQuery();

                Oracle.ManagedDataAccess.Types.OracleRefCursor cur = parameters[0].Value as Oracle.ManagedDataAccess.Types.OracleRefCursor;
                OracleDataReader reader = cur.GetDataReader();
                
                //The first traversal method
                foreach (System.Data.Common.DbDataRecord obj in reader)
                {
                    object[] values = new object[obj.FieldCount];
                    obj.GetValues(values);
                }

                //The second traversal method
                {
                    System.Collections.IEnumerator enumrator = reader.GetEnumerator();
                    enumrator.MoveNext();
                    System.Data.Common.DbDataRecord obj = enumrator.Current as System.Data.Common.DbDataRecord;
                    while (obj != null)
                    {
                        object[] values = new object[obj.FieldCount];
                        obj.GetValues(values);
                        enumrator.MoveNext();
                        obj = enumrator.Current as System.Data.Common.DbDataRecord;
                    }
                
                }
               
                
            }
            catch (System.Exception ex)
            {

            }

(1) Similar to calling a normal memory function, we place the return value of the function in the first element of the parameters, and the second element begins to place the parameters required by the function.

(2) Line 10, the name of the function to be invoked is passed in, where we need to add the name of the package we defined.

(3) Lines 15 and 16 acquire the type of cursor returned by the storage function. We need to read the data set managed by the cursor, and get the OracleDataReader object of the cursor on line 16. The next operation is to get the data through the object.

(4) OracleDataReader object inherits IEnumerator, so we list two ways to traverse it. One is using foreach, the other is using GetEnumerator() method inherited from IEnumerator and so on. Through traversing, we can get the data set data managed by the cursor object.