Invoke oracle function returing oracle type

["Ahuja, Ruchi" <ru...@fmr.com>]

Hi,

I am trying to call an Oracle function using ibatis. While doing so i
get the following error - 
--- Check the output parameters (register output parameters failed).  
--- Cause: java.sql.SQLException: Parameter Type Conflict:
sqlType=-99999999

Oracle function is as follows -
 FUNCTION getClientInfo ( clientId IN varchar2, serviceName IN varchar2,
isNew char) RETURN CLIENTINFO; 

Here CLIENTINFO is Oracle OBJECT TYPE.

The ibatis sqlMap is -
 <sqlMap> 
<resultMap id="GetClientInfo-result-map" class="client.ClientInfo"> 
.... 
</resultMap> 
<parameterMap id="GetClientInfo-param-map" class="java.util.Map"> 
<parameter property="result" jdbcType="OBJECT" typeName="CLIENTINFO"
mode="OUT"/> 
<parameter property="clientId" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN" /> 
<parameter property="serviceName" jdbcType="VARCHAR2"
javaType="java.lang.String" mode="IN" /> 
<parameter property="isNew" jdbcType="CHAR" javaType="java.lang.String"
mode="IN" /> 
</parameterMap> 
<procedure id="GetClientInfo" parameterMap="GetClientInfo-param-map"
resultMap="GetClientInfo-result-map"> 
{? = call getClientInfo(?, ?, ?) }</procedure> 
</sqlMap> 
	 
My DAO code is as follows - 
Map params = new HashMap(); 
params.put("clientId", clientId); 
params.put("serviceName", serviceName); 
params.put("isNew", "Y"); 
ClientInfo result =
(ClientInfo)getSqlMapClientTemplate().queryForObject("GetClientInfo",
params); 
	 
Can anyone tell me what I am doing wrong.

Regards,
Ruchi

"Any comments or statements made in this email are not necessarily those
of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity
Investments group companies. The information transmitted is intended
only for the person or entity to which it is addressed and may contain
confidential and/or privileged material. If you have received this in
error, please contact the sender and delete the material from any
computer. All e-mails sent from or to Fidelity Business Services India
Pvt. Ltd. may be subject to our monitoring procedures."

Re: Invoke oracle function returing oracle type

[Daniel Henrique Ferreira e Silva <dh...@gmail.com>]

Ruchi,

iBATIS can't figure out complex types. You'll have to write a custom
type handler to use CLIENTINFO complex type.
Look at iBATIS wiki page. There you'll find a sample of how to write
one custom type handler.

Hope that helped,
Daniel Silva.

On 10/5/05, Ahuja, Ruchi <ru...@fmr.com> wrote:
>
>
> Hi,
>
> I am trying to call an Oracle function using ibatis. While doing so i get
> the following error -
> --- Check the output parameters (register output parameters failed).
> --- Cause: java.sql.SQLException: Parameter Type Conflict: sqlType=-99999999
>
> Oracle function is as follows -
>  FUNCTION getClientInfo ( clientId IN varchar2, serviceName IN varchar2,
> isNew char) RETURN CLIENTINFO;
>
> Here CLIENTINFO is Oracle OBJECT TYPE.
>
> The ibatis sqlMap is -
>  <sqlMap>
> <resultMap id="GetClientInfo-result-map" class="client.ClientInfo">
> ....
> </resultMap>
> <parameterMap id="GetClientInfo-param-map" class="java.util.Map">
> <parameter property="result" jdbcType="OBJECT" typeName="CLIENTINFO"
> mode="OUT"/>
> <parameter property="clientId" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN" />
> <parameter property="serviceName" jdbcType="VARCHAR2"
> javaType="java.lang.String" mode="IN" />
> <parameter property="isNew" jdbcType="CHAR" javaType="java.lang.String"
> mode="IN" />
> </parameterMap>
> <procedure id="GetClientInfo"
> parameterMap="GetClientInfo-param-map"
> resultMap="GetClientInfo-result-map">
> {? = call getClientInfo(?, ?, ?) }</procedure>
> </sqlMap>
>
> My DAO code is as follows -
> Map params = new HashMap();
> params.put("clientId", clientId);
> params.put("serviceName", serviceName);
> params.put("isNew", "Y");
> ClientInfo result =
> (ClientInfo)getSqlMapClientTemplate().queryForObject("GetClientInfo",
> params);
>
> Can anyone tell me what I am doing wrong.
>
> Regards,
> Ruchi
>
> "Any comments or statements made in this email are not necessarily those of
> Fidelity Business Services India Pvt. Ltd. or any of the Fidelity
> Investments group companies. The information transmitted is intended only
> for the person or entity to which it is addressed and may contain
> confidential and/or privileged material. If you have received this in error,
> please contact the sender and delete the material from any computer. All
> e-mails sent from or to Fidelity Business Services India Pvt. Ltd. may be
> subject to our monitoring procedures."