That sounds reasonable. I've done some more digging, and found that the database instance in this case is an _OLD_ version of Oracle: 9.2.0.8.0. I also tried using the OCI driver (version 12), which refuses to even talk to this database.
I have three other databases running on more recent versions of Oracle, and all three have worked fine with DataImportHandler. On Thu, Sep 12, 2013 at 9:48 AM, Shalin Shekhar Mangar < shalinman...@gmail.com> wrote: > This is probably a bug with Oracle thin JDBC driver. Google found a > similar issue: > > http://stackoverflow.com/questions/4168494/resultset-getstring-on-varchar2-column-returns-empty-string > > I don't think this is specific to DataImportHandler. > > > On Thu, Sep 12, 2013 at 12:43 PM, Raymond Wiker <rwi...@gmail.com> wrote: > > Followup: I just tried modifying the select with > > > > select CAST('APPLICATION' as varchar2(100)) as sourceid, ... > > > > and that caused the sourceid field to be empty. CASTing to char(100) gave > > me the expected value ('APPLICATION', right-padded to 100 characters). > > > > Meanwhile, google gave me this: > http://bugs.caucho.com/view.php?id=4224(via > > http://forum.caucho.com/showthread.php?t=27574). > > > > > > On Thu, Sep 12, 2013 at 8:25 AM, Raymond Wiker <rwi...@gmail.com> wrote: > > > >> I'm trying to index a view in an Oracle database, and have come across > >> some strange behaviour: all the VARCHAR2 fields are being returned as > empty > >> strings; this also applies to a datetime field converted to a string via > >> TO_CHAR, and the url field built by concatenating two constant strings > and > >> a numeric filed converted via TO_CHAR. > >> > >> If I cast the fields columns to CHAR(N), I get values back, but this is > >> not an acceptable workaround (the maximum length of CHAR(N) is less than > >> VARCHAR2(N), and the result is padded to the specified length). > >> > >> Note that this query works as it should in sqldeveloper, and also in > some > >> code that uses the .NET sqlclient api. > >> > >> The query I'm using is > >> > >> select 'APPLICATION' as sourceid, > >> 'http://app.company.com' || '/app/report.aspx?trsid=' || > >> to_char(incident_no) as "URL", > >> incident_no, trans_date, location, > >> responsible_unit, process_eng, product_eng, > >> case_title, case_description, > >> index_lob, > >> investigated, investigated_eng, > >> to_char(modified_date, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') as modified_date > >> from synx.dw_fast > >> where (investigated <> 3) > >> > >> while the view is > >> INCIDENT_NO NUMBER(38) > >> TRANS_DATE VARCHAR2(8) > >> LOCATION VARCHAR2(4000) > >> RESPONSIBLE_UNIT VARCHAR2(4000) > >> PROCESS_ENG VARCHAR2(4000) > >> PROCESS_NO VARCHAR2(4000) > >> PRODUCT_ENG VARCHAR2(4000) > >> PRODUCT_NO VARCHAR2(4000) > >> CASE_TITLE VARCHAR2(4000) > >> CASE_DESCRIPTION VARCHAR2(4000) > >> INDEX_LOB CLOB > >> INVESTIGATED NUMBER(38) > >> INVESTIGATED_ENG VARCHAR2(254) > >> INVESTIGATED_NO VARCHAR2(254) > >> MODIFIED_DATE DATE > >> > >> > >> > > > > -- > Regards, > Shalin Shekhar Mangar. >