Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Loader issue

Posted on 2012-09-18
15
Medium Priority
?
1,126 Views
Last Modified: 2012-09-19
Hello Experts,

I have a CSV file where I am using SQL Loader utility:

Here is my table structure:

CREATE TABLE TEST_FEED
(
ASSIGNED_TO                        VARCHAR2(100)  ,
AUTO_ESCALATION_INDICATOR          NUMBER         ,
CFN                                NUMBER         ,
CLIENT_ID                          VARCHAR2(100)  ,
CREATE_DATE                        DATE           ,
DELIVERY_STATUS                    NUMBER         ,
EVENT_CODE                         NUMBER         ,
EVENT_SEQUENCE_ID                  NUMBER         ,
EVENT_TYPE                         VARCHAR2(1000) ,
LAST_MODIFIED_BY                   VARCHAR2(100)  ,
LOG_STRING_SIZE                    NUMBER         ,
MODIFIED_DATE                      DATE           ,
NOTIFICATION_ID                    NUMBER         ,
NOTIFICATION_FEEDBACK_LOG          VARCHAR2(4000) ,
SEQ_NO                             NUMBER         ,
SHORT_DESCRIPTION                  VARCHAR2(100)  ,
SUBMITTER                          VARCHAR2(100)  ,
TICKET                             NUMBER         ,
TICKET_ROLE                        NUMBER         ,
FBRESPONSELOG_MODIFIED_DTMINM      DATE           ,
FBRESPONSELOG_MODIFIED_DTMAXM      DATE           
);

Open in new window



Here is my Control file which I am using to load the file:

load data
 infile 'D:\test.csv'
 append into table test_feed
 fields terminated by "," 
 optionally enclosed by '"'              
 trailing nullcols 
 	(
	ASSIGNED_TO                       ,
	AUTO_ESCALATION_INDICATOR         ,
	CFN                               ,
	CLIENT_ID                           ,
	CREATE_DATE                        "TO_DATE(:CREATE_DATE,'MM/DD/YYYY HH24:MI:SS')",
	DELIVERY_STATUS                     ,
	EVENT_CODE                          ,
	EVENT_SEQUENCE_ID                   ,
	EVENT_TYPE                          ,
	LAST_MODIFIED_BY                    ,
	LOG_STRING_SIZE                     ,
	MODIFIED_DATE                      "TO_DATE(:MODIFIED_DATE,'MM/DD/YYYY HH24:MI:SS')",
	NOTIFICATION_ID                   ,
	NOTIFICATION_FEEDBACK_LOG    char(4000)   NULLIF NOTIFICATION_FEEDBACK_LOG=BLANKS,
	SEQ_NO                              ,
	SHORT_DESCRIPTION                   ,
	SUBMITTER                           ,
	TICKET                              ,
	TICKET_ROLE                         ,
	FBRESPONSELOG_MODIFIED_DTMINM      "TO_DATE(:FBRESPONSELOG_MODIFIED_DTMINM,'MM/DD/YYYY HH24:MI:SS')",
	FBRESPONSELOG_MODIFIED_DTMAXM      "TO_DATE(:FBRESPONSELOG_MODIFIED_DTMAXM,'MM/DD/YYYY HH24:MI:SS')"           
	)

Open in new window


I am getting error as :

Record 1: Rejected - Error on table TEST_FEEDBACK, column AUTO_ESCALATION_INDICATOR.
ORA-01722: invalid number

Open in new window


Where is the file the column is having 0 or 1 .

Do I need to change the control file ?
test.csv
0
Comment
Question by:Swadhin Ray
  • 7
  • 7
15 Comments
 
LVL 21

Assisted Solution

by:flow01
flow01 earned 1000 total points
ID: 38411695
try

OPTIONS (SKIP=1)
load data
   ...

to skip the first header line
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38411699
The first row is the header.  You need to skip it.  First line of the control file:

OPTIONS (SKIP=1)
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38411702
sry flow...  was typing.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 16

Author Comment

by:Swadhin Ray
ID: 38411728
Ops ! I missed while copying the control file here :


When I execute it I am getting the below error now:


Record 1: Rejected - Error on table TEST_FEED, column NOTIFICATION_FEEDBACK_LOG.
second enclosure string not present

Open in new window

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38411744
Looks like you might have double quotes inside the field.

Is this accurate?

...  Your ticket is now “Ready to Close” ...
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 38411745
How i can use  like below on my control file for NOTIFICATION_FEEDBACK_LOG column  :


From :

NOTIFICATION_FEEDBACK_LOG    char(4000)   NULLIF NOTIFICATION_FEEDBACK_LOG=BLANKS,

To :

