Home » SQL & PL/SQL » SQL & PL/SQL » How to call a function(returning a number) inside an oracle package from vb.net ?
icon7.gif  How to call a function(returning a number) inside an oracle package from vb.net ? [message #211178] Wed, 27 December 2006 01:06 Go to next message
vineet2k1
Messages: 1
Registered: December 2006
Location: Mumbai
Junior Member
Hi
Im trying to call oracle function from vb.net, but continuosly getting some errors..
My Function has 1 input and 1 output parameter and it returns a number.
Pls help me, if u have any solution..
Re: How to call a function(returning a number) inside an oracle package from vb.net ? [message #211234 is a reply to message #211178] Wed, 27 December 2006 08:09 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

whenever you want to post a query post along with your code/work and also error message you are getting then it will be easier for the guys to answer you properly

However see the following code which may serve your purpose.

Add a reference for oracle client in your dot net as follows

Project->Add Reference
In the references
Add System.data.OracleClient

I created a test function which accepts one input and one output parameters and returns a number value


Oracel Test Function as follows

create or replace function testFunction (eno number,jb out varchar2) return number is
sl number;
begin
select sal,job into sl,jb from emp where empno=eno;
return ( sl );
exception
when no_data_found then
jb := 'No Data';
return (0);
end;

Vb.net code

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim Conn As New OracleConnection("Data Source=ssil;user id=scott;password=tiger")
Dim cmd As New OracleCommand
Conn.Open()
cmd.CommandText = "testfunction"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("eno", OracleType.Number)
cmd.Parameters.Add("jb", OracleType.VarChar, 10)
cmd.Parameters.Item("jb").Direction = ParameterDirection.Output
cmd.Parameters.Item("eno").Value = 7788
cmd.Parameters.Add("sl", OracleType.Number)
cmd.Parameters.Item("sl").Direction = ParameterDirection.ReturnValue
cmd.Connection = Conn
Dim obj As Object = cmd.ExecuteScalar()
Dim str As String
str = "Salary = " & cmd.Parameters.Item("sl").Value & vbNewLine
str = str & "Job = " & cmd.Parameters.Item("jb").Value
MessageBox.Show(str)
end sub
Re: How to call a function(returning a number) inside an oracle package from vb.net ? [message #211750 is a reply to message #211178] Tue, 02 January 2007 02:06 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
s a general note, don't use OUT parameters on functions - it defeats the whole point of using them.
Previous Topic: ROWNUM
Next Topic: Automatically run pl/sql or Sql*Plus script
Goto Forum:
  


Current Time: Tue Dec 06 08:28:02 CST 2016

Total time taken to generate the page: 0.08181 seconds