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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
358 views