How to load and validate timestamp data in multiple formats?

Viewed 57

I am populating table data from a file using the copy command. The table includes timestamp data in multiple formats. I have set alter session set TIMESTAMP_INPUT_FORMAT = 'dd-mon-yyyy hh24.mi.ss.ff6'; which handles the formatting of certain values thus formatted, but there are other timestamp values in the source file that are formatted differently. To cope with this I am doing e.g.

copy into <table> (
     <timestamp_column_1>,
     <timestamp_column_2>
...
) from (
SELECT
    $1,
    TO_TIMESTAMP_TZ(t.$2, 'DD-MON-YY') 

This works, but the validate command does not support transformations, so my current validation method is unrealiable.

Is there a way I can achieve what I want in my load without using transformations?

0 Answers
Related