apply interval day to second function to character data

I want to load data from a csv file whose one filed has data like this '02:54:27' (hh:mi:ss). This field records the activity time of an agent. Googling did not help me in finding answers to my questions.

I have 2 questions:
1. Is there a way I can convert such character data directly to interval day to second data type while loading using SQL loader?
2. If the answer to 1 is No, then I will load data directly in some temporary table. Is there a function in oracle that would help in converting char data to interval day to second type within oracle so that I can load it in a different table?

Did you try this in your control file?:

Code:

---

LOAD DATA
... etc ...
my_interval "TO_DSINTERVAL(:my_interval)",
... etc ...

---

;)

Quote:

---

Originally Posted by LKBrwn_DBA

Did you try this in your control file?:

Code:

---

LOAD DATA
... etc ...
my_interval "TO_DSINTERVAL(:my_interval)",
... etc ...

---

;)

---

Thanks. Actually I could find that after searching for 2-3 hours, I almost forgot that such a function exists.

Quote:

---

Originally Posted by LKBrwn_DBA

Did you try this in your control file?:

Code:

---

LOAD DATA
... etc ...
my_interval "TO_DSINTERVAL(:my_interval)",
... etc ...

---

;)

---

Is there a way in which to_dsinterval handles the following situation:

SQL> select to_dsinterval(concat('0 ','44:34:54')) from dual;
select to_dsinterval(concat('0 ','44:34:54')) from dual
*
ERROR at line 1:
ORA-01850: hour must be between 0 and 23

Obviously, I won't do that in a sql statement. Actually, the data from csv file comes like this: 44:20:13, 34:14:31. I have avoided converting this data to type interval day to second while loading. So, in oracle table, this data is stored as char type.

After I load data, I need to present this data using select statement as sum of two fields. So, the data in column1(44:20:13) and column2(34:14:31) will be presented in one column as:

select column1+column2 from ;

The output should be +3 06:34:44 or 78:34:44(preferred).