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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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,286 views