Spring-iBatis-Oracle data access

[seps@wanadoo.es]

Hi all,

I've just spent hours on a problem which appears unsolvable. Hope someone can clarify. I have constructed a view in my oracle database which uses data from other oracle databases.

In SQLDeveloper the queries work fine. Now, I programmed my app with all the maps, implementations, interfaces, beans and everthing else that's required and all apears fine until I

do a simple query to select all of the rows from the view. I always receive the same error despite many different attempts at defining all these components. Here´s the exact error:

--- The error occurred in xx/xxxx/xxx/xxxxxx/sqlmaps/GaeVwcmDeri.xml.  
--- The error occurred while applying a parameter map.  
--- Check the GaeVwcmDeri.selectDeriDivsList-InlineParameterMap.  
--- Check the statement (query failed).  
--- Cause: java.sql.SQLDataException: ORA-01861: el literal no coincide con la cadena de formato

Now remember, I'm using Spring and iBatis for db access to oracle so I'll start with the view definition's underlying data types:

COLUMN-NAME	DATA-TYPE	NULLABLE
DERI_CODADIFD	NUMBER		No
DERI_FECHAINI	DATE		No
DERI_FECHAFIN	DATE		Yes
DERI_PCT	NUMBER		No
DERI_CODADIFP	NUMBER		Yes
DERI_DESIG	VARCHAR2(40)	No
DERI_CODCP	VARCHAR2(5)	Yes
CCON_DESIG	CHAR(40)	Yes
DIV_CODIGO	CHAR(4)		Yes
DIV_DESIG	CHAR(30)	Yes
EMP_CODIGO	NUMBER(10,0)	No
EMP_DESIG	VARCHAR2(63)	No

So here´s the sql-map definition (which i've tried with an explicit resultMap which you see here and I've also tried with with the implicit mapping to the bean and I´ve also tried

selecting only 1 column or 2 and just about every permutation possible as well as usuing column names instead of columnIndexes, etc.. ad infititum):

<sqlMap namespace="GaeVwcmDeri">
	<resultMap id="selectDeriDivsListResult" class="es.adif.gae.common.sql.beans.GaeVwcmDeri">
		<result property="deriCodadifd" columnIndex="1"/>
		<result property="deriFechaini" columnIndex="2" javaType="java.sql.Date" jdbcType="DATE" nullValue="0"/>
		<result property="deriFechafin" columnIndex="3" javaType="java.sql.Date" jdbcType="DATE" nullValue="0"/>
		<result property="deriPct" columnIndex="4"/>
		<result property="deriCodadifp" columnIndex="5"/>
		<result property="deriDesig" columnIndex="6" javaType="java.lang.String" jdbcType="VARCHAR" nullValue="NULL"/>
		<result property="deriCodcp" columnIndex="7" javaType="java.lang.String" jdbcType="VARCHAR" nullValue="NULL"/>
		<result property="cconDesig" columnIndex="8" javaType="java.lang.String" jdbcType="CHAR" nullValue="NULL"/>
		<result property="divCodigo" columnIndex="9" javaType="java.lang.String" jdbcType="CHAR" nullValue="NULL"/>
		<result property="divDesig" columnIndex="10" javaType="java.lang.String" jdbcType="CHAR" nullValue="NULL"/>
		<result property="empCodigo" columnIndex="11"/>
		<result property="empDesig" columnIndex="12" javaType="java.lang.String" jdbcType="VARCHAR" nullValue="NULL"/>
	</resultMap>
 
	<select id="selectDeriDivsList" resultMap="selectDeriDivsListResult">
		SELECT 
			DERI_CODADIFD,
			DERI_FECHAINI,
			DERI_FECHAFIN,
			DERI_PCT,
			DERI_CODADIFP,
			DERI_DESIG,
			DERI_CODCP,
			CCON_DESIG,
			DIV_CODIGO,
			DIV_DESIG,
			EMP_CODIGO,
			EMP_DESIG
		FROM GAE_VWCM_DERI
	</select>
</sqlMap>

So here´s the bean variable definitions (with all the setters and getters left out for brevity):

	private long deriCodadifd;
	private Date deriFechaini;
	private Date deriFechafin;
	private float deriPct;
	private long deriCodadifp;
	private String deriDesig;
	private String deriCodcp;
	private String cconDesig;
	private String divCodigo;
	private String divDesig;
	private long empCodigo;
	private String empDesig;

So where is the problem? I just don't see it and I think I've tried every possible way to get the data I want. The problem has nothing to do with, GRANTs, SYNONYMs or anything else

strictly oracle related; as I've said, the query works fine in SQLDeveloper (I'll probably try it also in straight JDBC ANd ODBC to see if I can get anymore info). I think it's

clear that the problem is somewhere in the iBatis interface to oracle but I'll be damned if I can find where but I have a feeling that it may have something to do with the NULLABLE

columns which I abhor but can´t do anything about in this case as the underlying data comes from an external DB. Maybe I'll also try a stored procedure but I can't see how the

results would be any different.

If anyone could provide a reasonable suggestion, I'll be the happiest man on earth.

HELP! I'm desperate.

Thanks in advance for any reply,
Bill