Argument name not found for PL/SQL procedure, only in IIS7 (not IIS6)

Question

I have a stored procedure in oracle getspname which I will receive 2 input parameters (varchar2 and number) and one output param of cursor type.

The application that is executing this stored procedure is a bit old, it is using VB6 in IIS6 to connect to this SP in ORACLE and I am migrating it to IIS7.

Provider=MSDAORA.1;Data Source=XXXXXXXX;User ID=XXXXXXXXX;Password=XXXXXX

and SQL is:

{call schema.Pkg_name.getspname(?,?,{resultset 0, io_cursor})}

Params: 11111111;0

IN IIS6 has no problem executing the procedure and returning the cursor data, in IIS7 if not, it sent input parameters, this also works, but I need to send it these fields to comply with the applied business logic.

This solution was also implemented but the error is replicated in SP that has only input or output parameters of type varchar2 stackoverflow.com/a/50643323/5904375

VB6:

Public Function execProcedure( _
                            ByVal sConnString As String, _
                            ByVal sSQL As String, _
                            ByRef oRs As Variant, _
                            ByVal sParameters As Variant, _
                            ByRef vntRespuesta As Variant, _
                            Optional Encrypt As Boolean _
                            ) As Integer
    Dim Params, i
    Dim RsCmd
    Dim oRecordSet
    Dim RspMsg  As Variant
    Dim RspMsg1  As Variant
    Dim RspMsg2  As Variant
    Dim oConec
    Dim BeforeConn As Boolean
    Dim auxErr As Integer
    Dim auxValue As Variant
    Dim StringReg As Variant
    Dim oEncrypt
    Dim bEncrypt  As Boolean
        
    On Error GoTo Error_handler
    
    '** Se verifica encriptacion del string de conexion
    bEncrypt = False
    If Not IsMissing(Encrypt) Then
        If Encrypt Then bEncrypt = True
    End If
    
    If bEncrypt Then
        Set oEncrypt = CreateObject("NR_DBconn.Encrypt")
        StringReg = oEncrypt.EncriptarChrTran("azv", sConnString, 2)
        Set oEncrypt = Nothing
    Else
        StringReg = sConnString
    End If
          
          
    BeforeConn = True
    Set ctxObject = GetObjectContext
    
    strNombreObjeto = "ADODB.Command"
    Set RsCmd = ctxObject.CreateInstance("ADODB.Command")
    'Set RsCmd = CreateObject("ADODB.Command")
    
    strNombreObjeto = "ADODB.Recordset"
    Set oRecordSet = ctxObject.CreateInstance("ADODB.Recordset")
    'Set oRecordSet = CreateObject("ADODB.Recordset")
    
    strNombreObjeto = "ADODB.Connection"
    Set oConec = ctxObject.CreateInstance("ADODB.Connection")
    'Set oConec = CreateObject("ADODB.Connection")

    '*
    '* Verifica si el sSQL es válido
    '*
    If sSQL = "" Or Len(sSQL) = 0 Then
        ctxObject.SetAbort
        'La transaccion finaliza con error
        Err.Raise INSTRUCCION_INVALID, "Verificar sSQL"
    End If
    
    '*
    '* Se verifica string de conexión
    '*
    If (StringReg = "") Then
        ctxObject.SetAbort
        'La transaccion finaliza con error
        Err.Raise INSTRUCCION_INVALID, "Verificar string de Conexion"
    End If
    
    BeforeConn = False
    
    oConec.Open StringReg
        
    With RsCmd
        .ActiveConnection = oConec
        .CommandText = sSQL
        .CommandType = adCmdText ' adCmdStoredProc   'adCmdText
        
        If Len(sParameters) <> 0 Then
            Params = Split(sParameters, ";")
            For i = 0 To UBound(Params)
                .Parameters(i).Direction = adParamInput
                .Parameters(i).Value = Params(i)
            Next i
        End If
            
    End With
                    
    oRecordSet.CursorLocation = adUseClient
    oRecordSet.CursorType = adOpenStatic
    oRecordSet.LockType = eltBatchOptimistic
    Set oRecordSet = RsCmd.Execute()
    
