Printable View
In my staging table i have column as Effect_Date as Date its data type.
I have one Flat file separated by '|' .And one of the column contains Date in the format '21-JAN-08 12:30:54'.When ever I m runninng CTL file to load data into Staging Table..it doesnt do so..Kindly suggest me should I need to declare some function in CTL For date or sumthing else.
Himanshu
I assume you are talking about sqlloader.
Please copy/paste your code, control file and log.
Check this url and search for date mask .
http://download.oracle.com/docs/cd/B...t.htm#i1008914
Regards
Raj
here is my code where i face problem
LOAD DATA
INFILE *
APPEND INTO TABLE SIEBEL.STAGING_CONTRACT
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
ASSET_NUM ,
PRODUCT_NAME ,
STATUS ,
BILL_TO ,
SOLD_TO ,
FULFILL_TO ,
ORIGINAL_ORDER ,
MODIFY_ORDER ,
CANCEL_ORDER ,
EFFECT_DATE ,
EXPIRE_DATE ,
NET_PRICE
)
BEGINDATA
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
so this 'EFFECT_DATE AND EXPIRE_DA* BOTH are not loading values to my Staging Table...
Bcoz of this SQLLoadr is not working...
My table Representation is :-
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 SYSTIMESTAMP),
EXPIRE_DATE SYSTIMESTAMP,
NET_PRICE VARCHAR2(22)
)
Lets be sure your issue is related to the systimestamp columns.
1- Please try to load your data taking out your systimestamp values, just load nothing (nulls) in there.
2- If it works then we know for sure the problem is systimestamp related and consequently I would say formatting related. In this case please start by doing a "select systimestamp from dual;" and check what's the expected systimestamp format.
In my case I can see "03/10/2008 2:34:15.881 PM"
I did dat query ...'select systimest....fr..dual'
In my case I m getting:-
11-MAR-08 03.24.52.312664000 AM -07:00
I tried the step given by u.
I emptied the column effect_date and the other date col. .So both the columns were null.
Then I executed the CTL It sucessfully loaded the specific record into the
staging table.
Now plz tel me wat next shud I follow?
-Himanshu
Good, now you are certain the problem is caused by these offending dates.
Would you mind in loading a single row with effect_date = '11-MAR-08 03.24.52.312664000 AM -07:00'... meaning, the format your database appears to be expecting?
Pav...
Could u tell me wat data type I shud use with this specific column?
systimestamp?
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.
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.
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
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>
----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
Look at your data, your data doesn't match your to_timestamp() format.
Look at the example on previous posting.
Fix your syntax.
Could you please guide me where excatly my data doesnt match
If u r talking abt the TO_timestamp functon ..that i used(start_date,dd-mon-yy hh24:mi:ss:fff') tooo..if its not please let me know..
i know I might be doin some sily mistake....
I 'll folow ur statement howver my database is facing some issue..so as soon it gets recitfy i 'll let u know...
Himanshu
Not trying to be rude, if you can't follow this instructions...
... I'll be tempted to give up on you.Quote:
Originally Posted by PAVB
Hey ,,If u dont mind Can I knw ur Name?
I appologize for not looking at ur example carefully which agonised u.
Howevr I followed and it worked sucessfully.Here is the code that I used
UPDATE STAGING_CONTRACT
SET EFFECT_DATE = TO_TIMESTAMP(SUBSTR(START_DATE,1,18),'DD-MON-YY HH24:MI:SS')
,EXPIRE_DATE = TO_TIMESTAMP(SUBSTR(END_DATE,1,18),'DD-MON-YY HH24:MI:SS')
I m really Thankful to you.This litle stuff was like a pain in my neck....but now its a useful learning too for me.....
Himanshu
I'm glad it worked out.
--Can I Knw your name so that I cane remebr always....
One more thing have you ever worked on DBMS_Scheduler?
Himanshu
Around here most people calls me "Ivan le russe fou" which is a cute wording for "Ivan the crazy russian".
Yes.
Ivan I m new in PL/SQL..but still able to get workaround luckily..jus because of such knowledge filled Forums.
Now the problem is we've been asked to use dbms_scheduler whose task will be to run a shel script which will call other sqls for processing...
The workng of scheduler is to call Linux M/C remotely..so if u have any basic knowledge how to do it?Please let me know
-Himanshu
You have to open a new thread
I 'll do dat