Trying to call a stored procedure from C# (and failing)

[Guest]

Hi;

This is using the Microsoft Oracle ADO.NET classes.

Two questions on this. The first is - is there a way to call a stored
procedure, including passing paramaters, where the entire request is in a
single string? I ask because our app lets users enter any select and so if we
have to break out the parameters, then we will have to parse their select
string and I worry that we will miss some of the select syntax and not always
parse the select correctly.

Second, the following is telling me:
System.Data.OracleClient.OracleException was unhandled
Message="ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of
arguments in call to 'ADD_JOB_HISTORY'\nORA-06550: line 1, column 7:\nPL/SQL:
Statement ignored\n"
Source="System.Data.OracleClient"
ErrorCode=-2146232008
Code=6550
StackTrace:
at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle
errorHandle, Int32 rc)

This is calling ADD_JOB_HISTORY in the XE/HR database. How do I figure out
what it is unhappy with? I have tried every variation I can think of for
this. Latest is:

{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ADD_JOB_HISTORY";

OracleParameter param = new OracleParameter("employee_id", OracleType.Number);
param.Value = 123;
cmd.Parameters.Add(param);
param = new OracleParameter("start_date", OracleType.DateTime);
param.Value = DateTime.Now.AddMonths(-1).Date;
cmd.Parameters.Add(param);
param = new OracleParameter("end_date", OracleType.DateTime);
param.Value = DateTime.Now.Date;
cmd.Parameters.Add(param);
param = new OracleParameter("job_id", OracleType.NVarChar);
param.Value = "SA_MAN";
cmd.Parameters.Add(param);
param = new OracleParameter("department_id", OracleType.Number);
param.Value = 10;
cmd.Parameters.Add(param);

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[WenYuan Wang [MSFT]]

Hello Dave,

1)call a stored procedure in single string.

Have you tried with CommandType.Text? Hope the following method helps.

OracleCommand command = new OracleCommand();
command.CommandText = "execute procedureName('Parameter')";
command.CommandType = CommandType.Text;

2) System.Data.OracleClient.OracleException was unhandled
Message="ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of
arguments in call to 'ADD_JOB_HISTORY'\nORA-06550: line 1, column
7:\nPL/SQL:
Statement ignored\n"
Source="System.Data.OracleClient"
ErrorCode=-2146232008
Code=6550
StackTrace:
at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle
errorHandle, Int32 rc)

This error means the parameter (name/type/number) is wrong.
Is it possible for you paste your stored procure in newsgroup?
We have to check if there is something different between the parameters of
stored procedure and .net code...

Best regards,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hi;

Tried the CommandType.Text - didn't work.

The stored procedure is the sample HR one that comes with Oracle XE. We have
had 2 people look at our parameters vs the procedure and it looks good. How
can we get more details on the error message so it tells us what specifically
it does not like?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Patrice]

As this is a server side issue you could :

- enable the trace to see what if the statement that Oracle is trying to
perform. You can then try this in a query tool to see if you have the same
error and play with this

- not sure about Oracle but if parameter names matters, then you are not
using the proper names :
http://www.schematodoc.com/HRWeb/hr_200610141350_ADD_JOB_HISTORY.html

At least this is how it works with SQL Server i.e. you could use EXECUTE
MyProc @A=1 or EXECUTE MyProc @B=10 for a procedure that accepts two
parameters (@A and @B) but can work with one. Of course if you try EXECUTE
MyProc @C=20 it won't work. Could it be something similar in your case ?

 

[Patrice]

As this is a server side issue you could :

- enable the trace to see what if the statement that Oracle is trying to
perform. You can then try this in a query tool to see if you have the same
error and play with this

- not sure about Oracle but if parameter names matters, then you are not
using the proper names :
http://www.schematodoc.com/HRWeb/hr_200610141350_ADD_JOB_HISTORY.html

At least this is how it works with SQL Server i.e. you could use EXECUTE
MyProc @A=1 or EXECUTE MyProc @B=10 for a procedure that accepts two
parameters (@A and @B) but can work with one. Of course if you try EXECUTE
MyProc @C=20 it won't work. Could it be something similar in your case ?

 

[WenYuan Wang [MSFT]]

Hello Dave,
Thanks for your reply.

For your first issue, I'm not very sure about Oracle database.
However, would you mind trying "call sys.procedureName(parameter)" again?
It seems we should use keyword "call" (not "execute") to invoke a stored
produce in Oracle world.
Does it work on your side?

OracleCommand command = new OracleCommand();
command.CommandText = "call procedureName(Parameter)";
command.CommandType = CommandType.Text

For the second issue, I think we need some time to perform further analysis.
Please wait me one more day. I will update here if I have any information.

If you have any more concern, please let me know. We are glad to assist you.
Best regards,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Got illegal variable number/name.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[WenYuan Wang [MSFT]]

Hello Dave,
Thanks for your wait.

I have reproduced this issue on my side. It seems a product limitation.
However, I figured out a work around: removing "Execute/Call" from SQL
command and setting CommandType to StoredProcedure.

ocd.CommandText = "test.ins_test(8,'pp')";
ocd.CommandType = System.Data.CommandType.StoredProcedure;
ocd.ExecuteNonQuery();

Would you please try the above method and let me know if it works for you?

For the second issue, I suggest we follow the steps as below to trouble
shoot:
1) Check if PL/SQL "execute ADD_JOB_HISTORY ()" works correctly within
Oracle SQL*Plus.

2) If this stored procedure works fine with Oracle SQL*Plus, we need to
hard-code values for parameters and execute it as StroredProcedure.

cmd.CommandText = String.Format("ADD_JOB_HISTORY({0},{1},{2},{3},{4})",
123,
DateTime.Now.AddMonths(-1).Date,
DateTime.Now.Date,
"SA_MAN",
10);
cmd.CommandType = CommandType.StoredProcedure;

If all these steps work fine on your side, would you please send me your
Stored Procedure? Thereby I could reproduce the issue and perform further
analyze.

I'm waiting for you reply. Have a great day. thanks.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hi;

Our customer found a way to use views instead so we are giving up on stored
procedures.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm