ODP.NET - Calling Oracle Function , ODD behaviour.

508658

I wrote a small Oracle Function for Test as below.
CREATE OR REPLACE FUNCTION SaveData(en varchar2) RETURN VARCHAR2 IS
xxx varchar2(100);
Begin
xxx := en || 'Done';
Insert into TestSaveData values(xxx);
Return xxx;
End;

When i Execute the same from SQL*PLus it works as expceted. e.g
Step1: Exec :x := SaveData('Hello');
Step2: Select * from TestSaveData;
ENX
---------
HelloDone
Done
Done
Done
Done


But when i call from .NET using ODP, some how "EN" does not receive parameter value, as you can see in sample data above. The First record is inserted via PL*SQL and Rest by a .NET program.

Following is the code written in VB.NET
Try
Dim cnn As New Oracle.DataAccess.Client.OracleConnection("Data Source=OracleG;User Id=Scott;password=Tiger")
Dim cmd As New Oracle.DataAccess.Client.OracleCommand("SAVEDATA", cnn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("EN", Oracle.DataAccess.Client.OracleDbType.Varchar2, 100, "HHH", ParameterDirection.Input)
cmd.Parameters.Add("RS", Oracle.DataAccess.Client.OracleDbType.Varchar2, ParameterDirection.ReturnValue)
cnn.Open()
Dim ooo As Object = cmd.ExecuteScalar()
Dim q As Object = cmd.Parameters("RS").OracleDbType.ToString()
Dim s As String = q
cnn.Close()
Catch ex As Oracle.DataAccess.Client.OracleException
MsgBox(ex.Message())
End Try


I cant't spot where i am making mistake. Can someone throw light

445476

Immjediately before you execute.. how many parameters does the command have, and what are the values?

600550

No need to do this much code for getting value from function ,

just take values like this

Dim cnn As New Oracle.DataAccess.Client.OracleConnection("Data Source=OracleG;User Id=Scott;password=Tiger")
Dim cmd As New Oracle.DataAccess.Client.OracleCommand()

cmd.Connection=cnn
cmd.CommandType = CommandType.Text

Dim obj as object
Dim s as String

obj = cmd.ExecuteScalar("SELECT SaveData('Hello') FROM DUAL")

s = obj.ToString()

556154

For some reason only knows to Larre, your returnvalue must be the FIRST parameter. Not sure for the logic, but it works.

So by simply switching your code to:
cmd.Parameters.Add("RS", Oracle.DataAccess.Client.OracleDbType.Varchar2, ParameterDirection.ReturnValue)

cmd.Parameters.Add("EN", Oracle.DataAccess.Client.OracleDbType.Varchar2, 100, "HHH", ParameterDirection.Input)

It'll work.

651667

I am agree with kesler's opinion,Just do it.

gdarling - oracle

Just fyi, here's the reason the returnvalue needs to be first. ODP constructs an anonymous block to execute the stored procedure, along the lines of the following:

begin :retval := myproc(:paramval);end;

and the default for ODP is to bind by position, rather than by name. So the fist parameter added needs to be the return value.



Hope it helps,
Greg

839560

Thanks man... I was in a lot of troubles with this, and i would never imagine that the parameter must be the first one