How to call an oracle function with a in C#.

[jens Jensen]

Hello,
Assum i have an oracle function called ofunction.

Can someone tell me how i can call this function assuming it take the
parameter in_param of type clob.

Any help will be highly appreciated

C# .ADO.NET 2.0

jj

 

[n4ixt]

Which data connection are you using, System.Data.OracleClient or
System.Data.OleDb?

I'll take a guess. Here's an example that uses OracleClient to delete a
record from the database. It needs the primary key of the record to be
deleted passed in.

OracleCommand cmd = new OracleCommand("DeleteStoredProcedureName",
connOracleConnectionObject);
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter p_ID = new OracleParameter("PrimaryKeyID",
OracleType.Int32);
p_ID.Value = _ID; // _ID is class level variable (aka field) that
holds the id for the record to be deleted

cmd.Parameters.Add(p_ID);

cmd.ExecuteNonQuery();

To adapt my example for your code, you'd put ofunction where
DeleteStoredProcedureName is, change OracleType.Int32 to OracleType.Clob,
and change PrimaryKeyID to in_param. Oh, and of course change the assignment
of p_ID.Value from _ID to whatever your variable name is in C# that holds
the data.

For more info, check out Pro Oracle .Net Programming from APress
(http://www.apress.com/book/bookDisplay.html?bID=378). It was nice because
it taught ADO.Net from the Oracle perspective (virtually every other book
I've seen used SQL Server, which is nice as I like SQL Server, but in my
experience Oracle is used just as much).

Hope this helps,

Robert

 

[jens Jensen]

here is the function.

just assum you have a connection string:

FUNCTION process_message (

in_message IN CLOB

)

RETURN CLOB;





There seem to be no support to call a function.

 

[Bjorn Abelli]

...

here is the function.

just assum you have a connection string:

FUNCTION process_message (

in_message IN CLOB

)

RETURN CLOB;

There seem to be no support to call a function.

Yes, in ADO.NET there is.

You call it the same way as you call stored procedures, but add a special
parameter for the return value.

Something like this should work:

OracleCommand cmd = new OracleCommand("process_message", conn);
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter par =
new OracleParameter("RETURN_VALUE", OracleType.Clob);
par.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(par);

par = new OracleParameter("IN_MESSAGE", OracleType.Clob);
par.Value = myLargeObjectToPass;
par.Direction = ParameterDirection.Input;
cmd.Parameters.Add(par);

cmd.ExecuteNonQuery();

OracleLob myReturnValue =
(OracleLob) cmd.Parameters["RETURN_VALUE"].Value;


/// Bjorn A

 

[jens Jensen]

1000 Thanks,
this made my day.
jj