run oracle stored procedure from c#

[nicknack]

Hello.
I have an oracle connection and I'm trying to run a stored procedure.

The code for defining the parameters in C# is:
------------------------------
cmd.commandType = CommandType.storedProcedure;
//Parameter 1:
OracleParameter prm1 = new OracleParameter("c1",OracleDbType.VARCHAR2);
prm1.Direction = ParameterDirection.Output;
prm1.Value = '1';
cmd.Parameters.add(prm1);

//Parameter 2:
OracleParameter prm2 = new OracleParameter("c2",OracleDbType.VARCHAR2,
200);
prm1.Direction = ParameterDirection.Output;
cmd.Parameters.add(prm2);

cmd.ExecutenonQuery();
--------------------------------------
The stored Procedure in Oracle (10g) is:

create or replace procedure test (c1 in varchar2, c2 out varchar2) is
Begin
c2:='1';
End test
\
--------------------------------------

The error I'm having is:
"ORA-06502: numeric or value error:charecter string buffer is too
small"

I saw that error in few sites but not something like my problem?

Any idea?

Thanks.

 

[AB]

I see some typos ...

//Parameter 1:
OracleParameter prm1 = new OracleParameter("c1",OracleDbType.VARCHAR2);
prm1.Direction = ParameterDirection.Output;

--> Input instead of Output ??

prm1.Value = '1';
cmd.Parameters.add(prm1);

//Parameter 2:
OracleParameter prm2 = new OracleParameter("c2",OracleDbType.VARCHAR2,
200);
prm1.Direction = ParameterDirection.Output;

^^^^
--> prm2

Any idea?

Thanks.

n/p

 

[Victor Rosenberg]

Hey man
Finally i can help

when you create the OracleParameter that will contain the output value
of a SP, you should use the constructor's overload that receives a
value as well, and pass there the String.Empty value.

Enjoy!


nicknack ëúá:

 

[nicknack]

Hi victor.

I didn't find any overload thet receive a value (I'm with VS2005 and I
added refference to the ODP dll of oracle).

What I did manage to do is to give the parameter a value:
prm3.value ='1'
and it worked but when i gave him string.Empty i got the same error.

Thanks.

Victor Rosenberg כתב:

 

[Victor Rosenberg]

Hi
I am not entirely sure (i dont have VS installed at home), but i think
there is some kind of CreateParameter function, within the command
object, or perhaps the connection, with lots of overloads, and one of
them is just as I've described before


nicknack כתב:

 

[Victor Rosenberg]

Hey man
Ignore my last message, here's the constuctor overload to use:
Declaration
// C#
public OracleParameter(string parameterName, OracleDbType type, object
obj,
ParameterDirection direction);

You can read more on
http://download-uk.oracle.com/docs/cd/B19306_01/win.102/b14307/OracleParameterClass.htm#i1011428



nicknack כתב: