none
How to call an Oracle 10g Stored function not procedure.

    Question

  • I've been trying to figure out how to call an Oracle 10g stored function and insert the return value into my VB.NET application.  Stored procedures I've been already able to figure out, but I have no idea about functions.  No where on the web have I been able to find any good help on how to do this.  I have the parameter set to send to the function, the oracle function works perfect, but what VB coding to I need to get that return value.  The return value is a CHAR.

    Again the Oracle DB is in their 10g release.

    My version of VB.NET Is Visual Studio 2010.

    Also I've beent trying to use the Imports System.Data.OracleClient namespace but it comes up with an error saying its not available.  Right now I use System.Data.OleDb.  How can I import the oracleclient?

    Thanks for the help

    Wednesday, November 10, 2010 5:28 PM

Answers

  • That should come from your first (return) parameter:

    TextBox1.Text = dbComm.Parameters(0).Value
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Liliane Teng Thursday, November 18, 2010 8:48 AM
    Wednesday, November 17, 2010 1:14 PM
  • ' Clarify the Parameters.
    Retparm = New OracleParameter()
    Retparm.Direction = ParameterDirection.ReturnValue
    Retparm.OracleDbType = OracleDbType.Char
    Retparm.Size = 25
    dbComm.Parameters.Add(Retparm)
    
    ' Define which Stord Procedure to call.
    dbComm.CommandText = "EMPNAME_SF"
    dbComm.CommandType = CommandType.StoredProcedure
    dbComm.ExecuteNonQuery()
    
    ' Receive the return name and put it in the name label.
    Dim theReturnValue As String
    theReturnValue = Retparm.Value.ToString()
    
    

    I'd make the Parameter for the return value unique, instead of naming it twice as "parm".  So, name it RetParm and then you can get it's value after the ExecuteNonQuery is issued.

    Here is a more comprehensive look at working with Oracle Databases:

    http://msdn.microsoft.com/en-us/library/ms971506.aspx#msdnorsps_topic5


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    • Marked as answer by Liliane Teng Thursday, November 18, 2010 8:48 AM
    Wednesday, November 17, 2010 1:17 PM

All replies

  • I just found out how to get the imports system.data.oracleclient.

    Wednesday, November 10, 2010 5:37 PM
  • Not that I personally could give a flying ____ or care one iota since I don't use Oracle, however forum etiquette suggests that you might take a moment from your busy day and non reflective, self absorbed life to post the solution you discovered so that others might benefit and utilize this knowledge in the future?

    David Marso/TrancePlant
    Wednesday, November 10, 2010 9:27 PM
  • Sounds like your having a ruff day.  I just wanted to let people know I found it so they wouldn't have to answer it.  However here is the answer to getting the Imports System.Data.OracleClient:

    It should already be installed on your computer but you need to reference it to Visual Studio.  I have version 2010.  To add it open up VS and your program.  Right click on tha name of your program in the solution explorer.  Click on add Reference, click on the browse tab, then find this folder: C:\Program Files\Microsoft.Net\OracleClient.Net.  Double click on the System.Data.OracleClient.dll and load it into VS.

    I still need help in trying to complete an Oracle stored function.  Again I can connect to the DB and pass in the parameter, I just don't know how to get the value back from the DB.

    • Marked as answer by smdp Thursday, November 11, 2010 3:34 PM
    • Unmarked as answer by smdp Thursday, November 11, 2010 3:34 PM
    Thursday, November 11, 2010 3:29 PM
  • First, I would recommend switching to Oracle's ODP.NET. The System.Data.OracleClient library has been deprecated by Microsoft. There will be no future development and it may not be available in the next version of the .NET Framework:

    http://blogs.msdn.com/b/adonet/archive/2009/06/15/system-data-oracleclient-update.aspx

    http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

    The following should help with calling an Oracle function:

    http://stackoverflow.com/questions/1773534/what-is-the-right-way-to-call-an-oracle-stored-function-from-ado-net-and-get-the

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, November 11, 2010 3:45 PM
  • You need to setup an OracleDataReader that reads the value from REF CURSOR --- don't ask me about the SProc because I don't know Oracle.  But it just looks like you are on the right track and just need to continue to finalize your implementation.  Here's a link with just about all the example you'd need:

    http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracledatareader(v=VS.71).aspx


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Thursday, November 11, 2010 3:45 PM
  • I have my stored function working half way.  What I need now is to know how to display the return value inside a textbox or label on a form.  Here is the code that I have, I just need to know the code for displaying the return value.

    ' Open the connection to the DB
    Call OpenConn()
    dbComm = dbConn.CreateCommand

    ' Clarify the Parameters.
    parm = New OracleParameter()
    parm.Direction = ParameterDirection.ReturnValue
    parm.OracleDbType = OracleDbType.Char
    parm.Size = 25
    dbComm.Parameters.Add(parm)

    parm = New OracleParameter()
    parm.Direction = ParameterDirection.Input
    parm.Value = scanNum
    parm.OracleDbType = OracleDbType.Int16
    dbComm.Parameters.Add(parm)

    ' Define which Stord Procedure to call.
    dbComm.CommandText = "EMPNAME_SF"
    dbComm.CommandType = CommandType.StoredProcedure
    dbComm.ExecuteNonQuery()

    ' Receive the return name and put it in the name label.
    ????????

    ' Close connection
    dbConn.Close()

    Wednesday, November 17, 2010 5:52 AM
  • That should come from your first (return) parameter:

    TextBox1.Text = dbComm.Parameters(0).Value
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Liliane Teng Thursday, November 18, 2010 8:48 AM
    Wednesday, November 17, 2010 1:14 PM
  • ' Clarify the Parameters.
    Retparm = New OracleParameter()
    Retparm.Direction = ParameterDirection.ReturnValue
    Retparm.OracleDbType = OracleDbType.Char
    Retparm.Size = 25
    dbComm.Parameters.Add(Retparm)
    
    ' Define which Stord Procedure to call.
    dbComm.CommandText = "EMPNAME_SF"
    dbComm.CommandType = CommandType.StoredProcedure
    dbComm.ExecuteNonQuery()
    
    ' Receive the return name and put it in the name label.
    Dim theReturnValue As String
    theReturnValue = Retparm.Value.ToString()
    
    

    I'd make the Parameter for the return value unique, instead of naming it twice as "parm".  So, name it RetParm and then you can get it's value after the ExecuteNonQuery is issued.

    Here is a more comprehensive look at working with Oracle Databases:

    http://msdn.microsoft.com/en-us/library/ms971506.aspx#msdnorsps_topic5


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    • Marked as answer by Liliane Teng Thursday, November 18, 2010 8:48 AM
    Wednesday, November 17, 2010 1:17 PM
  • Thank you very much that worked.  There was one thing that I had to add because of the data type of the text box so it looked like this:

    TextBox.Text = Convert.ToString(dbComm.Parameters(0).Value)

    You guys are awesome!

    Wednesday, November 17, 2010 9:06 PM