Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ODP.NET - Calling Oracle Function , ODD behaviour.

508658May 16 2006 — edited Feb 14 2011
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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 14 2011
Added on May 16 2006
6 comments
29,292 views