Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PLS-00306 "Wrong number or types of arguments" when calling overloaded package function, how to resolve?

Miller HJun 28 2024 — edited Jun 28 2024

I am trying to call an overloaded function inside of a package using VBA in excel with ADODB. I am at a loss on what I need to do to get this to work.

Here are my overloaded procedures (I am trying to use the first one)

procedure SelectModelLimits(a_curserRef out T_CURSOR, a_modelId in number , a_testSequenceId in NUMBER) 
is 
    sqlSelect varchar2(1024); 
    sqlWhere varchar2(200); 
    whereAdded int; 
begin 
    whereAdded := 0; 
    if ( a_modelId = 0) then 
        return; 
    end if; 
    
    if ( a_testSequenceId = 0) then 
        return; 
    end if; 
    
    sqlSelect := 'select limit.limit_id, limit.userinitial, limit.updated_date, limit.description, ' 
    || ' limit.Low, limit.High, TestStep.Name, TestStep.ViName, teststep.teststep_id, testStep.VariableName, TestStep.Description, ' 
    || ' TestSequenceStep.SequenceOrder, teststep.limittype_id, unit.display ' 
    || ' from limit inner join TestStep on Limit.TestStep_id = TestStep.TestStep_Id ' 
    || ' inner join TestSequenceStep on TestStep.TestStep_Id = TestSequenceStep.TestStep_Id ' 
    || ' inner join unit on Teststep.unit_id = Unit.unit_id '; 
    
    whereAdded := whereAdded + WhereFilterPkg.AppendWhereClause(sqlWhere, whereAdded, 'Limit.Model_Id', a_modelId); 
    whereAdded := whereAdded + WhereFilterPkg.AppendWhereClause(sqlWhere, whereAdded, 'TestSequenceStep.TestSequence_Id', a_testSequenceId); 
    whereAdded := whereAdded + WhereFilterPkg.AppendWhereClause(sqlWhere, whereAdded, 'Limit.Active', '1'); 
    
    if ( whereAdded >= 1 ) then 
        sqlSelect := sqlSelect || ' where ' || sqlWhere ; 
    end if; 
    
    sqlSelect := sqlSelect || 'order by TestSequenceStep.SequenceOrder '; 
    open a_curserRef for sqlSelect; 
end SelectModelLimits; 

procedure SelectModelLimits(a_curserRef out T_CURSOR, a_modelDataId in Number ) 
is 
    sqlSelect varchar2(1024); 
    sqlWhere varchar2(200); 
    whereAdded int; 
begin 
    whereAdded := 1; 
    if ( a_modelDataId = 0) then 
        return; 
    end if; 
    
    sqlSelect := 'Select ModelData.Model_id, ModelData.Description, ModelData.Updated_date, ' 
    || ' ModelData.User_Id, Limit.Limit_id, Limit.Low, Limit.High, TestStep.LimitType_id, ' 
    || ' TestStep.TestStep_id, TestStep.Name, TestStep.VIName, TestStep.Description, ' 
    || ' TestStep.VariableName, TestStepCategory.Name, ' 
    || ' LimitType.Name, Unit.Display, Type.Name, TestSequenceStep.SequenceOrder ' 
    || ' from ModelData, Limit,TestStep, TestStepCategory, LimitType, Unit, Type, TestSequenceStep ' 
    || ' where ' 
    || ' ( Limit.ModelData_Id = ModelData.ModelData_Id ) ' 
    || ' and ( Limit.TestStep_Id = TestStep.TestStep_Id ) ' 
    || ' and ( TestStep.Unit_Id = Unit.Unit_Id ) ' 
    || ' and ( TestStep.LIMITTYPE_ID = LimitType.LimitType_Id ) ' 
    || ' and ( TestStep.TestStepCategory_Id =TestStepCategory.TestStepCategory_Id ) ' 
    || ' and ( TestStep.Type_Id = Type.Type_ID ) ' 
    || ' and ( TestStep.TestStep_Id = TestSequenceStep.TestStep_Id ) ' 
    || ' and ( TestStep.TestStep_Id = TestSequenceStep.TestStep_Id ) '; 
    
    whereAdded := whereAdded + WhereFilterPkg.AppendWhereClause(sqlWhere, whereAdded, 'ModelData.ModelData_Id', a_modelDataId); 
    
    if ( whereAdded >= 1 ) then 
        sqlSelect := sqlSelect || sqlWhere ; 
    end if; 
    
    sqlSelect := sqlSelect || 'order by TestSequenceStep.SequenceOrder '; 
    open a_curserRef for sqlSelect; 
end SelectModelLimits;

Here is where I am calling the stored procedure:

Public Function getModelLimits(ByRef dbConnection As ADODB.Connection, ByVal modelID As Integer, ByVal sequenceID As Integer) As ADODB.Recordset
    Dim selectModelLimitsProc As ADODB.Command
    Set selectModelLimitsProc = New ADODB.Command
    selectModelLimitsProc.ActiveConnection = dbConnection
    
    With selectModelLimitsProc
        .Parameters.Append selectModelLimitsProc.CreateParameter("@a_modelId", adNumeric, adParamInput, 64, modelID)
        .Parameters.Append selectModelLimitsProc.CreateParameter("@a_testSequenceId", adNumeric, adParamInput, 64, sequenceID)
        .Properties("PLSQLRSet") = True
        .NamedParameters = True
        .CommandText = "ModelLimitsPkg.SelectModelLimits"
        .CommandType = adCmdStoredProc
        '.CommandText = "{ CALL ModelLimitsPkg.SelectModelLimits(?, ?) }"
        '.CommandType = adCmdText
    End With
    
    Dim limitsRecordSet As ADODB.Recordset
    Set limitsRecordSet = New ADODB.Recordset
    With limitsRecordSet
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockReadOnly
    End With
    
    Set limitsRecordSet = selectModelLimitsProc.Execute
    Set getModelLimits = limitsRecordSet
    
    selectModelLimitsProc.Properties("PLSQLRSet") = False
End Function

I was under the impression that with a ref_cursor being returned, setting PLSQLRSet to true was all I needed to do? What is going on?

Comments
Post Details
Added on Jun 28 2024
6 comments
399 views