ado.net oledb 調用oracle預存程序返回結果集

資料庫指令碼:

   CREATE TABLE DEPT
   (DEPTNO NUMBER(2,0) NOT NULL,
   DNAME VARCHAR2(14) NULL,
   LOC VARCHAR2(13) NULL,
   PRIMARY KEY (DEPTNO)
   );

   INSERT INTO Dept VALUES(11,'Sales','Texas');
   INSERT INTO Dept VALUES(22,'Accounting','Washington');
   INSERT INTO Dept VALUES(33,'Finance','Maine');

   CREATE TABLE EMP
   (EMPNO NUMBER(4,0) NOT NULL,
   ENAME VARCHAR2(10) NULL,
   JOB VARCHAR2(9) NULL,
   MGR NUMBER(4,0) NULL,
   SAL NUMBER(7,2) NULL,
   COMM NUMBER(7,2) NULL,
   DEPTNO NUMBER(2,0) NULL,
   FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO), 
   PRIMARY KEY (EMPNO)
   );

   INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
   INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
   INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
  
   CREATE OR REPLACE PACKAGE curspkg_join AS
    TYPE t_cursor IS REF CURSOR ;
    Procedure open_join_cursor1 (io_cursor IN OUT t_cursor);
   END curspkg_join;
  
   CREATE OR REPLACE PACKAGE BODY curspkg_join AS
   Procedure open_join_cursor1 (io_cursor IN OUT t_cursor)
   IS
    v_cursor t_cursor;
   BEGIN
    IF n_EMPNO <> 0
    THEN
     OPEN v_cursor FOR
     SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
      FROM EMP, DEPT
      WHERE EMP.DEPTNO = DEPT.DEPTNO
      --AND EMP.EMPNO = n_EMPNO;

    ELSE
     OPEN v_cursor FOR
     SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
      FROM EMP, DEPT
      WHERE EMP.DEPTNO = DEPT.DEPTNO;

    END IF;
    io_cursor := v_cursor;
   END open_join_cursor1;
   END curspkg_join;
  
   --drop table dept;
   --drop table emp;
   --drop PACKAGE curspkg_join; 

 

調用代碼:

OleDbConnection Oraclecon = new OleDbConnection("Provider=MSDAORA.1;Password=tiger;"
              + "User ID=scott;Data Source=OracleServer;Persist Security Info=True");
Oraclecon.Open();
OleDbCommand myCMD =  new OleDbCommand
("{call curspkg_join.open_join_cursor1(?, {resultset 0, io_cursor})}", Oraclecon);
myCMD.Parameters.Add("ID", OleDbType.Numeric, 4).Value = 0;
OleDbDataReader myReader;
myReader = myCMD.ExecuteReader();
int x;
int count;
count = 0;
while (myReader.Read())

{
for (x = 0; x <= myReader.FieldCount - 1; x++)
Console.Write(myReader.GetValue(x) + " ");

     
Console.WriteLine();
count += 1;
}


MessageBox.Show(count + " Rows Returned.");
myReader.Close();
Oraclecon.Close();

 

原文:http://support.microsoft.com/default.aspx?scid=kb;en-us;309361

How To Use a DataReader Against an Oracle Stored Procedure in Visual C# .NET

這裡僅僅摘出了必要的代碼,調試通過! 這裡需要注意的是不是所有類型的列都可以直接select回來,TIMESTAMP類型的列就需要先to_char, 不同的資料類型在oracle裡和.net表示的方法不一樣, 用oledb的時候又不會自動轉換,所以對於特殊的類型會出錯,穩妥起見所有特殊類型都以字串返回.

create or replace procedure sp_sm_administrators_select(
admins_cursor out GLOBALPKG.t_cursor
)
is
begin
  open admins_cursor for
  select
      id,
      user_id,
      user_name,
      user_psw,
      to_char(create_date) as create_date,
      memo
  from sm_administrators
  order by create_date;
end sp_sm_administrators_select;