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
>
>
>
