Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

calling an oracle package that returns a ref cursor

user13328581Dec 10 2011
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;

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 7 2012
Added on Dec 10 2011
0 comments
1,569 views