-
sqllader
Sir
I already tried this data type..however oracle doesnt recgnise it....
-
IF I USE COLUMN WITH DATATYPE SYSTIMESTAMP...IT DOESNT WORK....
THEN WHAT i DID..
I DECLARED THE DATA TYPE OF COLUMN EFFECT_DATE AS VARCHAR2(100)
AND THEN WHILE THERE WERE NO ROWS IMPORTED ..i FIRST OF ALL
UPDATED THE TABLE SETTING THIS COLUMN TO "TO_SYSTIMESTAMP"..
IT SEEMED IT WORTKED AND THEN DATA LOADED SUCESSFULLY...BUT AFTER THIS i M RUNNING PROCEDURE TO LOAD ALL RECORDS IN2 ANOTHR TABLE THERE ITS NOT WORKING..ITS SAYING
"ORA-01830 -date format picture ends before converting entire input string"
-
Would you consider something quick and dirty to get rid of the issue?
Years ago we bumped into some data conversion issue involving sqlloader and this is what we did to get rid of the issue in a quick and dirty way.
Imagine your issue is your start_date column which at the end of the day has to be a timestamp data type one.
1- Define two columns in your table
start_date timestamp null,
start_date_x varchar2(100) null
2- Load your data into start_date_x and nulls into start_date
3- After your sqlloader process loads your table -putting your troublemaker date into start_date_x and leaving start_date as Null run a mass update -no where clause on the table setting start_date = to_timestamp(start_date_x)
Hope this helps.
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.
-
Deppressd
Hey first of all...
i m highly fortunate that you continoulsy helpin me on this issue.
Now I hav tried all the options evn the last one that u provided.
its givin me new error
ORA-01849: hour must be between 1 and 12
so plz help me out I m struck with this bloddy stuff.....
-
not sure when are you getting ORA-01849, is it during update statement after loading data thru sqlloader?
If yes... please post your update statement alongside with a five rows sample of the content of your table.
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.
-
loader
Here is my new table with new columns:-
CREATE TABLE SIEBEL.STAGING_CONTRACT
(ASSET_NUM VARCHAR2(100),
PRODUCT_NAME VARCHAR2(100),
STATUS VARCHAR2(30),
BILL_TO VARCHAR2(50),
SOLD_TO VARCHAR2(50),
FULFILL_TO VARCHAR2(50),
ORIGINAL_ORDER VARCHAR2(30),
MODIFY_ORDER VARCHAR2(30),
CANCEL_ORDER VARCHAR2(30),
EFFECT_DATE TIMESTAMP NULL ,
START_DATE VARCHAR2(100),------added new column
END_DATE VARCHAR2(100), -------added new column
EXPIRE_DATE TIMESTAMP NULL ,
NET_PRICE VARCHAR2(22)
)
1.sucesflly loaded data thru CTL
3. update staging_contract
set EFFECT_DATE =to_timestamp(START_DATE)
THEN IT GAVE ME THAT ERROR
-
..and WHERE ARE THE FIVE ROWS I've asked you to show us? We have to see how your start_date and end_date looks like.
You are missing the format in your to_timestamp() syntax, you cannot just specify the source with no format and pray for it to work.
1- Look at the data.
2- Look at the syntax... http://www.itk.ilstu.edu/docs/oracle...nctions178.htm
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.
-
Here are 5 records.They may appear haphazardly coz I jus took export frm SQLDevloper...
"ASSET_NUM" "PRODUCT_NAME" "STATUS" "BILL_TO" "SOLD_TO" "FULFILL_TO" "ORIGINAL_ORDER" "MODIFY_ORDER" "CANCEL_ORDER" "EFFECT_DATE" "START_DATE" "END_DATE" "EXPIRE_DATE" "NET_PRICE"
"R-1" "Agent Bronze XL" "Inactive" "1000" "1000" "1000" "R-1" "" "" "" "01-Jul-96 18:25:00:000" "01-Aug-98 18:25:00:000" "" "99.00"
"R-2" "Agent Bronze XL" "Expired" "1001" "1001" "1001" "R-2" "" "" "" "01-Jul-96 18:25:00:000" "01-Jul-97 18:25:00:000" "" "99.00"
"R-3" "Agent Bronze XL" "Expired" "1002" "1002" "1002" "R-3" "" "" "" "01-Jul-96 18:25:00:000" "22-May-01 00:00:00:000" "" "99.00"
"R-4" "Agent Bronze XL" "Inactive" "1003" "1003" "1003" "R-4" "" "" "" "01-Jul-96 18:25:00:000" "23-Jan-97 17:03:00:000" "" "99.00"
"R-5" "Agent Bronze XL" "Inactive" "1004" "1004" "1004" "R-5" "" "" "" "01-Jul-96 18:25:00:000" "23-Jan-97 17:02:00:000" "" "99.00"
-
How about working in your to_timestamp() syntax?
I'm not going to do it for you, follows and example of your type-and-pray syntax and also an example of a working syntax both using your actual data.
Code:
SQL>
SQL> select to_timestamp('22-May-01 00:00:00:000')
2 from dual
3 ;
select to_timestamp('22-May-01 00:00:00:000')
*
ERROR at line 1:
ORA-01849: hour must be between 1 and 12
SQL>
SQL>
SQL>
SQL> select to_timestamp(substr('22-May-01 00:00:00:000',1,18),'dd-mon-yy hh24:mi:ss')
2 from dual
3 ;
TO_TIMESTAMP(SUBSTR('22-MAY-0100:00:00:000',1,18),'DD-MON-YYHH24:MI:SS')
---------------------------------------------------------------------------
22-MAY-01 12.00.00 AM
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.
-
sqlloader
----I am sorry i forgot to tell u while updating table i used that syntax too
to_timestamp(new_date,'dd-mon-yy hh24:mi:ss.ff')..after this it gave me that specific error
Himanshu
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
|