0 Replies Latest reply: Dec 11, 2011 12:17 AM by user13328581 RSS

    calling an oracle package that returns a ref cursor

      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);
                  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();
                  ddl_courses.DataSource = dt;
                  ddl_courses.DataTextField = "C_NAME";
                  ddl_courses.DataValueField = "C_NAME";
      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;
      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;