?
Solved

SQL Loader

Posted on 2007-10-18
10
Medium Priority
?
1,207 Views
Last Modified: 2013-12-19
Hi,

I have a .CSV file with diffrent fields one of the field is Date( Have data in diffrent date format)
I am try to load but I am face this kin of errors

---- SQL LOADER SCRIPT FILE ---
--options(skip=1)
options(Errors=20000)
load data
infile 'C:\pdxappdb_scripts\fadi_tables\csv\habib_treq_dbs007.csv'    
badfile 'C:\pdxappdb_scripts\fadi_tables\TREQ.bad'
discardfile 'C:\pdxappdb_scripts\fadi_tables\TREQ.dsc'
replace
into table  TREQ_DBS
fields terminated by ',' optionally enclosed by '$'
--fields terminated by '$'
trailing nullcols
(
      REQUESTNO       ,
      SAFETYCODE       CHAR(1000),
      ENVTCODE       CHAR(500),
      SRED             CHAR(500),
      MSDS             CHAR(500),
      CHEMICAL       CHAR(1000),
      MADE             CHAR(1000),
--      CALENDAR_DATE       "to_date (:CALENDAR_DATE, 'yyyy-mm-dd hh24:mi:ss')" ,
      CALENDAR_DATE       "to_date (:CALENDAR_DATE, 'RRRR/MM/DD')",
      REQUESTOR        CHAR(1000),
      CELLTYPE       CHAR(400),
      CELLBATCH       CHAR(1000),
      SUBBATCH       CHAR(400),
      CELLNO             ,
      PRIORITY        ,
      PURPOSE       char(1000),
      NO_OFASSEM       ,
      CATHODE       CHAR(1000),
      ANODE             CHAR(1000),
      ELEC              CHAR(800),
      DESIGN             CHAR(800),
      COND             CHAR(800),
      PERFORM       ,
      SAFETY             ,
      CONDDETAILS       CHAR(800),
      PERFORMDETAILS       CHAR(1800),
      NO_OFCYCLE       ,
      SAFETYDETAILS       CHAR(1800),
      NO_OFABUSE       ,
      DATATECHNICIAN       ,
      COMMENTS       CHAR(800),
      THISTREQ       CHAR(400)
--      EXPERIMENT_ID       ,
--      APPROVED       

)

 
                    ------------------------------

 ----- Errors ------

SQL*Loader: Release 9.2.0.1.0 - Production on Thu Oct 18 16:21:19 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Control File:   C:\pdxappdb_scripts\fadi_tables\TREQ_DBS.txt
Data File:      C:\pdxappdb_scripts\fadi_tables\csv\habib_treq_dbs007.csv
  Bad File:     C:\pdxappdb_scripts\fadi_tables\TREQ.bad
  Discard File: C:\pdxappdb_scripts\fadi_tables\TREQ.dsc
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 20000
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table TREQ_DBS, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
REQUESTNO                           FIRST     *   ,  O($) CHARACTER            
SAFETYCODE                           NEXT  1000   ,  O($) CHARACTER            
ENVTCODE                             NEXT   500   ,  O($) CHARACTER            
SRED                                 NEXT   500   ,  O($) CHARACTER            
MSDS                                 NEXT   500   ,  O($) CHARACTER            
CHEMICAL                             NEXT  1000   ,  O($) CHARACTER            
MADE                                 NEXT  1000   ,  O($) CHARACTER            
CALENDAR_DATE                        NEXT     *   ,  O($) CHARACTER            
    SQL string for column : "to_date (:CALENDAR_DATE, 'RRRR/MM/DD')"
REQUESTOR                            NEXT  1000   ,  O($) CHARACTER            
CELLTYPE                             NEXT   400   ,  O($) CHARACTER            
CELLBATCH                            NEXT  1000   ,  O($) CHARACTER            
SUBBATCH                             NEXT   400   ,  O($) CHARACTER            
CELLNO                               NEXT     *   ,  O($) CHARACTER            
PRIORITY                             NEXT     *   ,  O($) CHARACTER            
PURPOSE                              NEXT  1000   ,  O($) CHARACTER            
NO_OFASSEM                           NEXT     *   ,  O($) CHARACTER            
CATHODE                              NEXT  1000   ,  O($) CHARACTER            
ANODE                                NEXT  1000   ,  O($) CHARACTER            
ELEC                                 NEXT   800   ,  O($) CHARACTER            
DESIGN                               NEXT   800   ,  O($) CHARACTER            
COND                                 NEXT   800   ,  O($) CHARACTER            
PERFORM                              NEXT     *   ,  O($) CHARACTER            
SAFETY                               NEXT     *   ,  O($) CHARACTER            
CONDDETAILS                          NEXT   800   ,  O($) CHARACTER            
PERFORMDETAILS                       NEXT  1800   ,  O($) CHARACTER            
NO_OFCYCLE                           NEXT     *   ,  O($) CHARACTER            
SAFETYDETAILS                        NEXT  1800   ,  O($) CHARACTER            
NO_OFABUSE                           NEXT     *   ,  O($) CHARACTER            
DATATECHNICIAN                       NEXT     *   ,  O($) CHARACTER            
COMMENTS                             NEXT   800   ,  O($) CHARACTER            
THISTREQ                             NEXT   400   ,  O($) CHARACTER            

Record 1: Rejected - Error on table TREQ_DBS, column CALENDAR_DATE.        ---  I give just one line
ORA-01847: day of month must be between 1 and last day of month


Table TREQ_DBS:
  165 Rows successfully loaded.
  14406 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 251064 bytes(12 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:         14571
Total logical records rejected:     14406
Total logical records discarded:        0

Run began on Thu Oct 18 16:21:19 2007
Run ended on Thu Oct 18 16:24:52 2007

Elapsed time was:     00:03:33.76
CPU time was:         00:00:03.50


What Format I need to Load diffrent date format into that Date Field

bye










0
Comment
Question by:patmoli
  • 8
10 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 20105646
try this format ...
TO_DATE(to_char(:CALENDAR_DATE,'DD-MON-YYYY','DD-MON-YYYYHH24:MI'))
0
 
LVL 18

Accepted Solution

by:
sventhan earned 1500 total points
ID: 20105649
i mean

try this format ...
TO_DATE(to_char(:CALENDAR_DATE,'DD-MON-YYYY'),'DD-MON-YYYYHH24:MI')
0
 
LVL 15

Expert Comment

by:ishando
ID: 20105655
Can you post a failed record?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 18

Expert Comment

by:sventhan
ID: 20105657
0
 
LVL 18

Expert Comment

by:sventhan
ID: 20105673
another one...

http://www.experts-exchange.com/Database/Oracle/Q_21156434.html?sfQueryTermInfo=1+ora-01847

There are solutions alreay avilable here in EE itself. If you do some search you do not have to waste the points... ;)

Good Luck.
0
 
LVL 2

Author Comment

by:patmoli
ID: 20110586
Hi,

How to define Tab as a fields terminater in SQL Loader


bye
0
 
LVL 18

Expert Comment

by:sventhan
ID: 20110927
While editing the control file, I entered a tab
between the quotes after 'terminated by' and
that worked for me.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 20111297
0
 
LVL 18

Expert Comment

by:sventhan
ID: 20111306
it worked for some one

FIELDS TERMINATED BY X'9'
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question