Retrieve Data through objAdapter in Oracle

m.kanishka

New member
Joined
Dec 14, 2011
Messages
1
Programming Experience
1-3
I created the Package and Package Body as follows,

create or replace package hrs
as
type t_cursor is ref cursor;
procedure select_employee(p_empno IN NUMBER,cur_employees out t_cursor);
end hrs;


create or replace package body hrs
as
procedure select_employee(p_empno IN NUMBER,cur_employees out t_cursor);
is
begin
open cur_employees for select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where empno = p_empno ;
end select_employee;
end hrs;

================================================
My Problem is When I retrieve the data from Emp Table,Data Table Shows only the Columns of the Query(),there is no values in the Data Table.I think that there is IN put Parameter.Still I couldn't find it.

Pls Show me Where is the Error in My Code:

using System;
using System.Collections.Generic;
using System.Text;
using Oracle.DataAccess.Client;
using System.Data;


OracleDataAdapter objAdapter = new OracleDataAdapter();

OracleCommand objSelectCmd = new OracleCommand();
objSelectCmd.Connection = objConn;
objSelectCmd.CommandText = "hrs.select_employee";
objSelectCmd.CommandType = CommandType.StoredProcedure;

//Input Paramter
OracleParameter prm1 = new OracleParameter("p_empno",OracleDbType.Varchar2);
prm1.Direction = ParameterDirection.Input;
prm1.Value =10;
objSelectCmd.Parameters.Add(prm1);


//output Parameter
objSelectCmd.Parameters.Add("cur_employees", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
objAdapter.SelectCommand = objSelectCmd;

DataTable dtEmp = new DataTable();
objAdapter.Fill(dtEmp);
 
You have to executenonquery the stored procedure then pass the refcursor parameter to the fill method of data adapter:

OraDataAdapter.Fill(dtEmp, oraCmd.parameters["cur_employees"].Value)
 
Back
Top Bottom