How do I call oracle function in C# ?

Question

0.00/5 (No votes)

See more:

I have an oracle function,which takes 3 parametes 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. 


					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);
                   
                }

What I have tried:

this is my code. 


					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);
                   
                }

Posted 8-May-18 9:08am

istudent

Updated 9-May-18 3:20am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2018-05-08T09:32:00

Solution 1

Quote:
How do I call oracle function in C# ?

The problem is not calling a stored procedure, the problem is that SQL drtbrt don't know the stored procedure you are calling.

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

Message is rather explicit!
Check what stored procedure exist on SQL server.
And correct procedure name or create procedure.

Posted 8-May-18 9:32am

Patrice T

Comments

istudent 8-May-18 16:04pm

That's function name in dB. It exists in dB.

Patrice T 8-May-18 16:09pm

The error message is rather explicit.
Can you list the procedure and copy/paste what the server says ?
There must be some kind of spelling but we can't see your SQL server.

Wendelius · Answer 2 · 2018-05-08T17:17:00

Check a few things:

-Ensure that the function compiles, that you don't get any error messages during compilation.

- If the owner of the function is a different user than the ones that calls it, define the schema in the call, like [schema].[procedure] or create a synonym for the function.

- Ensure that the caller has EXECUTE privileges to the function.

istudent · Answer 3 · 2018-05-09T03:21:00

Hey guys thank you.. I asked the guy who has created function to store proc and made following changes in my code. please let me know if it is good to provide max int size..

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));
cmd.Parameters.Add(new OracleParameter("o_return_cur", OracleDbType.RefCursor,int.MaxValue,ParameterDirection.Output));

How do I call oracle function in C# ?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0