-
Problem with invalid timestamp
Hi I need to load the data from CSV file to oracle tablesusing sql loader. A coulmn CRE_TIME in the CSV file has the possibilty of having either a null value or 00:00:00 or a valid timestamp. But the column CRE_TIME is defined with data type INTEGER EXTERNAL.So i need to replace null or 00:00:00 with a value 0 in the oracle table.
my control file looks like
CRE_TIME "Decode(:CRE_TIME,null,replace(TO_DATE('00:00:00','HH24:MI:SS'),':',''),'00:00:00',replace(TO_DATE('0 0:00:00','HH24:MI:SS'),':',''),replace(TO_DATE(:CRE_TIME,'HH24:MI:SS'),':',''))",
But still it is not working.(Error:ORA-01722: invalid number) Please advice on what could be done.
Please note that the datatype for coulmn is INTEGER EXTERNAL
-
Try this...
Code:
to_number(nvl((to_char(to_date(CRE_TIME,'hh24:mi:ss'),'hh24miss')),0))
Here are the three cases you mentioned...
Code:
SQL>
SQL> select to_number(nvl((to_char(to_date('00:00:00','hh24:mi:ss'),'hh24miss')),0))
2 from dual
3 ;
TO_NUMBER(NVL((TO_CHAR(TO_DATE('00:00:00','HH24:MI:SS'),'HH24MISS')),0))
------------------------------------------------------------------------
0
SQL> select to_number(nvl((to_char(to_date(NULL,'hh24:mi:ss'),'hh24miss')),0))
2 from dual
3 ;
TO_NUMBER(NVL((TO_CHAR(TO_DATE(NULL,'HH24:MI:SS'),'HH24MISS')),0))
------------------------------------------------------------------
0
SQL> select to_number(nvl((to_char(to_date('12:30:59','hh24:mi:ss'),'hh24miss')),0))
2 from dual
3 ;
TO_NUMBER(NVL((TO_CHAR(TO_DATE('12:30:59','HH24:MI:SS'),'HH24MISS')),0))
------------------------------------------------------------------------
123059
SQL>
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|