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?