NLS SETTINGS?

[karry450]

Hi there,
we are using an insert statement like the following :
INSERT INTO XYZ_c
(abc_date, 123_date, abc_abc_12,
cde_cde_cde, abe_code, db_channel,
efg_hij_date, jjg_date, abc_abc_code,
opq_amount, abc_opq_amount,
wxy_ixz_seq, jhk_id, klm_id, le_ste_id,
hg_sic_von, pct_code_type, db_ue_tp
)
VALUES (TO_DATE (:1, 'DD-MON-YY'), TO_DATE (:2, 'DD-MON-YY'), :3,
:4, :5, :6,
TO_DATE (:7, 'DD-MON-YY'), TO_DATE (:8, 'DD-MON-YY'), :9,
:10, :11,
:12, :13, :14, :15,
:16, :17, TO_DATE (:18, 'YYYY-MM-DD HH24:MI:SS')
)

since it's unlikely to cause a problem imminently, but generally speaking it's better to always use 4-digit years, not 2 as is used here.
Oracle has to make an assumption about the century unless you tell it explicitly what it is, and whilst it might work now, there's no guarantee that future versions of Oracle will make the same assumption. You should also avoid using character month abbreviations - (ie MON) - use numeric months instead (MM). This is because the charachter abbreviations are determined by the client NLS settings (ie how the DataStage job connects to db), and different languages use different abbreviations, but numeric representation of months is pretty standardised.

can anyone suggest how can i implement the above thing.


thanks

[ray.wurlod]

Easy. Get the NLS settings right. In DataStage, this means the TIME category of the locale.

[karry450]

Can anyone help with some more.... brief awnser please

[ray.wurlod]

Let's say you're processing dates in a Transformer stage. Use the following commands in ExecTCL as before-stage and after-stage subroutines.

Before-stage: SET.LOCALE TIME FR-FRENCH
After-stage: RESTORE.LOCALE

This will mean that, irrespective of what the rest of the job is doing, the Transformer stage will operate in French as far as dates and times are concerned; it will correctly recognize French day names and month names and their abbreviations.

[NickH]

You could use the oracle date format RR instead of YY
as in TO_DATE(date,'DD-MM-RR')

I believe it was set up to cope with the transiton between centuries.

[chulett]

What 'above thing' are you actually asking about? I don't see anything like a question or specific issue that you are having.