Re: How make a function in vb

From: Walter T Rejuney <BlueSax_at_Unforgetable.com>
Date: Tue, 19 Dec 2000 12:11:47 -0500
Message-ID: <3A3F96D3.3B50B3CA@Unforgetable.com>

Avi Abrami wrote:

> Walter T Rejuney wrote:
>
> > MP wrote:
> >
> > > How can call a function in Oracle that return a number in VB
> > > Thanks
> >
> > Could you restate this? The sentence, as written, doesn't make any
> > sense.
>
> Although I didn't write the original sentence, allow me to express
> my interpretation ...
>
> From a Visual Basic program, I wish to execute an Oracle database
> stored function and I know that the database function returns a number.
>
> Is that better?
> Avi.

Ok, if this is what is wanted, the actual methods depend on what approach one takes to data access. Generally I use ADO and this means that I deal with OLE DB syntax. However, there are many others who prefer OO4O and the syntax for that approach is entirely different.

I'll illustrate in a general way how I go about this using ADO and OLE DB:

First, lets assume that instead of using a function, you actually use a stored procedure that accepts one IN parameter and returns the output value in an OUT parameter. Also, there are two additional OUT parameters which will hold the output from SQLCODE and SQLERRM (I have found that I need this because sometimes the ADO error detection does not really work as it should when stored procedures are involved.

So, here is my stored procedure:

create or replace procedure myproc (InParm IN Varchar2,

                                                       OutParm OUT Number,
                                                       Errcode   OUT
Number,
                                                       Errmsg    OUT

Varchar2)
Begin

    If Inparm = 'xxx' then

        OutParm := 1;
    Else

        OutParm := 2;
    End if;
Exception

    When Others Then

        Errcode := Sqlcode;
        Errmsg := Substr(Sqlerrm,1,300);

End;

In my vb code I'd do something like this:

Public Sub GetValue()

    Dim MyConnection as ADODB.Connection     Dim MyCommand as ADODB.Command
    Dim Parm1 as ADODB.Parameter
    Dim Parm2 as ADODB.Parameter
    Dim Errcode as ADODB.Parameter
    Dim Errmsg as ADODB.Parameter
    Dim OraError as ADODB.Error
    Dim strSql as String

    Const strUsername As String = "scott"
    Const strPassword As String = "tiger"
    Const strService  As String = "orcl"
    Const strProvider As String = "MSDAORA.2"
    Const strConnect  As String = "Provider=" & strProvider & ";" & _
                                     "Data Source=" & strService & ";" & _
                                     "User Id=" & strUsername & ";" & _
                                     "Password=" & strPassword & ";"

    Set MyConnection = New ADODB.Connection

    MyConnection.ConnectionString = strconnec
    MyConnection.CursorLocation = adUseServer
    MyConnection.Open

    strSql = "scc.InsertCallDetail"

    Set MyCommand = New ADODB.Command
    Set MyCommand.ActiveConnection = MyConnection     MyCommand.CommandType = adCmdStoredProc     MyCommand.CommandText = strSql
    Set Parm1 = MyCommand.CreateParameter(, adVarchar, adParamInput)     Parm1.Value = "yyy"
    MyCommand.Parameters.Append Parm1
    Set Parm2 = MyClmmand.CreateParameter(,adNumeric,adParamOutput)     Parm2.Value = ""
    MyCommand.Parameters.Append Parm2
    Set ErrCode = MyCommand.CreateParameter(,adNumeric,adParamOutput)     ErrCode.Value = 0
    MyCommand.Parameters.Append ErrCode
    Set ErrMsg = MyCommand.createParameter(,adVarchar,adParamOutput)     ErrMsg.Value = ""
    MyCommand.Parameters.Append Errmsg
    MyCommand.Execute

    If Errcode.Value <> 0 Then

        MsgBox Errmsg.Value
    Else

        MsgBox "The return value is: " & Cstr(Parm2.Value)     End if
End Sub

Of course, this is only the very basics. There are a lot of variables depending on what exactly you are trying to accomplish. The best book on this subject is: "VB Oracle 8 Programmers Reference" by Dov Trietsch. Received on Tue Dec 19 2000 - 11:11:47 CST