ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Robert Glover <robertglove...@yahoo.com>
Subject iBatis with (Oracle) Stored Procedures that return multiple rows
Date Fri, 11 Apr 2008 14:30:45 GMT
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
======================================



Mime
View raw message