Resolution:
Using Oracle functions e.g. TO_DATE in the mapping for the Input Map that uses SQL Datasource throws the following exception: Invalid input value: TO_DATE(@DS1.attr1,'DD/MM/YYYY').  Is there any workaround for this?

Environment:

TIBCO Product name and version: TIBCO Collaborative Information Manager 7.x
Operating System(s): All Operating Systems

Symptoms:

An exception similar to Invalid input value: TO_DATE(@DS1.attr1,'DD/MM/YYYY') is thrown in CIM UI.

Cause:

It's a known issue with SQL Datasource.

Resolution:

You can try one of the following workarounds:

1. Use a regular datasource to replace the SQL datasource.

2. Create a copy of columns for which you want to use Oracle functions with "C" prefixed to them, in the table used by this SQL datasource, and also add the same data in these columns as the original ones.  After that, you should be able to save the input map. For example: let's suppose you have a table called TEST with columns ID and NAME and you created a SQL datasource based on this Table.  Now if you want to use Oracle function e.g. SUBSTR with NAME column, you need to create another column CNAME with exactly the same data as the NAME column.  This should allow you to save the input and initiate the import.  You will have to do this step for all the columns for which you want to use Oracle functions.

Attachments:

None

References:

None

Using Oracle functions e.g. TO_DATE in the mapping for the Input Map that uses SQL Datasource throws the following exception: Invalid input value: TO_DATE(@DS1.attr1,'DD/MM/YYYY').Is there any workaround for this?