How do I call Oracle function in c# which takes parameters and return records?

Question

I have an Oracle function, which takes 3 parameters and return records which has four columns.

But I am getting an exception which is

ORA-06550: line 1, column 7:
PLS-00221: 'get_receipts' is not a procedure or is undefined

this is my code:

OracleConnection con = new OracleConnection(oracleDB);
con.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;

cmd.CommandText = "get_receipts";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("p_store_num", store_num));
cmd.Parameters.Add(new OracleParameter("p_create_dt", create_date));
cmd.Parameters.Add(new OracleParameter("p_cc_last4", cc_last4));

OracleDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
    ResponseModel sample = new ResponseModel();

    sample.Field1 = dr.GetString(0);
    sample.Field2 = dr.GetString(1);
    sample.Field3 = dr.GetString(2);
    sample.Field4 = dr.GetString(3);

    response.Add(sample);
}

This may be a duplicate of an existing question: How to call Oracle table function (pipelined function) from .NET — Hilarion
– Hilarion, Commented May 9, 2018 at 16:46

Peter M · Accepted Answer · 2018-05-08 20:16:21Z

1

for oracle it is a little trickier

you have to return the result set as a ref_cursor type from your procedure

check this out

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/oracle-ref-cursors

answered May 8, 2018 at 20:16

Peter M

1925 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

istudent Over a year ago

cmd.Parameters.Add(new OracleParameter("o_return_cur", OracleDbType.RefCursor,int.MaxValue,ParameterDirection.Output)); is it good to pass max int size there? my store procedure return record set. please guide me the best practice to take care of this.

Peter M Over a year ago

honestly I am not sure. here is an oracle doc, it is using DBNull.Value instead of int.MaxValue, try that? docs.oracle.com/cd/B28359_01/win.111/b28375/featRefCursor.htm

istudent Over a year ago

I tried and it worked. It had worked when I had used int max also. But I am going with this one. thank you sir

Hilarion · Accepted Answer · 2018-05-09 17:03:12Z

0

If this is about a tabular function, I'd try writing a query, which uses the function as the sole data source and retrieve the query results.

EDIT: It would be something like this (untested):

SELECT *
  FROM TABLE(get_receipts(:p_store_num, :p_create_dt, :p_cc_last4))

edited May 9, 2018 at 17:03

answered May 8, 2018 at 21:12

Hilarion

8907 silver badges21 bronze badges

1 Comment

istudent Over a year ago

Thank you sir.. I later ask my colleague to change it to store procedure. Now my code is working as I handled ref cursor. please check my above comment. But still I have some doubt. You can check on my comment on Peter post.

Collectives™ on Stack Overflow

How do I call Oracle function in c# which takes parameters and return records?

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related