dear all, i have an oracle package that contains a function that returns a ref cursor, how do i utilize it in asp.net. see my codes below. I keep getting an error message that says ora-06550 line 1 column 7. all help is appreciated. thank you.
asp.net code below
protected void load_ddl()
{
string addr = "Data Source=(DESCRIPTION ="
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=john-VAIO)(PORT=1221)))"
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));"
+ "User Id = finance; Password=financecc;";
OracleConnection connect = new OracleConnection(addr);
connect.Open();
OracleCommand req_cmd = new OracleCommand();
req_cmd.Connection = connect;
req_cmd.CommandText = "finance.PKG_C.LIST";
req_cmd.CommandType = CommandType.StoredProcedure;
req_cmd.Parameters.Add("MY_T_CUR", OracleDbType.RefCursor).Direction = ParameterDirection.InputOutput;
DataTable dt = new DataTable();
dt.Load(req_cmd.ExecuteReader());
ddl_courses.DataSource = dt;
ddl_courses.DataTextField = "C_NAME";
ddl_courses.DataValueField = "C_NAME";
ddl_courses.DataBind();
connect.Dispose();
connect.Close();
}
oracle code below
create or replace package pkg_c as
type t_cur is ref cursor;
Function list(p_needed varchar2) return t_cur;
end pkg_c;
create or replace body package pkg_c as
Function list(p_needed varchar2) return t_cur is
my_t_cur t_cur;
begin
open my_t_cur for
select z.name as C_NAME from tbl_one z where z.id = p_needed;
return my_t_cur;
end list;
end pkg_c;