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

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.

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




"WenYuan Wang [MSFT]" wrote:


> 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.
> 
> 

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






"David Thielen" <thielen@nospam.nospam> a crit dans le message de news: 
81D4AD28-7728-43EB-989C-1D99F141D0EF@microsoft.com...

> 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
>
> 

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






"David Thielen" <thielen@nospam.nospam> a crit dans le message de news: 
81D4AD28-7728-43EB-989C-1D99F141D0EF@microsoft.com...

> 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
>
> 

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.

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]" wrote:


> 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.
> 
> 

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.