Orchestrate Schema with RCP and Timestamp

eph · Post by **eph** » Tue Aug 02, 2011 8:09 am

Hi all,

I'm facing some troubles trying to create a full generic job. The general idea is that I have to load on different oracle tables data coming from different files (and thus, having different schema).
There I'm struggling with some issue using an orchestrate schema with a column import

Code: Select all

record  {record_delim='\n', charset='UTF8', final_delim=end, delim_string='|', quote=none, null_field=''}
( E1_FILE_NAME: ustring[max=60];
  E1_PARENT_GUID: ustring[max=36];
  E1_TRANSACTION_ID: ustring[max=60];
  E1_TRANSACTION_LN: ustring[max=20];
  EIP_CTL_ID: ustring[max=25];
  SETID: ustring[max=5];
  BUSINESS_UNIT: ustring[max=5];
  EIP_DTA_STATUS: ustring[max=1];
  EIP_PROGRAM: ustring[max=10];
  EIP_SOURCE: ustring[max=1];
  EIP_REFERENCE: ustring[max=30];
  ORIG_DT_TIMESTAMP: nullable timestamp[microseconds];
  LASTUPDDTTM: nullable timestamp[microseconds];
  OPRID: ustring[max=30];
  PUBNODE: ustring[max=30];
  CHNLNAME: ustring[max=30];
  PUBID: decimal[38,0];
  MSGNAME: ustring[max=30];
  SUBNAME: ustring[max=30];
  AUDIT_ACTN: ustring[max=1];
)

There are no option in the column input stage, since i'm using RCP with an OSH.

I got this error :

Code: Select all

Input buffer overrun at field "ORIG_DT_TIMESTAMP", at offset: 302

which I can't understand, since my file looks like this :

Code: Select all

Join_articles_CLN1.txt|20100913184558406|SYD3631-20110201171310140-2011-02-01 17:13:37-0000000003|0000000100|0000000000000000000000004|GROUP| | | | | | | | | | |0| | | 
Join_articles_CLN1.txt|20100913184558406|SYD3631-20110201171310140-2011-02-01 17:13:37-0000000001|0000000100|0000000000000000000000002|GROUP| | | | | |2010-12-10 17:13:37.0000000001| | | | |0| | |

The first line got 168 characters and the second one, 197 char.

I've already gone through "nullable timestamp" and "Input buffer overrun" search on the forum, but I didn't found anything (except this and this).

Is it possible to get rid of this madness of nullable timestamp with OSH??? How could I get a working generic job i.e. without importing columns as varchar+type conversion?

Thanks in advance for any help

Eric

chulett · Post by **chulett** » Tue Aug 02, 2011 8:25 am

"2010-12-10 17:13:37.0000000001" seems to long to be a valid timestamp to me. What is the precision of your target TIMESTAMP column? Max is 9 and you've got 10 which is (I assume) why you got the 'overrun' error.

eph · Post by **eph** » Tue Aug 02, 2011 9:39 am

Hi Craig,

This value is indeed too long for a timestamp, that's why it's truncated by the oracle stage when I use the 'classical' workflow (column import with varchar+transformer for type conversion=>oracle load).

This problem is also present for the first line, where the value is ' ', which I assume should be considered as null as my null_field is ' '.

Eric

eph · Post by **eph** » Mon Aug 08, 2011 6:09 am

Is there any way to handle null values for timestamp? I'm using a column import in which null value is given as a ' ' (space). In the importing field are mixed varchar/decimal/timestamp columns that could be nullable.

I'm getting this warning :

Code: Select all

When validating import schema: At field "DATE": "null_field" length (1) must match field's fixed width (10)

chulett · Post by **chulett** » Mon Aug 08, 2011 7:30 am

The warning is telling you that your null field value must match the field's fixed width. What does that suggest to you?

eph · Post by **eph** » Tue Aug 09, 2011 6:02 am

Hi Craig,

I've already tried a null value of 10 characters long, but as other types (varchar) could be null, in that case the null value should be

Code: Select all

' '

and not

Code: Select all

'          '

Maybe I'm missing some trick to do it, but nothing seems obvious to me in that case.

Eric

chulett · Post by **chulett** » Tue Aug 09, 2011 7:43 am

You can set the 'null string value' for each field individually, however that option is probably gone when using RCP. Hopefully, others who've been down this road can offer advice.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 09, 2011 3:35 pm

You can set the 'null field value' property for each field individually in the schema file.

chulett · Post by **chulett** » Tue Aug 09, 2011 4:57 pm

Ah. :D

eph · Post by **eph** » Mon Sep 05, 2011 3:02 am

As it is actually impossible to use a ' ' (space) as null value for a datetime (which is longer), I've decided to put the type conversion later in the process chain, in a SQL query (that populate a table with datetime coming from a table where the data is a varchar).

It's a pity though, since I was trying to get a fully generic job.

Eric