Calling Oracle Stored Functions from vb.net

  • Thread starter Thread starter rishaan
  • Start date Start date
R

rishaan

I am trying to call a stored function of Oracle Package from VB.net.
The function takes a string and returns boolean.
How should I call it from vb.net ?
I tried 3 different ways but get error:

ORA-06550:PLS-00221:StoredFunctionNAme is not a procedure or is
undefined.:PL/SQL:statement ignored.

I also tried "select StoredFunctionName(' " & saha & "') from
dual"
but Oracle does not allow boolean values in SQL.

the code:

Dim connection As OracleConnection = Nothing
Dim command As OracleCommand = Nothing
connection = New OracleConnection(ConnString)
command.CommandType = CommandType.StoredProcedure
Dim paraName As New OracleParameter("StoredFunctionNAme",
OracleType.VarChar)
paraName.Direction = ParameterDirection.Input
paraName.Value = Name

command.Parameters.Add(paraName)
connection.Open()
Dim MyDA As New OracleDataAdapter(command)
Dim ds As New DataSet
Try

(1) MyDA.Fill(ds)

Dim rowid As OracleString
(2) Dim bb As Boolean =
Convert.ToBoolean(command.ExecuteOracleNonQuery(rowid))

(3) Dim bb1 As Boolean =
Convert.ToBoolean(command.ExecuteNonQuery())
Catch x As Exception
MessageBox.Show(x.Message.ToString)
End Try

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*
 
rishaan said:
I am trying to call a stored function of Oracle Package from VB.net.
The function takes a string and returns boolean.
How should I call it from vb.net ?
I tried 3 different ways but get error:

ORA-06550:PLS-00221:StoredFunctionNAme is not a procedure or is
undefined.:PL/SQL:statement ignored.
Click to expand...

You call a stored function exactly like you call any PL/SQL. But it's
tricky because neither SQL nor the OracleClient support the PL/SQL boolean
type.

So you have to do what you have to do whenever you need to use PL/SQL types
which aren't supported in the client (row types, object types, etc). You
need to use a Pl/SQL wrapper to access the function. If you can, the
easiest thing is probably to write a wrapper function which returns a number
1 or 0 instead of a boolean. Then you can run "select my_function(:arg)
from dual".

If you can't do this, you can send down the PL/SQL wrapper as an anonymous
block and do the conversion there.

Here's an example of how to do that.
Using the stored function

create or replace function my_function(in_arg varchar2)
return boolean
as
begin
return true;
end;
/


Imports System
Imports System.Data
Imports System.Data.OracleClient

Class test


Public Shared Function connect(ByVal ConnectionString As String) As
OracleConnection
Dim con As OracleConnection = New OracleConnection(ConnectionString)
con.Open()
Return con
End Function



Public Shared Function MyOracleFunction(ByVal con As OracleConnection,
ByVal arg As String) As Boolean
Dim cmd As New OracleCommand("begin :rv := case when my_function(:arg)
then 1 else 0 end; end;", con)
Dim rv As OracleParameter = cmd.Parameters.Add("rv", OracleType.Int32)
rv.Direction = ParameterDirection.Output
cmd.Parameters.Add("arg", arg)
cmd.ExecuteNonQuery()
Dim i As Integer = rv.Value
If i = 0 Then
Return False
Else
Return True
End If
End Function

<MTAThread()> _
Public Shared Sub Main(ByVal Args() As String)
'pipe trace output to the console
'in your app this would go to a trace file
Trace.Listeners.Add(New TextWriterTraceListener(System.Console.Out))
Dim connectionString As String = "User Id=scott;Password=tiger;Data
Source="
Dim con As OracleConnection = connect(connectionString)

Try

Dim b As Boolean = MyOracleFunction(con, "someValue")
Console.WriteLine(b)

Catch ex As Exception
Trace.Write(ex.ToString)
Finally
con.Close()
End Try

End Sub


End Class

David
 
Hi Rishaan

In your code I could not find the code where you are specifying the
stored procedure name. If "StoredProcedureNAme" is your procedures name
then it should be passed like the following code -

command.commandtext = "StoredProcedureNAme"

and the Oracleparameter takes the parametername as the first argument.

Hope this will solve ur prob
 
Back
Top