C # displays the cursor dataset returned by the stored procedure
1.-- Oracle creates a package to declare custom cursor types
Create or replace package pk_Var is
Type csr_tp is ref cursor
End
2.-- Oracle creates a stored procedure to return a data set
CREATE OR REPLACE PROCEDURE pr_getdata (
V_empno VARCHAR2, RC1 out PK_Var.csr_tp)
AS
SSQL varchar2 (2000)
Begin
SSQL: = 'select * from emp where empno=v_empno'
Open RC1 for sSQL
Return
End pr_getdata
3. The c # code is as follows:
/ / create oracle parameters
OracleParameter [] sqlParme = new OracleParameter [2]
/ / Parameter 1
SqlParme [0] = new OracleParameter ("RC1", OracleType.Cursor)
SqlParme [0] .Direction = ParameterDirection.Output
SqlParme [1] = new OracleParameter ("TKALA", OracleType.VarChar)
SqlParme [1] .Direction = ParameterDirection.Input
SqlParme [1] .value = aHoleOldNum
/ / get dataTable data sources from stored procedures
Private DataTable DBExecStoredProcedure (string storeureName, OracleParameter [] sqlParme)
{
Try
{
/ / use Microsoft's ORACLE access interface
If (DBHelper.G_pDBConn.State = = ConnectionState.Closed) / / get a data connection
DBHelper.G_pDBConn.Open ()
OracleCommand oraCmd = new OracleCommand (storeureName, DBHelper.G_pDBConn)
OraCmd.CommandType = CommandType.StoredProcedure
OraCmd.Parameters.Clear (); / / empty first
Foreach (OracleParameter parme in sqlParme)
{
OraCmd.Parameters.Add (parme)
}
DataTable table = new DataTable ()
DateTime BegTime = System.DateTime.Now
OracleDataAdapter da1 = new OracleDataAdapter (oraCmd); / / fetch data
Da1.Fill (table)
Return table
}
Catch (Exception ex)
{
MessageBox.Show ("failed to execute stored procedure:" + ex.Message, "debug")
Return null
}
Finally {
DBHelper.G_pDBConn.Close ()
}
}
