You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by "adamb (JIRA)" <ib...@incubator.apache.org> on 2007/08/27 03:15:31 UTC
[jira] Commented: (IBATIS-453) RefCursor result mapping fails
[ https://issues.apache.org/jira/browse/IBATIS-453?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12522916 ]
adamb commented on IBATIS-453:
------------------------------
Note: In oracle the "user_arguments" view contains information that also shows the (interface) name for each column and its position in the refcursor.
Our current workaround is to output the columnIndex=x for everything in the resultMap instead of the more readable column=x
> RefCursor result mapping fails
> ------------------------------
>
> Key: IBATIS-453
> URL: https://issues.apache.org/jira/browse/IBATIS-453
> Project: iBatis for Java
> Issue Type: Bug
> Components: SQL Maps
> Affects Versions: 2.2.0
> Reporter: adamb
>
> http://opensource.atlassian.com/confluence/oss/pages/viewpage.action?pageId=5653
> I am following this example, except that I am mapping back to a map rather than a java object.
> The result map expects the column names from the query, not from the definition of the ref cursor.
> The ResultSet in UnknownTypeHandler, debugged using Eclipse:
> String result="";
> for(int i=1;i<=rs.getMetaData().getColumnCount();i++){
> result+=String.format("(%s)%s\n",i,rs.getMetaData().getColumnName(i));
> }
> return result;
> shows the following columns in the result set:
> (1)CLIENT_NO
> (2)MSD_NAME
> (3)MSD_BIRTH_DATE
> (4)SERVICE_SHORT_CODE
> (5)SERVICE_STATUS_DESC
> (6)SERVICE_START_DATE
> (7)SERVICE_END_DATE
> (8)MATCH_CASE_ID
> (9)MATCH_RUN_ID
> (10)MATCH_RUN_TYPE_DESC
> (11)MATCH_RUN_DATE
> (12)ASSIGNMENT_TYPE_ID
> (13)URGENT
> (14)SPECIAL_BENEFIT
> (15)HIGH_EARNINGS
> (16)ASSIGNED_USER_NAME
> (17)LAST_EVENT_DETAILS
> (18)CASE_RECORD_STATUS_DESC
> (19)PENDING_EVENT_DETAILS
> (20)CASEWHENMC.CASE_STATUS_CODE=:B10ANDMC.ASSIGNMENT_TYPE_ID!=:B5THENMR.OOT_TO_OPEN_DATEWHENMC.CASE_STATUS_CODEIN(:B9,:B8,:B7,:B6,:B11)ANDMC.ASSIGNMENT_TYPE_ID!=:B5THENMR.OOT_NO_OUTCOME_DATEWHENMC.CASE_STATUS_CODE=:B10ANDMC.ASSIGNMENT_TYPE_ID=:B5THENMR.ABC_OO
> (21)DISTRICT_NAME
> (22)GENDER
> (23)UNDER_INVESTIGATION
> (24)COMMISSION_SALES
> (25)CONTACT_869A
> (26)MULTI_CLIENT
> (27)LINK_ID
> (28)MATCHED_SERVICES
> (29)MATCHED_NAME
> (30)NVL(MREC.MATCHED_BIRTH_DATE,MC.MSD_BIRTH_DATE)
> (31)RELATION_IND
> (32)OS_PENSION
> (33)DPB_CSI
> (34)MATCH_IND
> (35)EMP_LEGAL_ACTION
> (36)MP
> (37)MATCH_IND_2
> (38)MATCH_LEVEL
> (39)AGENCY_DETAIL_TYPE1
> (40)AGENCY_IND1
> (41)AGENCY_REFERENCE2
> (42)AGENCY_REFERENCE1
> (43)AGENCY_DETAIL_TYPE2
> (44)REL
> (45)STDT
> However the definition is:
> TYPE MatchCase IS RECORD (
> client_no ndm_match_case.client_no%TYPE,
> msd_name VARCHAR2(100),
> msd_birth_date ndm_match_case.msd_birth_date%TYPE,
> service_short_code ndm_service.service_short_code%TYPE,
> service_status_desc ndm_service_status.service_status_desc%TYPE,
> service_start_date ndm_match_case.service_start_date%TYPE,
> service_end_date ndm_match_case.service_end_date%TYPE,
> match_case_id ndm_match_case.match_case_id%TYPE,
> match_run_id ndm_match_case.match_run_id%TYPE,
> match_run_type_desc ndm_match_run_type.match_run_type_desc%TYPE,
> match_run_date ndm_match_run.match_run_date%TYPE,
> assignment_type_id ndm_match_case.assignment_type_id%TYPE,
> urgent VARCHAR2(3),
> special_benefit VARCHAR2(2),
> high_earnings VARCHAR2(2),
> assigned_name VARCHAR2(121),
> last_event_details VARCHAR2(100),
> case_record_status_desc ndm_case_record_status.case_record_status_desc%TYPE,
> pending_event_details VARCHAR2(100),
> milestone_date ndm_match_run.oot_to_open_date%TYPE,
> district_name ndm_district.district_name%TYPE,
> gender VARCHAR2(10),
> under_investigation VARCHAR2(3),
> commission_sales VARCHAR2(3),
> contact_869a VARCHAR2(5),
> multi_client VARCHAR2(1),
> link_id ndm_match_case.link_id%TYPE,
> matched_services VARCHAR2(100),
> matched_name VARCHAR2(122),
> matched_birth_date DATE,
> relation_ind ndm_match_case.relation_ind%TYPE,
> os_pension VARCHAR2(3),
> dpb_csi VARCHAR2(3),
> match_ind ndm_match_record.match_ind%TYPE,
> emp_legal_action VARCHAR2(2),
> mp VARCHAR2(3),
> match_ind_2 ndm_match_record.match_ind%TYPE,
> match_level ndm_match_record.match_level%TYPE,
> agency_detail_type1 ndm_match_record.agency_detail_type1%TYPE,
> agency_ind1 ndm_match_record.agency_ind1%TYPE,
> agency_reference2 ndm_match_record.agency_reference2%TYPE,
> agency_reference1 ndm_match_record.agency_reference1%TYPE,
> agency_detail_type2 ndm_match_record.agency_detail_type2%TYPE,
> rel VARCHAR2(5),
> stdt VARCHAR2(5)
> );
> TYPE MatchCaseCur IS REF CURSOR RETURN MatchCase;
> As you can see, if I try to get column "assigned_name", "milestone_date", or "matched_birth_date" it will fail.
> SQLMAP:
> <resultMap id="viewCaseCaseHeaderBaseQueryResultMap" class="java.util.HashMap">
> <result property="clientNo" column="CLIENT_NO"/>
> <result property="multiClient" column="MULTI_CLIENT"/>
> <result property="msdName" column="MSD_NAME"/>
> <result property="linkId" column="LINK_ID"/>
> <result property="agencyDetailType1" column="AGENCY_DETAIL_TYPE1"/>
> <result property="agencyInd1" column="AGENCY_IND1"/>
> <result property="agencyReference2" column="AGENCY_REFERENCE2"/>
> <result property="agencyReference1" column="AGENCY_REFERENCE1"/>
> <result property="agencyDetailType2" column="AGENCY_DETAIL_TYPE2"/>
> <result property="matchedServices" column="MATCHED_SERVICES"/>
> <result property="mp" column="MP"/>
> <result property="matchedName" column="MATCHED_NAME"/>
> <result property="matchedBirthDate" column="MATCHED_BIRTH_DATE"/>
> <result property="relationInd" column="RELATION_IND"/>
> <result property="msdBirthDate" column="MSD_BIRTH_DATE"/>
> <result property="serviceShortCode" column="SERVICE_SHORT_CODE"/>
> <result property="serviceStatusDesc" column="SERVICE_STATUS_DESC"/>
> <result property="serviceStartDate" column="SERVICE_START_DATE"/>
> <result property="serviceEndDate" column="SERVICE_END_DATE"/>
> <result property="districtName" column="DISTRICT_NAME"/>
> <result property="gender" column="GENDER"/>
> <result property="matchCaseId" column="MATCH_CASE_ID"/>
> <result property="matchRunTypeDesc" column="MATCH_RUN_TYPE_DESC"/>
> <result property="matchRunId" column="MATCH_RUN_ID"/>
> <result property="matchRunDate" column="MATCH_RUN_DATE"/>
> <result property="assignmentTypeId" column="ASSIGNMENT_TYPE_ID"/>
> <result property="matchInd2" column="MATCH_IND_2"/>
> <result property="matchLevel" column="MATCH_LEVEL"/>
> <result property="osPension" column="OS_PENSION"/>
> <result property="dpbCsi" column="DPB_CSI"/>
> <result property="urgent" column="URGENT"/>
> <result property="specialBenefit" column="SPECIAL_BENEFIT"/>
> <result property="highEarnings" column="HIGH_EARNINGS"/>
> <result property="rel" column="REL"/>
> <result property="assignedName" column="ASSIGNED_NAME"/>
> <result property="lastEventDetails" column="LAST_EVENT_DETAILS"/>
> <result property="matchInd" column="MATCH_IND"/>
> <result property="contact869a" column="CONTACT_869A"/>
> <result property="commissionSales" column="COMMISSION_SALES"/>
> <result property="underInvestigation" column="UNDER_INVESTIGATION"/>
> <result property="empLegalAction" column="EMP_LEGAL_ACTION"/>
> <result property="stdt" column="STDT"/>
> <result property="caseRecordStatusDesc" column="CASE_RECORD_STATUS_DESC"/>
> <result property="pendingEventDetails" column="PENDING_EVENT_DETAILS"/>
> <result property="milestoneDate" column="MILESTONE_DATE"/>
> </resultMap>
> <parameterMap id="viewCaseCaseHeaderBaseQueryParameterMap" class="map">
> <parameter property="result" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT" resultMap="viewCaseCaseHeaderBaseQueryResultMap"/>
> <parameter property="pMatchCaseId"/>
> </parameterMap>
> <procedure id="viewCaseCaseHeaderBaseQuery"
> parameterMap="viewCaseCaseHeaderBaseQueryParameterMap"
> >{call pkg_match_case.get_match_case(?,?)}</procedure>
> If I change the sqlMap from ORACLECURSOR to MATCHCASECUR I get:
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in nz/govt/msd/aimos/viewCase/server/CaseHeaderSqlMap.xml.
> --- The error occurred while executing query procedure.
> --- Check the {call pkg_match_case.get_match_case(?,?)}.
> --- Check the output parameters (register output parameters failed).
> --- Cause: java.sql.SQLException: Invalid column type
> Caused by: java.sql.SQLException: Invalid column type
> Note: This is just one of thousands of autogenerated mappings, so I can't just add aliases to every query in the database, or rename the map aliases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.