calling an oracle package that returns a ref cursor in asp.net

Question

User702049738 posted

dear all;

I have a function in a package similar to this below. How do i pass the required parameters needed and get the ref cursor back so that i pass it into a datatable. all help is appreciated. thank you

spec
type t_cur is ref cursor;
Function list(p_needed) return t_cur;

body 
Function list(p_needed) return t_cur is

my_t_cur t_cur;

begin 
open my_t_cur for 
select z.name from tbl_one z where z.id = p_needed;
return my_t_cur;
end list;

Answer 1

User269602965 posted

You can add other parameter with direction set to IN direction to your PLSQL package or procedure

VB.NET code in ASP.NET application

    ' Get data from stored procedure '
    Try
      Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString
      Using conn As New OracleConnection(connstr)
        Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcSelectBirdNames", conn)
          cmd.CommandType = CommandType.StoredProcedure
          cmd.Parameters.Clear()
          cmd.Parameters.Add("ListBirdNames", OracleDbType.RefCursor, ParameterDirection.Output)
          conn.Open()
          Using oda As New OracleDataAdapter(cmd)
            Dim ds As New DataSet()
            oda.Fill(ds)
            Me.RadGrid1.MasterTableView.DataSource = ds.Tables(0)
          End Using
        End Using
      End Using
    Catch ex As Exception
    End Try

Oracle PL/SQL code
    
CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME} AS TYPE refCursor IS REF CURSOR;
  PROCEDURE rcSelectBirdNames(ListBirdNames OUT refCursor);
END {PACKAGENAME};
/

CREATE OR REPLACE PACKAGE BODY {PACKAGENAME} AS

PROCEDURE rcSelectBirdNames(ListBirdNames OUT refCursor)
IS
BEGIN
  OPEN ListBirdNames FOR
    SELECT BIRDNAME FROM {SCHEMANAME}.LKUP_BIRDNAME;
END;

END {PACKAGENAME};
/

Answer 2

User702049738 posted

that code doesnt work..i tried exactly what you gave me and it is giving an error saying ora-06550 line 1 column 7. see all my codes below

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;

Answer 3

User269602965 posted

PORT=1221

standard oracle port on installation is PORT=1521

did you change that listenener port to 1221 after install?

 

Answer 4

User702049738 posted

yes i did and it works for all non-ref cursor functions and procedures

Answer 5

User269602965 posted

Have you granted Oracle privilege EXECUTE on the Schema.Package for the USER accessing the package via the application??

Answer 6

User269602965 posted

Also

You really have TWO parameters

The IN value for the WHERE clause p_needed

and

the OUT refcursor t_ref

Oracle command needs both parameters in the C# call

and so does the PLSQL pkg

 

use PROCEDURE instead of FUNCTION

PROCEDURE list(p_needed IN varchar2, t_cur OUT refCursor)

in the C# the parameters must be in the same ORDER as called in the PROC

++++++++++++++

also

good idea to issue

cmd.Parameter.Clear()

before the beginning of listing parameters

 

 

Answer 7

User702049738 posted

well I dont want to use a procedure, i want to learn how to use function instead.

Answer 8

User702049738 posted

yes I have granted the necessary priveledges...

Answer 9

User269602965 posted

well I dont want to use a procedure, i want to learn how to use function instead.

Okay you can return a REFCURSOR from a function

but

1. you still need two parameters, one to pass the IN value and one to return the cursor

for the latter

use

ParameterDirection.ReturnValue

instead of IN OUT or OUT

Answer 10

User269602965 posted

 req_cmd.Parameters.Clear();
 req_cmd.Parameters.Add("p_needed", OracleDbType.Varchar2, ParameterDirection.IN);
 req_cmd.Parameters.Add("MY_T_CUR", OracleDbType.RefCursor, ParameterDirection.ReturnValue);