call a STORED PROCEDURE (oracle) from C#

[RicercatoreSbadato]

I have a procedure with a lot of parameters:

....
var IN BOOLEAN DEFAULT FALSE,
....

and one variable is a boolean.

How can I put the bool parameter into this stored procedure from c#?

I've tried :

OracleParameter op5 = new OracleParameter("var", 1);
op5.Direction = ParameterDirection.Input;
cmd.Parameters.Add(op5);

but I receive always the same error:

System.Data.OracleClient.OracleException: ORA-06550: riga 1, colonna 7:
PLS-00306: numero o tipi di argomenti errati nella chiamata di 'myproc'
ORA-06550: riga 1, colonna 7:
PL/SQL: Statement ignored

Any idea ?

[Frans Bouma [C# MVP]]

RicercatoreSbadato wrote:

I have a procedure with a lot of parameters:

...
var IN BOOLEAN DEFAULT FALSE,
...

and one variable is a boolean.

How can I put the bool parameter into this stored procedure from c#?

I've tried :

OracleParameter op5 = new OracleParameter("var", 1);
op5.Direction = ParameterDirection.Input;
cmd.Parameters.Add(op5);

but I receive always the same error:

System.Data.OracleClient.OracleException: ORA-06550: riga 1, colonna
7: PLS-00306: numero o tipi di argomenti errati nella chiamata di
'myproc' ORA-06550: riga 1, colonna 7:
PL/SQL: Statement ignored

Any idea ?

BOOLEAN is a surrogate type, you should simply pass a NUMBER value.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

[David Browne]

"RicercatoreSbadato" <em***********@yahoo.it> wrote in message
news:11**********************@y41g2000cwy.googlegr oups.com...

I have a procedure with a lot of parameters:

...
var IN BOOLEAN DEFAULT FALSE,
...

and one variable is a boolean.

How can I put the bool parameter into this stored procedure from c#?

I've tried :

OracleParameter op5 = new OracleParameter("var", 1);
op5.Direction = ParameterDirection.Input;
cmd.Parameters.Add(op5);

but I receive always the same error:

System.Data.OracleClient.OracleException: ORA-06550: riga 1, colonna 7:
PLS-00306: numero o tipi di argomenti errati nella chiamata di 'myproc'
ORA-06550: riga 1, colonna 7:
PL/SQL: Statement ignored

There are a number of PL/SQL types that aren't directly accessable from
..NET. The general solution to those situations is to code a custom PL/SQL
block that marshals .NET compatible types.
Use an OracleCommand with CommandType.Text something like

DECLARE
lnum NUMBER(1) := :pVar;
lvar BOOLEAN;
BEGIN
if lnum = 1
lvar := TRUE;
elsif
lvar := FALSE;
endf;

MyOracleProc(lvar);
END;
Here pVar is a bind variable, and you would bind a numeric OracleParameter
to it.

David

[RicercatoreSbadato]

I've called a function() from a select. This function() calls the
procedure. I've solved the problem in this way. Thanks.