iBatis with (Oracle) Stored Procedures that return multiple rows

[Robert Glover <ro...@yahoo.com>]

Hello,

  I am a great, great fan of iBatis Abator.  I use Abator in all my work.  Today however I must drop down into iBatis for a special situation involving paging through a large data set.  I can do this two different ways.  The way I will probably do because it's easier to code, is to use straight iBatis and a complicated SQL statement.  However, the preferable way would be for me to use iBatis to access an Oracle stored procedure (which I would have to write) that returned multiple rows.  The problem is that the only example I have of using iBatis with a stored procedure is pages 117 to 121 of the book "iBatis in Action" by Begin, Goodin, and Meadors.  Unfortunately, the book does not give an example of how to use iBatis when multiple rows are returned.
   Any help/pointers/links very much appreciated.  I am pasting the SQL query below which is the subject of my question.  It is not really necessary to answer my question, but I include it (below) for completeness).
   Thank you in advance.
======================================
select 
    "WIN_ROW_NUM", 
    "ID_POV_AGT_RAW",
    "AGENT_INTERNAL_ID",
    "AGENT_NAME",
    "AGENT_CITY",
    "AGENT_STATE",
    "AGENT_TIN",
    "OBLIG_INTERNAL_ID",
    "OBLIG_NAME",
    "OBLIG_NAME_SHORT",
    "OBLIG_CITY",
    "OBLIG_STATE",
    "OBLIG_TIN",
    "CREDIT_INTERNAL_ID",
    "CREDIT_FIRST_INTERNAL_ID",
    "CREDIT_CUSIP",
    "CREDIT_ORIGIN_DATE",
    "CREDIT_MATURE_DATE",
    "CREDIT_COMMIT_EXP_GLOBAL",
    "CREDIT_UTIL_EXP_GLOBAL",
    "PART_INTERNAL_ID",
    "PART_NAME",
    "PART_NAME_SHORT",
    "PART_CITY",
    "PART_STATE",
    "PART_TIN",
    "PART_COMMIT_EXP_SHARE",
    "PART_UTIL_EXP_SHARE",
    "AGENT_RSSD_ID",
    "PART_RSSD_ID",
    "RAW_SOURCE",
    "RAW_SOURCE_DATE",
    "RAW_PART_IS_RPT_BANK_YN",
    "FINAL_TOTAL_CONF_LABEL",
    "RSSD_REPORTING_BANK_AGT",
    "RSSD_REPORTING_BANK_PART",
    "FK_BAT_SUM_AGT_2_PARTS",
    "FK_POV_AGT_RAW",
    "FK_POV_PART_RAW"  
from (
SELECT
    row_number() over (order by FK_POV_AGT_RAW)   as WIN_ROW_NUM, 
    "POV_AGT_RAW"."ID_POV_AGT_RAW",
    "POV_AGT_RAW"."AGENT_INTERNAL_ID",
    "POV_AGT_RAW"."AGENT_NAME",
    "POV_AGT_RAW"."AGENT_CITY",
    "POV_AGT_RAW"."AGENT_STATE",
    "POV_AGT_RAW"."AGENT_TIN",
    "POV_AGT_RAW"."OBLIG_INTERNAL_ID",
    "POV_AGT_RAW"."OBLIG_NAME",
    "POV_AGT_RAW"."OBLIG_NAME_SHORT",
    "POV_AGT_RAW"."OBLIG_CITY",
    "POV_AGT_RAW"."OBLIG_STATE",
    "POV_AGT_RAW"."OBLIG_TIN",
    "POV_AGT_RAW"."CREDIT_INTERNAL_ID",
    "POV_AGT_RAW"."CREDIT_FIRST_INTERNAL_ID",
    "POV_AGT_RAW"."CREDIT_CUSIP",
    "POV_AGT_RAW"."CREDIT_ORIGIN_DATE",
    "POV_AGT_RAW"."CREDIT_MATURE_DATE",
    "POV_AGT_RAW"."CREDIT_COMMIT_EXP_GLOBAL",
    "POV_AGT_RAW"."CREDIT_UTIL_EXP_GLOBAL",
    "POV_AGT_RAW"."PART_INTERNAL_ID",
    "POV_AGT_RAW"."PART_NAME",
    "POV_AGT_RAW"."PART_NAME_SHORT",
    "POV_AGT_RAW"."PART_CITY",
    "POV_AGT_RAW"."PART_STATE",
    "POV_AGT_RAW"."PART_TIN",
    "POV_AGT_RAW"."PART_COMMIT_EXP_SHARE",
    "POV_AGT_RAW"."PART_UTIL_EXP_SHARE",
    "POV_AGT_RAW"."AGENT_RSSD_ID",
    "POV_AGT_RAW"."PART_RSSD_ID",
    "POV_AGT_RAW"."RAW_SOURCE",
    "POV_AGT_RAW"."RAW_SOURCE_DATE",
    "POV_AGT_RAW"."RAW_PART_IS_RPT_BANK_YN",
    "BAT_SUM_DET_AGT_2_PARTS"."FINAL_TOTAL_CONF_LABEL",
    "BAT_SUM_DET_AGT_2_PARTS"."RSSD_REPORTING_BANK_AGT",
    "BAT_SUM_DET_AGT_2_PARTS"."RSSD_REPORTING_BANK_PART",
    "BAT_SUM_DET_AGT_2_PARTS"."FK_BAT_SUM_AGT_2_PARTS",
    "BAT_SUM_DET_AGT_2_PARTS"."FK_POV_AGT_RAW",
    "BAT_SUM_DET_AGT_2_PARTS"."FK_POV_PART_RAW" 
FROM
    "DAI"."POV_AGT_RAW" "POV_AGT_RAW" 
        INNER JOIN "DAI"."BAT_SUM_DET_AGT_2_PARTS" "BAT_SUM_DET_AGT_2_PARTS" 
        ON "POV_AGT_RAW"."ID_POV_AGT_RAW" = "BAT_SUM_DET_AGT_2_PARTS".
        "FK_POV_AGT_RAW" 
WHERE
    ("BAT_SUM_DET_AGT_2_PARTS"."FINAL_TOTAL_CONF_LABEL" ='MEDIUM') AND
    ("BAT_SUM_DET_AGT_2_PARTS"."RSSD_REPORTING_BANK_PART" =98765) AND
    ("BAT_SUM_DET_AGT_2_PARTS"."FK_BAT_SUM_AGT_2_PARTS" =5105)
)
where WIN_ROW_NUM between 2 and 9
======================================