Tuesday, May 14, 2013

How to insert Date datatype in Oracle database using bash script


Oracle database supports the representation of date like 2013/5/22 or 2013/feb/3 etc with datatype DATE datatype in Oracle. however we
can have the misconception that inorder to insert the data in the database the database must be in specific format like 2013/feb/4 but the truth
is the data can  be in format like year/month/day or day/month/year seperated by either / or -.

and the most important thing is


  1. if the data is like 2013/05/22 then be sure to represent as yyyy/mm/dd



OPTIONS (skip=1, errors=100, direct=True)
LOAD DATA
INFILE 'CustomerDataVs.csv'
TRUNCATE into table CUSTOMER_STG
FIELDS TERMINATED BY ',' optionally enclosed by '"'
(
CUST_FIRST_NAME,
CUST_LAST_NAME,
CUST_RELIGION_IDNT,
CUST_EDU_QUALI,
CUST_INCOME_IDNT,
CUST_IDNT,
ADDR_ZNE,
ADDR_DISTT,
ADDR_MUN_VDC,
CUST_MIDDLE_NAME,
CUST_DOB date 'yyyy/mm/dd',
MARITAL_STATUS,
CUST_CONTACT

)


2.           if the data is like 2013-05-22 then be sure to represent as yyyy-mm-dd


OPTIONS (skip=1, errors=100, direct=True)
LOAD DATA
INFILE 'CustomerDataVs.csv'
TRUNCATE into table CUSTOMER_STG
FIELDS TERMINATED BY ',' optionally enclosed by '"'
(
CUST_FIRST_NAME,
CUST_LAST_NAME,
CUST_RELIGION_IDNT,
CUST_EDU_QUALI,
CUST_INCOME_IDNT,
CUST_IDNT,
ADDR_ZNE,
ADDR_DISTT,
ADDR_MUN_VDC,
CUST_MIDDLE_NAME,
CUST_DOB date 'yyyy-mm-dd',
MARITAL_STATUS,
CUST_CONTACT

)



3           if the data is like 2013/may/22 then be sure to represent as yyyy/mon/dd and data of                   
              month must be 3 letter word like jan,feb.....
              but not janu or ja and so on.


OPTIONS (skip=1, errors=100, direct=True)
LOAD DATA
INFILE 'CustomerDataVs.csv'
TRUNCATE into table CUSTOMER_STG
FIELDS TERMINATED BY ',' optionally enclosed by '"'
(
CUST_FIRST_NAME,
CUST_LAST_NAME,
CUST_RELIGION_IDNT,
CUST_EDU_QUALI,
CUST_INCOME_IDNT,
CUST_IDNT,
ADDR_ZNE,
ADDR_DISTT,
ADDR_MUN_VDC,
CUST_MIDDLE_NAME,
CUST_DOB date 'yyyy/mon/dd',
MARITAL_STATUS,
CUST_CONTACT

)
                   4.  if the data is like 2013-may-22 then be sure to represent as yyyy-mon-dd


OPTIONS (skip=1, errors=100, direct=True)
LOAD DATA
INFILE 'CustomerDataVs.csv'
TRUNCATE into table CUSTOMER_STG
FIELDS TERMINATED BY ',' optionally enclosed by '"'
(
CUST_FIRST_NAME,
CUST_LAST_NAME,
CUST_RELIGION_IDNT,
CUST_EDU_QUALI,
CUST_INCOME_IDNT,
CUST_IDNT,
ADDR_ZNE,
ADDR_DISTT,
ADDR_MUN_VDC,
CUST_MIDDLE_NAME,
CUST_DOB date 'yyyy-mon-dd',
MARITAL_STATUS,
CUST_CONTACT

)

1 comment:

  1. Amazing stuff here, very nice to read this kind of blogs, thanks for sharing it. . .
    cheap relocations services.

    ReplyDelete