Check Part II SQL*Loader I can't remember when I last used it (being server side I use External Tables), but it should turn out something like
INFILE 'D:\ss\TT-Projects\Customer Apps\Client Dump\3GBundle.csv'
BADFILE 'D:\dd\TT-Projects\Customer Apps\Client Dump\3GBundle.bad'
DISCARDFILE 'D:\dd\TT-Projects\Customer Apps\Client Dump\3GBundle.dsc'
INSERT INTO TABLE tmp_mtnuga_3g_expiry_mig
fields terminated by ',' optionally enclosed by '"'
EXPIRY_DATE_T char "to_timestamp_tz(:EXPIRY_DATE_T,'yyyy-mm-dd hh24:mi:sstzh:tzm')",
STATUS_DATE_T char "to_timestamp_tz(:STATUS_DATE_T,'yyyy-mm-dd hh24:mi:sstzh:tzm')",
you must adjust the mask to match your data
Getting the below error message
EXPIRY_DATE_T Time zone related format elements not allowed in TIMESTAMP formatting
I have change the table data size as well like below
EXPIRY_DATE_T TIMESTAMP (9) WITH LOCAL TIME ZONE,
STATUS_DATE_T TIMESTAMP (9) WITH LOCAL TIME ZONE,
Sorry, I cannot test it as I have just Apex Workspace available from home and you haven't posted some data sample of yours.
You have defined your columns as timestamp with time zone and according to documentation SQL*Loader is able to manage that data type.
If you changed from TIMESTAMP with TIME ZONE (which needs an explicit time zone) to TIMESTAMP WITH LOCAL TIME ZONE (which implies a time zone) then you should not have time zone pieces in your format mask.
You said your data is like "2013-08-31 17:14:56"
EXPIRY_DATE_T char "to_timestamp(:EXPIRY_DATE_T,'yyyy-mm-dd hh24:mi:ss')",
STATUS_DATE_T char "to_timestamp(:STATUS_DATE_T,'yyyy-mm-dd hh24:mi:ss')",
If you get an error again, post your current table definition, current loader file, and a few lines of data.
I am using 2 option to import into a table
Then perhaps you should use option #3 and download Oracle's free sql developer. It can import that data just fine.
The problem in TOAD will also have been time formats - you need to tell it the format to expect, and having the TIMESTAMP WITH TIME ZONE datatype will not have helped. SQLDeveloper also has its own storage of the formats to use.
At the database session level, and for SQLPlus, for TIMESTAMP WITH LOCAL TIME ZONE you'd need
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';
In TOAD this will be either a TIMESTAMP format or a TIMESTAMP WITH LOCAL TIME ZONE format.
I listed the options for SQLDeveloper and SQLPlus in https://forums.oracle.com/message/11177827#11177827
OP is the one you need to be replying to since they are the one with the problem. I really don' t care what Toads problems are.