How to call oracle function with 2 param using vb with oledbconnection?

[renn.lin]

hello, i want to ask why my function cant be called
here's the function

create or replace function hitung_denda (idPinjam number, tggl varchar2) return number is
  denda number;
  tgl1 date;
  tgl date;
begin
  select tgl_batas into tgl1 from peminjaman where id_pinjam=idPinjam;
  tgl:=to_date (tggl, 'MM/DD/YYYY');
  if tgl>tgl1 then
    select (tgl-tgl_batas)*1000 into denda from peminjaman where id_pinjam=idPinjam;
  else
    denda:=0;
  end if;
  return denda;
exception
  when no_data_found then
    return -1;
end;

and here's my vb code

 Private Sub TextBox3_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBox3.TextChanged
        Try
            Dim con As New OleDbConnection
            con.ConnectionString = "provider=msdaora; data source=xe; user id=hr; password=hr;"
            con.Open()

        Dim cmd As OleDbCommand = New OleDbCommand("hitung_denda", con)
        cmd.CommandType = CommandType.StoredProcedure
        Dim returnval As New OleDbParameter("retval", OleDbType.Decimal, 100, ParameterDirection.ReturnValue, True, 0, 0, "retval", DataRowVersion.Current, vbNull)
        cmd.Parameters.Add("idPinjam", OleDbType.Decimal, 5).Value = CInt(TextBox3.Text)
        cmd.Parameters.Add("tgl", OleDbType.VarChar, 40).Value = DateTimePicker1.Text

        Dim denda As Integer
        denda = returnval.Value
        Label8.Text = CStr(denda)

        con.Close()
        con.Dispose()
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, "")
    End Try
End Sub

i'm looking forward for your help.. Thanks

[cgeier]

First of all, I've found the following related to the provider "MSDAORA" (Microsoft OLE DB Provider for Oracle):

Microsoft OLE DB Provider for Oracle

"...This feature will be removed in a future version of Windows. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, use Oracle’s OLE DB provider..."

Also, you create a parameter "returnvalue" but never "add" it to "cmd": cmd.Parameters.Add(returnval). Additionally, you don't have any code that would execute the command.

[cgeier]

The following is untested (and has the username and password replaced by an asterisk):

    Private Function call_hitung_denda_function(ByVal idPinjam As Decimal, ByVal tgl As String) As Integer
        Dim denda As Integer
        Dim con As New OleDbConnection

        Try


            con.ConnectionString = "provider=msdaora; data source=xe; user id=*; password=*;"
            con.Open()

            'Dim cmd As OleDbCommand = New OleDbCommand("<package name>.hitung_denda", con)

            Dim cmd As OleDbCommand = New OleDbCommand("hitung_denda", con)
            cmd.CommandType = CommandType.StoredProcedure

            'Dim returnval As New OleDbParameter("retval", OleDbType.Decimal, 100, ParameterDirection.ReturnValue, True, 0, 0, "denda", DataRowVersion.Current, "")

            Dim returnval As New OleDbParameter()
            returnval.ParameterName = "returnval"
            returnval.OleDbType = OleDbType.Decimal
            returnval.Direction = ParameterDirection.ReturnValue

            cmd.Parameters.Add(returnval)

            cmd.Parameters.Add("idPinjam", OleDbType.Decimal, 5).Value = idPinjam
            cmd.Parameters.Add("tgl", OleDbType.VarChar, 40).Value = tgl

            'execute cmd
            'returns number of rows affected
            cmd.ExecuteNonQuery()

            'returns the first column in the first row
            'of the result set
            'cmd.ExecuteScalar()


            'denda = cmd.Parameters("returnval").Value
            denda = returnval.Value

            Label8.Text = CStr(denda)


        Catch ex As OleDb.OleDbException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        Finally
            'close connection 
            If con.State = ConnectionState.Open Then
                con.Close()
            End If

            'dispose connection
            con.Dispose()
        End Try

        Return denda

    End Function

Resources:
Note: Some of the code is adapted from C#.

Click Here

Click Here

Click Here

Click Here

Parameterized Queries (Oracle, SQLServer, OleDb)