This discussion is archived
6 Replies Latest reply: Jan 11, 2013 7:45 AM by Astr0 RSS

SQL * Loader : Load data with format MM/DD/YYYY HH:MI:SS PM

Astr0 Newbie
Currently Being Moderated
Please advice how to load data with format MM/DD/YYYY HH:MI:SS PM into an Oracle Table using SQL * Loader.

- What format should I give in the control file?
- What would be the column type to create the table to load data.

Sample data below;

MM/DD/YYYY HH:MI:SS PM

12/9/2012 2:40:20 PM
11/29/2011 11:23:12 AM

Thanks in advance
Avinash
  • 1. Re: SQL * Loader : Load data with format MM/DD/YYYY HH:MI:SS PM
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post exact OS and database versions. What have you found in your research so far ?

    http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_field_list.htm#i1016161

    HTH
    Srini
  • 2. Re: SQL * Loader : Load data with format MM/DD/YYYY HH:MI:SS PM
    Astr0 Newbie
    Currently Being Moderated
    Hello Srini,

    OS - UNIX
    DB version - Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


    I tried to insert the data into the column as follows
    CREATION_DATE          "TO_CHAR(TO_DATE(:CREATION_DATE,'MM/DD/YYYY HH:MI:SS AM'),'DD-MON-YYYY HH:MI:SS AM')",
    where creation_date is a column of timestamp(6) datatype in the table.

    I tried altering the column to timestamp(0) but i ended up with
    12/9/2012 2:40:20. PM
    instead of
    12/9/2012 2:40:20 PM
    Please rectify my mistakes if any.

    Regards,
    Avinash
  • 3. Re: SQL * Loader : Load data with format MM/DD/YYYY HH:MI:SS PM
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    The DATE datatype should be sufficient - http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#autoId12

    Pl post the complete sqlldr command, the contents of the control file, a sample of the input data file and a description of the table

    HTH
    Srini
  • 4. Re: SQL * Loader : Load data with format MM/DD/YYYY HH:MI:SS PM
    Astr0 Newbie
    Currently Being Moderated
    Hello Srini,

    I had tried with the creation date as DATE datatype but i had got an error as
    ORA-01830: date format picture ends before converting entire input string
    I am running the SQL*LOADER from Oracle R12 EBS front-end.

    the contents of my control file is
    LOAD DATA
    INFILE "$_FileName" 
    REPLACE
    
    INTO TABLE po_recp_int_lines_stg
    WHEN (01) = 'L'
    FIELDS TERMINATED BY "|" 
    OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    INDICATOR                POSITION(1) CHAR,
    TRANSACTION_MODE          "TRIM(:TRANSACTION_MODE)",
    RECEIPT_NUMBER               "TRIM(:RECEIPT_NUMBER)",
    INTERFACE_SOURCE          "TRIM(:INTERFACE_SOURCE)",
    RECEIPT_DATE               "TO_CHAR(TO_DATE(:RECEIPT_DATE,'MM/DD/YYYY'),'DD-MON-YYYY')",
    QUANTITY               "TRIM(:QUANTITY)",
    PO_NUMBER               "TRIM(:PO_NUMBER)",
    PO_LINE_NUMBER               "TRIM(:PO_LINE_NUMBER)",
    CREATION_DATE               "TO_CHAR(TO_DATE(:CREATION_DATE,'MM/DD/YYYY HH:MI:SS AM'),'DD-MON-YYYY HH:MI:SS AM')",
    ERROR_MESSAGE                   "TRIM(:ERROR_MESSAGE)",
    PROCESS_FLAG                    CONSTANT 'N',
    CREATED_BY                      "fnd_global.user_id",
    LAST_UPDATE_DATE                SYSDATE,
    LAST_UPDATED_BY                 "fnd_global.user_id"
    )
    {code}
    
    My data file goes like
    {code}
    H|CREATE|123|ABC|12/10/2012||||
    L|CREATE|123|ABC|12/10/2012|100|PO12345|1|12/9/2012  2:40:20 PM
    L|CORRECT|123|ABC|12/10/2012|150|PO12346|2|11/29/2011 11:23:12 AM{code}
    
    Below is the desc of the table
    {code}
    INDICATOR             VARCHAR2 (1 Byte)                         
    TRANSACTION_MODE        VARCHAR2 (10 Byte)                         
    RECEIPT_NUMBER             NUMBER                         
    INTERFACE_SOURCE        VARCHAR2 (20 Byte)                         
    RECEIPT_DATE             DATE                    
    QUANTITY             NUMBER                    
    PO_NUMBER             VARCHAR2 (15 Byte)                         
    PO_LINE_NUMBER             NUMBER                         
    CREATION_DATE             TIMESTAMP(0)                         
    ERROR_MESSAGE             VARCHAR2 (4000 Byte)                         
    PROCESS_FLAG             VARCHAR2 (5 Byte)                         
    CREATED_BY             NUMBER               
    LAST_UPDATE_DATE        DATE               
    LAST_UPDATED_BY             NUMBER     {code}
    
    Thanks,
    Avinash                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 5. Re: SQL * Loader : Load data with format MM/DD/YYYY HH:MI:SS PM
    User286067 Journeyer
    Currently Being Moderated
    Astr0 wrote:
    Hello Srini,

    I had tried with the creation date as DATE datatype but i had got an error as
    ORA-01830: date format picture ends before converting entire input string
    CREATION_DATE               "TO_CHAR(TO_DATE(:CREATION_DATE,'MM/DD/YYYY HH:MI:SS AM'),'DD-MON-YYYY HH:MI:SS AM')",
    try
    CREATION_DATE "to_Date(:CREATION_DATE,'yyyy-mm-dd hh:mi:ss AM')",
    does this help? it will load properly in timestamp column

    Raj
  • 6. Re: SQL * Loader : Load data with format MM/DD/YYYY HH:MI:SS PM
    Astr0 Newbie
    Currently Being Moderated
    Hi Raj,

    I don't know why i had been thinking too much..


    THANKS.. THIS WORKED LIKE A CHARM.. Great.


    And BTW this loaded the data into the DATE type column as suspected by Srini..

    ~Avinash

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points