Can the IBatis Procedure call invoke a function within a Package in on a DB ?

["Murray, AlanM" <Al...@agriculture.gov.ie>]

Hi, 

Can the IBatis Procedure call invoke a function within a Package on a DB ?  Im attempting to invoke a function which exists on a remote DB using the IBatis procedure call.  Looked around the net for examples of the IBatis procedure call which returns a resultmap but could nt find any clear solution.
I 've tried executing the following code in my sqlMap.xml :

<sqlMap namespace="ie.gov.agriculture.sps.app.dao.impl.IForisSQLMapDAO">

	<typeAlias alias="IForisData" type="ie.gov.agriculture.sps.app.dao.data.IForisData"/>
	
	<resultMap class="IForisData" id="iForisResultByParcelID">
 		<result property="exists" column="ExistInIforis" nullValue=" " />
	    <result property="plantingDate" column="PlantingDate" nullValue=" " />
	    <result property="herdNum" column="HerdNum" nullValue=" " />
	    <result property="contractNum" column="ContractId" nullValue=" " />
	    <result property="dualClaim" column="DualClaim" nullValue=" " />	
	    <result property="dualClaimType" column="DualClaimType" nullValue="0" />
	</resultMap>	
	
	<parameterMap id="iForisResultByParcelIDParams" class="map">
		<parameter property="landUnitUniqueId" jdbcType="VARCHAR2" javaType="java.lang.String" mode="IN"/>
		<!--<parameter property="result" jdbcType="ORACLECURSOR" mode="OUT"/>-->
	</parameterMap>

  	<procedure id="findIForisClaims" parameterMap="iForisResultByParcelIDParams" resultMap="iForisResultByParcelID">
	 { call PKCO_IF_DUAL_CLAIMS.DUAL_CLAIM_QUERY(?) }
	</procedure>	
	

</sqlMap>

This gives the following error :

06/05/31 16:11:02 ie.gov.agriculture.sps.global.exceptions.SPSBusinessException: com.ibatis.dao.client.DaoException: Failed to queryForList - id [ie.gov.agriculture.sps.app.dao.impl.IForisSQLMapDAO.findIForisClaims], parameterObject [{landUnitUniqueId=15020597}].  Cause: com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in ie/gov/agriculture/sps/app/dao/xml/IForisData.xml.  
--- The error occurred while applying a parameter map.  
--- Check the ie.gov.agriculture.sps.app.dao.impl.IForisSQLMapDAO.iForisResultByParcelIDParams.  
--- Check the statement (update procedure failed).  
--- Cause: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00221: 'DUAL_CLAIM_QUERY' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Does this error mean that i can not invoke a function on the remote DB using the keyword procedure ?and do I just have to setup a procedure on the DB  ? Or is it possible to do use an alternative keyword call in IBatis, i know its possible to define parameters of mode "Out" ? but i was hoping to return a result map.  
Any help on any of the above would be great.

Thanks,
Al.



**********************************************************************
***********  Department of Agriculture and Food ***************

The information contained in this email and in any
attachments is confidential and is designated solely
for the attention and use of the intended recipient(s).
This information may be subject to legal and professional
privilege.  If you are not an intended recipient of
this email, you must not use, disclose, copy,
distribute or retain this message or any part of it.
If you have received this email in error, please
notify the sender immediately and delete all copies of
this email from your computer system(s).
**********************************************************************

Re: Can the IBatis Procedure call invoke a function within a Package in on a DB ?

[Beemsterboer Software <ha...@beemsterboer-software.nl>]

Hi,

It should be possible to call a function within a DB package.
The error doesn't seem to be related to iBatis.
Can you successfully execute the SQL statement from a DB client?
If yes, there may be a problem with the configuration of your JDBC 
connection.

Greetings,
Hans.

Murray, AlanM wrote:
> Hi, 
>
> Can the IBatis Procedure call invoke a function within a Package on a DB ?  Im attempting to invoke a function which exists on a remote DB using the IBatis procedure call.  Looked around the net for examples of the IBatis procedure call which returns a resultmap but could nt find any clear solution.
> I 've tried executing the following code in my sqlMap.xml :
>
> <sqlMap namespace="ie.gov.agriculture.sps.app.dao.impl.IForisSQLMapDAO">
>
> 	<typeAlias alias="IForisData" type="ie.gov.agriculture.sps.app.dao.data.IForisData"/>
> 	
> 	<resultMap class="IForisData" id="iForisResultByParcelID">
>  		<result property="exists" column="ExistInIforis" nullValue=" " />
> 	    <result property="plantingDate" column="PlantingDate" nullValue=" " />
> 	    <result property="herdNum" column="HerdNum" nullValue=" " />
> 	    <result property="contractNum" column="ContractId" nullValue=" " />
> 	    <result property="dualClaim" column="DualClaim" nullValue=" " />	
> 	    <result property="dualClaimType" column="DualClaimType" nullValue="0" />
> 	</resultMap>	
> 	
> 	<parameterMap id="iForisResultByParcelIDParams" class="map">
> 		<parameter property="landUnitUniqueId" jdbcType="VARCHAR2" javaType="java.lang.String" mode="IN"/>
> 		<!--<parameter property="result" jdbcType="ORACLECURSOR" mode="OUT"/>-->
> 	</parameterMap>
>
>   	<procedure id="findIForisClaims" parameterMap="iForisResultByParcelIDParams" resultMap="iForisResultByParcelID">
> 	 { call PKCO_IF_DUAL_CLAIMS.DUAL_CLAIM_QUERY(?) }
> 	</procedure>	
> 	
>
> </sqlMap>
>
> This gives the following error :
>
> 06/05/31 16:11:02 ie.gov.agriculture.sps.global.exceptions.SPSBusinessException: com.ibatis.dao.client.DaoException: Failed to queryForList - id [ie.gov.agriculture.sps.app.dao.impl.IForisSQLMapDAO.findIForisClaims], parameterObject [{landUnitUniqueId=15020597}].  Cause: com.ibatis.common.jdbc.exception.NestedSQLException:   
> --- The error occurred in ie/gov/agriculture/sps/app/dao/xml/IForisData.xml.  
> --- The error occurred while applying a parameter map.  
> --- Check the ie.gov.agriculture.sps.app.dao.impl.IForisSQLMapDAO.iForisResultByParcelIDParams.  
> --- Check the statement (update procedure failed).  
> --- Cause: java.sql.SQLException: ORA-06550: line 1, column 7:
> PLS-00221: 'DUAL_CLAIM_QUERY' is not a procedure or is undefined
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> Does this error mean that i can not invoke a function on the remote DB using the keyword procedure ?and do I just have to setup a procedure on the DB  ? Or is it possible to do use an alternative keyword call in IBatis, i know its possible to define parameters of mode "Out" ? but i was hoping to return a result map.  
> Any help on any of the above would be great.
>
> Thanks,
> Al.
>
>