0 Replies Latest reply: Dec 10, 2011 6:17 PM by user13328581 RSS

    calling an oracle package that returns a ref cursor

    user13328581
      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;