Ado.net OLE DB calls an Oracle stored procedure returns a result set

Database scripts:

CREATE TABLE DEPT
(DEPTNO number (2,0) not NULL,
Dname VARCHAR2 () NULL,
LOC VARCHAR2 () NULL,
PRIMARY KEY (DEPTNO)
);

INSERT into Dept VALUES (one, ' Sales ', ' Texas ');
INSERT into Dept VALUES (, ' Accounting ', ' Washington ');
INSERT into Dept VALUES (, ' Finance ', ' Maine ');

CREATE TABLE EMP
(EMPNO number (4,0) not NULL,
Ename VARCHAR2 (Ten) 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 the 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;

Calling code:

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; <= myreader.fieldcount-1 x + +)
Console.Write (Myreader.getvalue (x) + "");


Console.WriteLine ();
Count + 1;
}


MessageBox.Show (Count + "Rows returned.");
Myreader.close ();
Oraclecon.close ();

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

DataReader against a Oracle Stored Procedure in Visual C #. NET

Here just pick out the necessary code and debug it through! It should be noted here that not all types of columns can be directly select back, timestamp types of columns need to be to_char, different data types in Oracle and. NET method is not the same, with OLE DB does not automatically convert, So there will be errors for a particular type, and it is safe to return all special types as strings.

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;