[Bill]

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

[support-joy]

Bill,

I believe you have cross-posted on another forum – http://forums.oracle.com/forums/thread.jspa?threadID=2170240 and the issue was related to crude date string being returned. You seem to have fixed it with converting to_date.

Best.

[Author]

Posts