#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    136
    Rep Power
    10

    Getting the out parameter from a oracle procedure in VB


    Hi,

    I'm having a question on how I can call a oracle procedure from VB and get the out parameter to a VB variable.

    The procedure I want to call is looking like this;

    Code:
    create or replace procedure validateCoA( v_segment1 in varchar
                         , v_segment2 in varchar
                         , v_segment3 in varchar
                         , v_segment4 in varchar
    	        , v_segment5 in varchar
    	        , v_segment6 in varchar
    	        , v_segment7 in varchar
    	        , v_segment8 in varchar
    	        , v_segment9 in varchar
    	        , v_validatedCCID out number
    	        , v_return out varchar
    	        , v_errormsg out varchar)

    I'm trying to alter some existing code that currently is calling another oracle function. The existing code can be seen below.

    What I'm wondering is how I can replace the existing sql sentence by making it call the validateCoA instead and also get the out variable from the procedure? I'm not very experienced with VB, and I would be very happy if someone could give me an example.

    Thanks very much in advance!

    Code:
    sSql = "select apps.xxah_find_ccid_f('50268','" & sInvoiceDate & "','" & sCompNo & "','" & SegmentArray(1) & "','" & SegmentArray(2) & "','" & SegmentArray(3) & "','" & SegmentArray(4) & "','" & SegmentArray(5) & "','" & SegmentArray(6) & "','" & SegmentArray(7) & "','" & SegmentArray(8) & "','" & SegmentArray(9) & "') from dual"
    WriteDebugLog sSql
    
    oDBObj.Open sSql, db.Connection, adOpenForwardOnly
    
    If oDBObj.EOF = False Then
        oDBObj.MoveFirst
        sReturnMessage = IsNullt(oDBObj.Fields(0).Value)
        WriteDebugLog sReturnMessage
    End If
    
    oDBObj.Close
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    136
    Rep Power
    10
    Hi again,

    After some googling I found some code example using the ADODB.Command
    object to get output value from the oracle procedure. I've edited my own code to use this ADODB.Command, but I'm not sure if it will work.

    Can anyone see any problems with the code?

    Thanks

    Code:
    Dim oDBObj As New ADODB.Recordset
    
    sSql = "select validateCoA('" & SegmentArray(1) & "','" & SegmentArray(2) & "','" & SegmentArray(3) & "','" & SegmentArray(4) & "','" & SegmentArray(5) & "','" & SegmentArray(6) & "','" & SegmentArray(7) & "','" & SegmentArray(8) & "','" & SegmentArray(9) & "', '', '', '') from dual"
    WriteDebugLog sSql
    
    oDBObj.Open sSql, db.Connection, adOpenForwardOnly
    
    Dim objCommand As ADODB.Command
      Set objCommand = New ADODB.Command
      With objCommand
        .ActiveConnection = oDBObj   'replace this with your Connection object
        .CommandType = adCmdText
        .CommandText = sSql         'replace this with your SQL statement
        .Prepared = True
        
        .Parameters.Append .CreateParameter("validatedCCID", adVarChar, adParamOutput, , "")
        .Parameters.Append .CreateParameter("return", adVarChar, adParamOutput, , "")
        .Parameters.Append .CreateParameter("errormsg", adVarChar, adParamOutput, , "")
            
    objCommand.Execute
        
    
    If oDBObj.EOF = False Then
        oDBObj.MoveFirst
        'sReturnMessage = IsNullt(oDBObj.Fields(0).Value)
        sReturnMessage IsNullt(objCommand.Parameters("validatedCCID").Value)
        WriteDebugLog sReturnMessage
    End If
    
    oDBObj.Close

IMN logo majestic logo threadwatch logo seochat tools logo