You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by "DelCastillo, Alberto" <Al...@axa-canada.com> on 2005/10/17 15:12:51 UTC
Problem calling a oracle stored procedure
I have problem calling a stored procedure (I'm using
oracle 10g).
===================================================
This is my iBatis-sqlMap-CreditScoreCache file:
<parameterMap id="clientScoreParams"
class="com.axacanada.creditscore.implementation.cache.CreditScoreCacheAc
tion"
>
<parameter property="action" jdbcType="VARCHAR"
nullValue=""
javaType=" java.lang.String" mode="IN"/>
<parameter property="entry.subject.clientNo"
jdbcType="INTEGER" nullValue="0"
javaType="int" mode="INOUT"/>
<parameter property="entry.subject.parentClientNo"
jdbcType="INTEGER" nullValue="0"
javaType="int" mode="IN"/>
<parameter property="entry.subject.companyNo "
jdbcType="INTEGER" nullValue="0"
javaType="int" mode="IN"/>
<parameter property="entry.subject.subjectType"
jdbcType="VARCHAR" nullValue=""
javaType="java.lang.String" mode="IN"/>
<parameter property="entry.subject.surname"
jdbcType="VARCHAR" nullValue=""
javaType="java.lang.String " mode="IN"/>
<parameter property="entry.subject.givenName"
jdbcType="VARCHAR" nullValue=""
javaType="java.lang.String" mode="IN"/>
<parameter property="entry.subject.middleName"
jdbcType="VARCHAR" nullValue=""
javaType="java.lang.String" mode="IN"/>
<parameter property=" entry.subject.formattedBirthDate"
jdbcType="VARCHAR" nullValue=""
javaType="java.lang.String" mode="IN"/>
<parameter property="entry.subject.address.city "
jdbcType="VARCHAR" nullValue=""
javaType="java.lang.String" mode="IN"/>
<parameter property="entry.subject.address.postalCode"
jdbcType="VARCHAR" nullValue=""
javaType="java.lang.String" mode="IN"/>
<parameter property="entry.subject.address.country"
jdbcType="VARCHAR" nullValue=""
javaType=" java.lang.String" mode="IN"/>
<parameter property="entry.subject.address.province"
jdbcType="VARCHAR" nullValue=""
javaType="java.lang.String" mode="IN"/>
<parameter property="entry.subject.address.street"
jdbcType="VARCHAR" nullValue=""
javaType="java.lang.String" mode="IN"/>
<parameter property=" entry.subject.phoneNumberAsLong"
jdbcType="INTEGER" nullValue="0"
javaType="java.lang.Long" mode="IN"/>
<parameter property="entry.subject.noSeqContract "
jdbcType="SMALLINT" nullValue="0"
javaType="java.lang.Short" mode="IN" />
<parameter
property="entry.subject.creditScore.providerCode"
jdbcType="INTEGER" nullValue="0"
javaType="java.lang.Integer" mode="IN"/>
<parameter
property="entry.subject.creditScore.lnkCreditScoreCacheReportModel.repor
tDate "
jdbcType="TIMESTAMP"
javaType="java.sql.Timestamp" mode="IN"/>
<parameter
property="entry.subject.creditScore.providerCreditScore"
jdbcType="INTEGER" nullValue="0"
javaType="java.lang.Integer" mode="IN" />
<parameter
property="entry.subject.creditScore.internalCreditScore"
jdbcType="VARCHAR" nullValue=""
javaType="java.lang.String" mode="IN"/>
<parameter
property="entry.subject.creditScore.consentResponsibleName"
jdbcType="VARCHAR" nullValue=""
javaType=" java.lang.String" mode="IN"/>
<parameter
property="entry.subject.creditScore.consentDateFormatted"
jdbcType="VARCHAR" nullValue=""
javaType="java.lang.String " mode="IN"/>
<parameter property="entry.entryType"
jdbcType="VARCHAR" nullValue=""
javaType="java.lang.String" mode="IN"/>
<parameter
property="entry.subject.creditScore.lnkCreditScoreCacheReportModel.repor
tDetail"
jdbcType="VARCHAR" nullValue=""
javaType="java.lang.String" mode="IN"/>
<parameter property="entry.subject.userCreationCode"
jdbcType="VARCHAR" nullValue=""
javaType="java.lang.String" mode="IN"/>
<parameter property="returnCode" jdbcType="NUMERIC"
nullValue="0"
javaType="java.lang.Integer" mode="OUT"/>
</parameterMap>
<procedure id="insertCreditScore2"
parameterMap="clientScoreParams" >
CALL OUCSS01.CRE_COTE_CR2(?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
</procedure>
===================================================
Thats the trace of call of the stored procedure:
DEBUG [16 oct. 05 19:19:30.519][PreparedStatement]
{pstm-100009} PreparedStatement: CALL
OUCSS01.CRE_COTE_CR2(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
DEBUG [16 oct. 05 19:19: 30.519][PreparedStatement]
{pstm-100009} Parameters: [cc, null, null, 36, P,
Benfeito, Carlos, null, 10/14/1976, Brossard,
J4Y3J1AAAA, CAN, QC, 7570 Lautrec, null, null, 5,
null, 781, A, Carlos Benfeito, 10/16/2005, H,
<xml>aaaaaaaa</xml>, CSS]
DEBUG [16 oct. 05 19:19:30.519][PreparedStatement]
{pstm-100009} Types: [java.lang.String, null, null,
java.lang.Integer, java.lang.String, java.lang.String,
java.lang.String , null, java.lang.String,
java.lang.String, java.lang.String, java.lang.String,
java.lang.String, java.lang.String, null, null,
java.lang.Integer, null, java.lang.Integer,
java.lang.String, java.lang.String, java.lang.String ,
java.lang.String, java.lang.String, java.lang.String]
===================================================
This is the stack trace:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
iBatis-sqlMap-CreditScoreCache.xml.
--- The error occurred while applying a parameter map.
--- Check the CreditScoreCache.clientScoreParams.
--- Check the statement (update procedure failed).
--- Cause: java.lang.NullPointerException
Caused by: java.lang.NullPointerException
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdat
e(GeneralStatement.java:89)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update
(SqlMapExecutorDelegate.java:312)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl
.java:74)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.j
ava :49)
======================================================
it seems to have problems to set parameters in runtime
execution.
I verified all the parameters, I have some null values
but I worked before with iBatis using null values as
parameters with JTOpen driver (DB2 400) without
problems.
Any suggestions to resolve this problem?
Alberto
Alberto Del Castillo
_____
"Ce message est confidentiel, a l'usage exclusif du destinataire
ci-dessus et son contenu ne represente en aucun cas un engagement de la
part de AXA, sauf en cas de stipulation expresse et par ecrit de la part
de AXA. Toute publication, utilisation ou diffusion, meme partielle,
doit etre autorisee prealablement. Si vous n'etes pas destinataire de ce
message, merci d'en avertir immediatement l'expediteur."
"This e-mail message is confidential, for the exclusive use of the
addressee and its contents shall not constitute a commitment by AXA,
except as otherwise specifically provided in writing by AXA. Any
unauthorized disclosure, use or dissemination, either whole or partial,
is prohibited. If you are not the intended recipient of the message,
please notify the sender immediately."
Re: Problem calling a oracle stored procedure
Posted by Vincent NICOLAS <vi...@teamlog.com>.
Does the procedure return anything ?
You should use a insert() method to call the procedure in the Java code
to avoid iBatis to try and retrieve a value from the procedure.
DelCastillo, Alberto a écrit :
> I have problem calling a stored procedure (I'm using
> oracle 10g).
> ===================================================
> This is my iBatis-sqlMap-CreditScoreCache file:
>
> <parameterMap id="clientScoreParams"
> class="com.axacanada.creditscore.implementation.cache.CreditScoreCacheAction"
> >
> <parameter property="action" jdbcType="VARCHAR"
> nullValue=""
> javaType=" java.lang.String" mode="IN"/>
> <parameter property="entry.subject.clientNo"
> jdbcType="INTEGER" nullValue="0"
> javaType="int" mode="INOUT"/>
> <parameter property="entry.subject.parentClientNo"
> jdbcType="INTEGER" nullValue="0"
> javaType="int" mode="IN"/>
> <parameter property="entry.subject.companyNo "
> jdbcType="INTEGER" nullValue="0"
> javaType="int" mode="IN"/>
> <parameter property="entry.subject.subjectType"
> jdbcType="VARCHAR" nullValue=""
> javaType="java.lang.String" mode="IN"/>
> <parameter property="entry.subject.surname"
> jdbcType="VARCHAR" nullValue=""
> javaType="java.lang.String " mode="IN"/>
> <parameter property="entry.subject.givenName"
> jdbcType="VARCHAR" nullValue=""
> javaType="java.lang.String" mode="IN"/>
> <parameter property="entry.subject.middleName"
> jdbcType="VARCHAR" nullValue=""
> javaType="java.lang.String" mode="IN"/>
> <parameter property=" entry.subject.formattedBirthDate"
> jdbcType="VARCHAR" nullValue=""
> javaType="java.lang.String" mode="IN"/>
> <parameter property="entry.subject.address.city "
> jdbcType="VARCHAR" nullValue=""
> javaType="java.lang.String" mode="IN"/>
> <parameter property="entry.subject.address.postalCode"
> jdbcType="VARCHAR" nullValue=""
> javaType="java.lang.String" mode="IN"/>
> <parameter property="entry.subject.address.country"
> jdbcType="VARCHAR" nullValue=""
> javaType=" java.lang.String" mode="IN"/>
> <parameter property="entry.subject.address.province"
> jdbcType="VARCHAR" nullValue=""
> javaType="java.lang.String" mode="IN"/>
> <parameter property="entry.subject.address.street"
> jdbcType="VARCHAR" nullValue=""
> javaType="java.lang.String" mode="IN"/>
> <parameter property=" entry.subject.phoneNumberAsLong"
> jdbcType="INTEGER" nullValue="0"
> javaType="java.lang.Long" mode="IN"/>
> <parameter property="entry.subject.noSeqContract "
> jdbcType="SMALLINT" nullValue="0"
> javaType="java.lang.Short" mode="IN" />
> <parameter
> property="entry.subject.creditScore.providerCode"
> jdbcType="INTEGER" nullValue="0"
> javaType="java.lang.Integer" mode="IN"/>
> <parameter
> property="entry.subject.creditScore.lnkCreditScoreCacheReportModel.reportDate
> "
> jdbcType="TIMESTAMP"
> javaType="java.sql.Timestamp" mode="IN"/>
> <parameter
> property="entry.subject.creditScore.providerCreditScore"
> jdbcType="INTEGER" nullValue="0"
> javaType="java.lang.Integer" mode="IN" />
> <parameter
> property="entry.subject.creditScore.internalCreditScore"
> jdbcType="VARCHAR" nullValue=""
> javaType="java.lang.String" mode="IN"/>
> <parameter
> property="entry.subject.creditScore.consentResponsibleName"
> jdbcType="VARCHAR" nullValue=""
> javaType=" java.lang.String" mode="IN"/>
> <parameter
> property="entry.subject.creditScore.consentDateFormatted"
> jdbcType="VARCHAR" nullValue=""
> javaType="java.lang.String " mode="IN"/>
> <parameter property="entry.entryType"
> jdbcType="VARCHAR" nullValue=""
> javaType="java.lang.String" mode="IN"/>
> <parameter
> property="entry.subject.creditScore.lnkCreditScoreCacheReportModel.reportDetail"
> jdbcType="VARCHAR" nullValue=""
> javaType="java.lang.String" mode="IN"/>
> <parameter property="entry.subject.userCreationCode"
> jdbcType="VARCHAR" nullValue=""
> javaType="java.lang.String" mode="IN"/>
> <parameter property="returnCode" jdbcType="NUMERIC"
> nullValue="0"
> javaType="java.lang.Integer" mode="OUT"/>
> </parameterMap>
>
>
> <procedure id="insertCreditScore2"
> parameterMap="clientScoreParams" >
> CALL OUCSS01.CRE_COTE_CR2(?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
> </procedure>
>
> ===================================================
> Thats the trace of call of the stored procedure:
> DEBUG [16 oct. 05 19:19:30.519][PreparedStatement]
> {pstm-100009} PreparedStatement: CALL
> OUCSS01.CRE_COTE_CR2(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
> DEBUG [16 oct. 05 19:19: 30.519][PreparedStatement]
> {pstm-100009} Parameters: [cc, null, null, 36, P,
> Benfeito, Carlos, null, 10/14/1976, Brossard,
> J4Y3J1AAAA, CAN, QC, 7570 Lautrec, null, null, 5,
> null, 781, A, Carlos Benfeito, 10/16/2005, H,
> <xml>aaaaaaaa</xml>, CSS]
> DEBUG [16 oct. 05 19:19:30.519][PreparedStatement]
> {pstm-100009} Types: [java.lang.String, null, null,
> java.lang.Integer, java.lang.String, java.lang.String,
> java.lang.String , null, java.lang.String,
> java.lang.String, java.lang.String, java.lang.String,
> java.lang.String, java.lang.String, null, null,
> java.lang.Integer, null, java.lang.Integer,
> java.lang.String, java.lang.String, java.lang.String ,
> java.lang.String, java.lang.String, java.lang.String]
>
>
> ===================================================
> This is the stack trace:
> com.ibatis.common.jdbc.exception.NestedSQLException:
>
> --- The error occurred in
> iBatis-sqlMap-CreditScoreCache.xml.
> --- The error occurred while applying a parameter map.
>
> --- Check the CreditScoreCache.clientScoreParams.
> --- Check the statement (update procedure failed).
> --- Cause: java.lang.NullPointerException
> Caused by: java.lang.NullPointerException
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:89)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update
> (SqlMapExecutorDelegate.java:312)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:74)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java
> :49)
>
> ======================================================
> it seems to have problems to set parameters in runtime
> execution.
> I verified all the parameters, I have some null values
> but I worked before with iBatis using null values as
> parameters with JTOpen driver (DB2 400) without
> problems.
> Any suggestions to resolve this problem?
>
> Alberto
>
>
>
>
>
> Alberto Del Castillo
> ------------------------------------------------------------------------
> "Ce message est confidentiel, a l'usage exclusif du destinataire
> ci-dessus et son contenu ne represente en aucun cas un engagement de
> la part de AXA, sauf en cas de stipulation expresse et par ecrit de la
> part de AXA. Toute publication, utilisation ou diffusion, meme
> partielle, doit etre autorisee prealablement. Si vous n'etes pas
> destinataire de ce message, merci d'en avertir immediatement
> l'expediteur."
>
> "This e-mail message is confidential, for the exclusive use of the
> addressee and its contents shall not constitute a commitment by AXA,
> except as otherwise specifically provided in writing by AXA. Any
> unauthorized disclosure, use or dissemination, either whole or
> partial, is prohibited. If you are not the intended recipient of the
> message, please notify the sender immediately."
--
Vincent NICOLAS
Teamlog, Lyon