NOTIFICATION_FEEDBACK_LOG    char(4000)  NULLIF NOTIFICATION_FEEDBACK_LOG=BLANKS "TRANSLATE(:NOTIFICATION_FEEDBACK_LOG, 'A"', 'A') ",
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 38411750
I don't think you can replace double quotes after sql loader has tried to parse the row.

You need to do this before sql loader gets the data.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 38411755
I am using a shell script to execute the loader command.

How can I use it before sql loader get the data ????
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 38411760
Here is my shell script:


#!/bin/sh
export ORACLE_BASE=/opt/app/oracle
export ORACLE_SID=MYSID
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$ORACLE_HOME/bin
export PATH=$ORACLE_HOME/bin:$PATH

sqlldr userid=username$ORACLE_SID/password control=/home/myfiles/control.ctl log=/home/myfiles/test.log skip=1

Open in new window

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38411762
I would have whomever is creating the file take care of it.  Tell them they are producing a bad file.  Which they are.

To do it after it is produced would likely require a lot of scripting/programming.  Basically you would need to replace any double quote that wasn't at the beginning, end or on both sides of a comma.  I'm not even sure how to pull that one off.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 38411771
can we use REGEXP_REPLACE for the same column ? :

Like when we want to replace the ' " ' :

SELECT
REGEXP_REPLACE('"1', '(["])|(^      )',NULL)
from dual;
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38411774
>>can we use REGEXP_REPLACE for the same column ? :

Not inside the control file.  By then sql loader has already read the line and has tried to parse it.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 38411990
Even when I tried with external table still no luck as all the field values are coming on the 1st  column.

Here is the code which I am using :

CREATE TABLE xtern_TEST_FEED
  (
    ASSIGNED_TO               VARCHAR2(100) ,
    AUTO_ESCALATION_INDICATOR NUMBER ,
    CFN                       NUMBER ,
    CLIENT_ID                 VARCHAR2(100) ,
    CREATE_DATE DATE ,
    DELIVERY_STATUS   NUMBER ,
    EVENT_CODE        NUMBER ,
    EVENT_SEQUENCE_ID NUMBER ,
    EVENT_TYPE        VARCHAR2(1000) ,
    LAST_MODIFIED_BY  VARCHAR2(100) ,
    LOG_STRING_SIZE   NUMBER ,
    MODIFIED_DATE DATE ,
    NOTIFICATION_ID           NUMBER ,
    NOTIFICATION_FEEDBACK_LOG VARCHAR2(4000) ,
    SEQ_NO                    NUMBER ,
    SHORT_DESCRIPTION         VARCHAR2(100) ,
    SUBMITTER                 VARCHAR2(100) ,
    TICKET                    NUMBER ,
    TICKET_ROLE               NUMBER ,
    FBRESPONSELOG_MODIFIED_DTMINM DATE ,
    FBRESPONSELOG_MODIFIED_DTMAXM DATE
  )
  organization external
  (
    DEFAULT DIRECTORY ORA_DIR ACCESS PARAMETERS 
    ( records delimited BY newline
    FIELDS TERMINATED BY ',' 
    optionally enclosed BY '"'
    MISSING FIELD VALUES ARE NULL 
    ( ASSIGNED_TO ,
    AUTO_ESCALATION_INDICATOR ,
    CFN ,
    CLIENT_ID ,
    CREATE_DATE CHAR date_format DATE mask "dd-mon-yyyy HH24:MI:SS" ,
    DELIVERY_STATUS ,
    EVENT_CODE ,
    EVENT_SEQUENCE_ID ,
    EVENT_TYPE ,
    LAST_MODIFIED_BY ,
    LOG_STRING_SIZE ,
    MODIFIED_DATE CHAR date_format DATE mask "dd-mon-yyyy HH24:MI:SS" ,
    NOTIFICATION_ID ,
    NOTIFICATION_FEEDBACK_LOG ,
    SEQ_NO , 
    SHORT_DESCRIPTION ,
    SUBMITTER , 
    TICKET , 
    TICKET_ROLE ,
    FBRESPONSELOG_MODIFIED_DTMINM CHAR date_format DATE mask "dd-mon-yyyy HH24:MI:SS" ,
    FBRESPONSELOG_MODIFIED_DTMAXM CHAR date_format DATE mask "dd-mon-yyyy HH24:MI:SS" ) ) LOCATION ('test.csv')
  )
  REJECT LIMIT UNLIMITED;

Open in new window

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38412013
>>Even when I tried with external table still no luck

No real surprise.  They use the same interface code.

To fix this you need to really get with the people that created the file and have them correct it.  It is a bad file they are providing you.

For example, how would you load this file:
"a","a,a","a"a",","a"","a"

What are the 'valid' column values?
0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 38413868
Thanks.. The solution is to do the clean up before starting SQL Loader.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

810 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