6 Replies Latest reply: Feb 14, 2011 9:34 AM by 839560 RSS

    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