Hello. I have a problem returning mutiple index-by tables as host arrays back to ado from a package (Oracle). The problem is that I cannot bring back all the values back - this depends on how many columns(index-by-tables) I specify. If I specify one field, then I can loop and return 12900 records. If I specify 6 of the formal parameters, then I can only return 2000. The following is code to return dummy information. I am testing the limitation of the FOR LOOP of index-by tables. Can anyone tell me what I am doing wrong or is it simply a limitation as to the arraysize.

My ASP page calls a packaged stored procedure and is structured as follows:

Sub Test2
'Code for just one output parameter
Const cProcName = "{call report_query_pack5.content_activity(12920,{results et 15000, TYPE})}"

'Code for all parameters returned

'Const cProcName = "{call report_query_pack5.content_activity(12000,{results et 15000, LEARNING_PATH_NAME, PATHCODE, LEARNING_MODULE_OBJECT_NAME, LAST_NAME, FIRST_NAME, EXTENDED_EMAIL})}"

Dim objResultSet
Dim objCommand
Dim strResult
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = MembershipConn
objCommand.CommandText = cProcName
objCommand.CommandType = 1

'Set input params to the proc
Set objResultSet = Server.CreateObject("ADODB.RecordSet")

Response.Write time() & "<BR>"'*** This writes out the current time
lngstart = timer() '*** This initializes a timer object
Response.Flush

Set objResultSet = objCommand.Execute()
Response.Flush
Response.Write time() & "<BR>"'*** This writes out the current time
Response.Write formatnumber(timer-lngstart,2) & " seconds <BR>"
strResult = "<HTML><HEAD><TITLE></TITLE></HEAD><BODY><TABLE border=1>"
strResult = strResult & "<TR>"

For each objField IN objResultSet.Fields
strResult = strResult & "<td>" & objField.name & "</td>"
NEXT
strResult = strResult & "</TR>"
strResult = strResult & "<TR>"
'* Go thru the result set and build the resulting HTML
Do While Not objResultSet.EOF
For each objItem in objResultSet.Fields
strValue = objItem.value
strResult = strResult & "<TD>" & strValue & "</TD>"
Next
strResult = strResult & "</TR>"
objResultSet.MoveNext
Loop
strResult = strResult & "</TABLE>"
strResult = strResult & objResultSet.RecordCount & " records returned.<BR>"
strResult = strResult & "</BODY></HTML>"
Response.Write strResult
'* Close everything
objResultSet.Close
Set objResultSet = Nothing
Set objCommand = Nothing

End Sub

Response.Write "Calling test...<BR>"
call Test2
Response.Write "Done test.<BR>"[QUOTE]


My package is structured as :

CREATE OR REPLACE PACKAGE report_query_pack5
AS
TYPE tblCharType IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
TYPE tblNumType IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;

PROCEDURE content_activity
(
-- i_ListLM1 IN VARCHAR2,
-- i_ListLP1 IN VARCHAR2,
-- i_Orgid IN NUMBER,
i_LoopCtr IN INTEGER,
TYPE OUT tblCharType
-- LEARNING_PATH_NAME OUT tblCharType,
-- PATHCODE OUT tblNumType,
-- LEARNING_MODULE_OBJECT_NAME OUT tblCharType,
-- MODULECODE OUT tblNumType,
-- KOT_ID OUT tblNumType,
-- MEM_ID OUT tblNumType,
-- LAST_NAME OUT tblCharType,
-- FIRST_NAME OUT tblCharType,
-- EXTENDED_EMAIL OUT tblCharType,
-- PASSMARK OUT tblNumType,
-- LIVEPLAY_COUNT OUT tblNumType,
-- DOWNLOAD_COUNT OUT tblNumType,
-- ASSESSMENT_COUNT OUT tblNumType,
-- RESOURCES_COUNT OUT tblNumType,
-- DESCRIPTION_COUNT OUT tblNumType
);
END report_query_pack5;
/

CREATE OR REPLACE PACKAGE BODY report_query_pack5
AS

PROCEDURE content_activity
(
-- i_ListLM1 IN VARCHAR2,
-- i_ListLP1 IN VARCHAR2,
-- i_Orgid IN NUMBER,
i_LoopCtr IN INTEGER,
TYPE OUT tblCharType
-- LEARNING_PATH_NAME OUT tblCharType,
-- PATHCODE OUT tblNumType,
-- LEARNING_MODULE_OBJECT_NAME OUT tblCharType,
-- MODULECODE OUT tblNumType,
-- KOT_ID OUT tblNumType,
-- MEM_ID OUT tblNumType,
-- LAST_NAME OUT tblCharType,
-- FIRST_NAME OUT tblCharType,
-- EXTENDED_EMAIL OUT tblCharType,
-- PASSMARK OUT tblNumType,
-- LIVEPLAY_COUNT OUT tblNumType,
-- DOWNLOAD_COUNT OUT tblNumType,
-- ASSESSMENT_COUNT OUT tblNumType,
-- RESOURCES_COUNT OUT tblNumType,
-- DESCRIPTION_COUNT OUT tblNumType
)
IS
CURSOR cur_mems (pOrg_id IN NUMBER) IS
SELECT mem_id FROM membership WHERE org_id = pOrg_id;

