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
======================================
