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
