Forum Stats

  • 3,784,354 Users
  • 2,254,928 Discussions
  • 7,880,790 Comments

Discussions

calling an oracle package that returns a ref cursor

user13328581
user13328581 Member Posts: 1,327 Silver Badge
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;
This discussion has been closed.