'*
'* Verifica el resultado
'*
    auxErr = -1
    
    If oRecordSet.State = 1 Then
        
        If oRecordSet.EOF Or oRecordSet.BOF Or oRecordSet Is Nothing Then
            RspMsg1 = "0"
            RspMsg2 = "No se obtuvo recordset de vntRespuesta - warning"
            RspMsg = "[" & RspMsg1 & "]: " & RspMsg2
            
            auxErr = 0
            Set oRs = Nothing
        Else
            RspMsg1 = "0"
            RspMsg2 = ""
            RspMsg = "" '"[" & RspMsg1 & "]: " & RspMsg2
            
    '------traspasa el recordsetresult a recordset desconectado
            Dim Rsx
            Dim j
            Dim NumFields
            
                Set Rsx = New ADODB.Recordset
                NumFields = oRecordSet.Fields.Count - 1
                With Rsx
                    .CursorLocation = adUseClient
                    .CursorType = adOpenStatic
                    .LockType = eltBatchOptimistic
                    Set .ActiveConnection = Nothing
                    For i = 0 To NumFields
                      With .Fields
                        .Append oRecordSet.Fields(i).Name, adBSTR
                      End With
                    Next i
                    .Open
                    Do While Not oRecordSet.EOF
                        .AddNew
                        For j = 0 To NumFields
                            auxValue = oRecordSet.Fields(j)
                            .Fields(j) = CheckNull(auxValue)
                        Next j
                        oRecordSet.MoveNext
                    Loop
                    .MoveFirst
                End With
    
    '-------------
            Set oRs = Rsx
            'Set oRecordSet.ActiveConnection = Nothing
            Set oRecordSet = Nothing
            Set Rsx = Nothing
            auxErr = 1
            
        End If
    Else
        RspMsg1 = "2"
        RspMsg2 = "No se obtuvo recordset de vntRespuesta - operacion no necesita"
        RspMsg = "[" & RspMsg1 & "]: " & RspMsg2
        
        auxErr = 2
        Set oRs = Nothing
    
    End If
    
    
    vntRespuesta = RspMsg
    
    Set RsCmd = Nothing
    oConec.Close
    Set oConec = Nothing
    
'    If IsObject(oRs) Then
'    execProcedure = oRs.RecordCount
'    Else
    execProcedure = auxErr
'    End If
    
    '*
    '* La transacción finaliza exitosamente
    ctxObject.SetComplete
    
    Exit Function
    
Error_handler:

    execProcedure = -1
    RspMsg = ErrorMessage("SQLserver.execProcedure.", Err.Number, Err.Source, Err.Description, sSQL)
    
    '*
    '* Fin de la función, ha ocurrido un Error Fatal
    '*
    
    On Error Resume Next
    If Not BeforeConn Then
        If (oConec.State = adStateOpen) Then
            oConec.Close
        End If
    End If
    Set oConec = Nothing
    
    vntRespuesta = RspMsg

    '*
    '* La transacción finaliza con error
    '*
    ctxObject.SetAbort

End Function

Function Error VB6:

Private Function ErrorMessage(ErrPath, ErrCod, ErrSource, ErrDescription, sSQL)
Dim RspMsg1
Dim RspMsg2
Dim strPathError

    strPathError = "[" & ErrPath & "." & Err.Source & "] "

    Select Case (ErrCod)
        Case INSTRUCCION_INVALID
             RspMsg1 = INSTRUCCION_INVALID
             RspMsg2 = strPathError & " El string 'sSQL' esta mal formado." & _
                        "Verifique que el String 'sSQL' esté de acuerdo al formato SQL"
        Case STRING_CONNECTION
             RspMsg1 = STRING_CONNECTION
             RspMsg2 = strPathError & "No existe string de conexion para esta Aplicacion "
        Case NOT_REC_RETURN
             RspMsg1 = NOT_REC_RETURN
             RspMsg2 = strPathError & "No se retornaron registros." & _
                        "Intente de nuevo o con otros valores en el 'sSQL'. Este error puede deberse a una falla en la conexion con la Base de Datos. (" & sSQL & ")"
        Case NOT_REC_FOUND
             RspMsg1 = NOT_REC_FOUND
             RspMsg2 = strPathError & "No se encontro registros." & _
                        "Intente con otros valores en la 'sSQL'. (" & sSQL & ")"
        Case 429:
            RspMsg1 = "-1"
            RspMsg2 = strPathError & " No se puede crear el componente: '" & strNombreObjeto & _
                        "Verifique que el componente esté instalado correctamente."
        'Error manipulado. En parametros de la base de datos.
        Case -2147217887
             RspMsg1 = "-1"
             RspMsg2 = strPathError & "Error al insertar campo." & _
                        "Verifique el largo del valor del campo no exceda al maximo permitido en la definicion de la TABLA de la Base de Datos."
        Case Else 'Cualquier otro tipo de Error
            RspMsg1 = "-1"
            RspMsg2 = strPathError & ErrDescription
    End Select

    ErrorMessage = "[" & RspMsg1 & "]: " & RspMsg2

End Function

This SP was also independently tested to rule out any problem, it works correctly SP ORACLE:

 PROCEDURE getspname (value IN varchar2 ,value2 IN number , io_cursor IN OUT t_cursor)
    AS
      
      BEGIN
        ........
      END;


END  getspname;

Answer 1

I solved this on Friday 08/17/2020 at 3 AM,

I decided to make a call directly to the oracle SP from the ASP using provider (OraOLEDB.Oracle) and thus replicate the behavior, this was giving an error of "Run-time error 3001 Arguments Are Of The Wrong Type… when setting ADODB.Command object members"