CURSOR cur_bv (pMem_id IN NUMBER) IS
SELECT last_name, first_name, extended_email FROM bv_user_profile WHERE user_id = pMem_id;

CURSOR cur_mem (pMem_id IN NUMBER) IS
SELECT mem_passmark from member where mem_id = pMem_id;

CURSOR cur_far (pMem_id IN NUMBER) IS
SELECT KO_ID, LIVEPLAY_COUNT, DOWNLOAD_COUNT, ASSESSMENT_COUNT, RESOURCES_COUNT, DESCRIPTION_COUNT FROM flat_activity_report WHERE mem_id = pMem_id AND ko_id IN(SELECT ko_id from chunter.collin_test where ko_id > 90000);

CURSOR cur_ko (pKo_id IN NUMBER) IS
SELECT short_desc, kot_id, DECODE(KOT_ID,1,'Classic Course',3,'Third Party (Custom)',13,'AICC (Custom)',17,'Classic Unit',20,'LRN (Custom)',29,'e3 Learning Object',30,'e3 Learning Module',32,'Secure URL (Custom)','Undefined')AS type FROM knowledge_object WHERE ko_id = pKo_id;

CURSOR cur_lp (pKo_id IN NUMBER) IS
SELECT k.short_desc, k.ko_id, l.child, k.kot_id FROM knowledge_object k, lp_definition l WHERE k.ko_id = l.ko_id AND k.ko_id = pKo_id;

CURSOR far_ms (pOrgID IN NUMBER) IS
-- SELECT far.ko_id, far.mem_id, far.LIVEPLAY_COUNT, far.DOWNLOAD_COUNT, far.ASSESSMENT_COUNT, far.RESOURCES_COUNT, far.DESCRIPTION_COUNT FROM flat_activity_report far, membership ms WHERE far.mem_id = ms.mem_id AND ms.org_id = pOrgID AND far.kot_id in (1,3,4,13,20,30,32,1002) AND far.ko_id in (Select * from chunter.collin_test) AND ROWNUM < 712;
SELECT far.ko_id, far.mem_id, far.LIVEPLAY_COUNT, far.DOWNLOAD_COUNT, far.ASSESSMENT_COUNT, far.RESOURCES_COUNT, far.DESCRIPTION_COUNT FROM flat_activity_report far, membership ms WHERE far.mem_id = ms.mem_id AND ms.org_id = pOrgID AND far.kot_id in (1,3,4,13,20,30,32,1002) AND far.ko_id in (Select * from chunter.collin_test) AND ROWNUM < 712;
--CURSOR cur_kos (pLMList IN VARCHAR2) IS
CURSOR cur_kos IS
select * from chunter.collin_test WHERE KO_ID < 80000;
--select * from chunter.collin_test where ko_id > 2000;
-- where ko_id in (pLMList);
-- Variable Declarations --
intCurrentKoId NUMBER(10) := 0;
-- actCount NUMBER(10) := 0;
strType VARCHAR2(255);
strShortDesc VARCHAR2(255);
intKotID NUMBER(10) := 0;
strLName VARCHAR2(255);
strFName VARCHAR2(255);
strEmail VARCHAR2(255);
intPassmark NUMBER(10) := 0;
intMemId NUMBER(10) := 0;
intLivePlayCount NUMBER(10) := 0;
intDownloadCount NUMBER(10) := 0;
intAssessmentCount NUMBER(10) := 0;
intResourcesCount NUMBER(10) := 0;
intDescriptionCount NUMBER(10) := 0;


BEGIN

FOR actCount in 1..i_LoopCtr LOOP
TYPE(actCount):= 'LM';
-- LEARNING_PATH_NAME(actCount):= '/';
-- PATHCODE(actCount):= -1;
-- LEARNING_MODULE_OBJECT_NAME(actCount):= 'LMON' || actCount;
-- MODULECODE(actCount):= actCount;
-- KOT_ID(actCount):= 1;
-- MEM_ID(actCount):= 184072;
-- LAST_NAME(actCount):= 'HUNTER';
-- FIRST_NAME(actCount):= 'Collin';
-- EXTENDED_EMAIL(actCount):= 'collin_hunter@smartforce.com';
-- PASSMARK(actCount):= 70;
-- LIVEPLAY_COUNT(actCount):= 10;
-- DOWNLOAD_COUNT(actCount):= 20;
-- ASSESSMENT_COUNT(actCount):= 30;
-- RESOURCES_COUNT(actCount):= 40;
-- DESCRIPTION_COUNT(actCount):= 50;
END LOOP; -- far mems list loop --

-- Trap for errors --
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected Error ' || SQLERRM || ' ' || SQLCODE);
END content_activity;
END report_query_pack5;
/
show errors;

Collin Hunter
Web Developer, Platform Development, SmartForce
collin_hunter@smartforce.com