6 Replies Latest reply: Jan 11, 2013 9:45 AM by Astr0 RSS

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

    Astr0
      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
          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
            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
              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
                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
                  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
                    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