Spring-iBatis-Oracle data access
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:
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:
--- 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
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
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
selecting only 1 column or 2 and just about every permutation possible as well as usuing column names instead of columnIndexes, etc.. ad infititum):
So here´s the bean variable definitions (with all the setters and getters left out for brevity):
<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"/>
<select id="selectDeriDivsList" resultMap="selectDeriDivsListResult">
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
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;
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,