The ASP code:

Name file: test.asp

<%@ Language=VBScript %>
<%Response.Buffer = false%>
<%Response.Expires = 0%>

<%

    Dim strConnect
    strConnect = "Provider=OraOLEDB.Oracle;Data Source=XXXXXXXX;User ID=XXXXXXXXX;Password=XXXXXX"
    Dim cn, rs, cmd, param  
    set cn = Server.CreateObject( "ADODB.Connection" )
    cn.Open Cstr(strConnect)   
    set cmd = server.CreateObject ("ADODB.Command")
    with cmd
        set .ActiveConnection   = cn
        .NamedParameters = True 
        .CommandText    =  "{call schema.Pkg_name.getspname({resultset 0, io_cursor})}"
        .CommandType    = 1
    end with

    set rs = server.CreateObject ( "ADODB.Recordset" ) 
    set rs = cmd.execute

    Set rs = cmd.Execute
    Do Until rs.EOF
        for each x in rs.fields
            response.write(x.name)
            response.write("=")
            response.write(x.value)
            response.write(", ")
        next
        response.write("<br />")
        rs.MoveNext
    Loop

    rs.Close 
%>


<%
    Dim strConnect_cn
    strConnect_cn = "Provider=OraOLEDB.Oracle;Data Source=XXXXXXXX;User ID=XXXXXXXXX;Password=XXXXXX"
    Dim cn_cn, rs_cn, cmd_cn , param1, param2
    set cn_cn = Server.CreateObject( "ADODB.Connection" )
    cn_cn.Open Cstr(strConnect_cn)   
    set cmd_cn = server.CreateObject ("ADODB.Command")
    with cmd_cn
        set .ActiveConnection   = cn_cn
        .CommandText    =  "{call schema.Pkg_name.getspname(?,?,{resultset 0, io_cursor})}"
        .CommandType    = 1
        
    end with
    cmd_cn.parameters.append(cmd_cn.createParameter("", adVariant, adParamInput, , "11111111"))
    cmd_cn.parameters.append(cmd_cn.createParameter("", adVariant, adParamInput, , "0"))

    set rs_cn = server.CreateObject ( "ADODB.Recordset" ) 
    set rs_cn = cmd_cn.execute

    Set rs_cn = cmd_cn.Execute
    Do Until rs_cn.EOF
        for each x in rs_cn.fields
            response.write(x.name)
            response.write("=")
            response.write(x.value)
            response.write(", ")
        next
        response.write("<br />")
        rs_cn.MoveNext
    Loop

    rs_cn.Close 
%>


<html>
<head>
<title> Links </title>

</head>
<body>
    <p>test.asp</p>
</body>
</html>

This code throws me the following problem with input parameters, where the arguments were of the wrong type.

"Run-time error 3001 ‘Arguments Are Of The Wrong Type, Are Out Of The Acceptable Range, or are in conflict with one another’ upon invocation of"

And according to the documentation that I found this is fine

• http://www-db.deis.unibo.it/courses/TW/DOCS/w3schools/asp/met_comm_createparameter.asp.html

• https://welookups.com/asp/ado_datatypes.html

• https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/datatypeenum?view=sql-server-ver15

After much searching in forums on the Internet I found that the following article:

• http://keencod.in/?p=23

This explains:

...that error is fired because of late binding of library references, so VB simply did not know of adCmdStoredProc and other constants. That meant that this error has nothing to do with ADODB or Ole or SP...

Therefore, you only had to declare these values at the beginning as constants, just as explained in the article.

So at the beginning of my ASP (test.asp) code add:

Const adVarChar  = 200
Const adParamInput  = &H1
Const adParamOutput  = &H2
Const adCmdStoredProc  = &H4
Const adVariant = 12

And magic, it worked!!

Then I modified my VB6 code, which was the one I had to use to comply with the business logic of the applications that were being migrated.

At the beginning of the file add:

Const adVarChar As Long = 200
Const adParamInput As Long = &H1
Const adParamOutput As Long = &H2
Const adCmdStoredProc As Long = &H4
Const adCmdText As Long = 1
Const adUseClient As Long = 3
Const adOpenStatic As Long = 3
Const adVariant As Long = 12

And then change my VB6 code like this:

    With RsCmd
        .ActiveConnection = oConec
        .CommandText = sSQL
        .CommandType = adCmdText ' adCmdStoredProc   'adCmdText
        
        If Len(sParameters) <> 0 Then
            .NamedParameters = True
            Params = Split(sParameters, ";")
            For i = 0 To UBound(Params)
                .Parameters.Append (.CreateParameter("", adVariant, adParamInput, , Params(i)))
            Next i
        End If
            
    End With

Using provider: MSDAORA.1 And with this the problem was solved!!

This is my story, thank you very much!!

End.