Solved

Need help on  oracle data load tool -- Challenging Question

Posted on 2016-10-04
7
61 Views
Last Modified: 2016-10-07
Hi Team,

I have an file the data in an input file called as emaidata.txt

"JobID"|"BatchID"|"SubID"|"EmailAddr"|"FirstName"|"LastName"|"pincode"|"SendDate"
"2796921"|"1442"|"151719024"|"RAJU@gmail.com"|""|""|""|"2016-07-31 02:40:37.697000000"
"2796926"|"487"|"75326115"|"CHANDRU@VINTEL.EDU"|""|""|""|"2016-07-31 01:39:52.070000000"
"2797764"|"1065"|"33696904"|"RAMCHAMDRA@GMAIL.COM"|""|""|""|"2016-07-29 20:34:14.113000000"
"2797764"|"5749"|"40004462"|"DSOUZA@VINTEL.edu"|""|""|""|"2016-07-30 14:52:42.443000000"
"2797764"|"13077"|"108620732"|"NAGAVENU@GMAIL.com"|""|""|""|"2016-07-31 15:19:48.123000000"
"2797764"|"13790"|"144806831"|"HAPPYTOHELP@outlook.com"|""|""|""|"2016-07-31 18:23:40.883000000"
"2797764"|"10633"|"42705052"|"venkatnarayan@vintel.edu"|""|""|""|"2016-07-31 06:14:53.760000000"
"2797764"|"13922"|"168459259"|"alxendar@outlook.com"|""|""|""|"2016-07-31 19:19:29.643000000"

I need to load this data into a table called as tmp_email. Below is the control file i have created , but it gives me an error.

Structure of Tmp_email
WORKID  NUMBER
 BatchID NUMBER
 SubID  NUMBER
 EmailAddr VARCHAR2(80)
 FirstName VARCHAR2(80)
 LastName VARCHAR2(80)
 pinCode NUMBER
 Senddate timestamp
 


load data
infile 'emaildata.txt'
TRUNCATE
into table TMP_email
fields terminated by '|'  OPTIONALLY ENCLOSED BY '"'
(WORKID  "RTRIM(LTRIM(:WORKID ,'\"'),'\"')",
 BatchID "RTRIM(LTRIM(:BatchID ,'"'),'"')",
 SubID  "RTRIM(LTRIM(:SubID ,'"'),'"')" ,
 EmailAddr "RTRIM(LTRIM(:EmailAddr ,'"'),'"')",
 FirstName "RTRIM(LTRIM(:FirstName ,'"'),'"')",
 LastName "RTRIM(LTRIM(:LastName ,'"'),'"')",
 pinCode "RTRIM(LTRIM(:PINCODE ,'"'),'"')",
 Senddate "RTRIM(LTRIM(:Senddate ,'"'),'"')" )
 

 
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Oct 5 02:05:43 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-350: Syntax error at line 6.
Expecting "," or ")", found "),".
(JobID  "RTRIM(LTRIM(:JOBID ,'"'),'"')",


Also, I need to convert the string into timestamp.

Any help is really appreciated.
0
Comment
Question by:sam_2012
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41829313
Try this on which the column you wanted to convert.

CASE WHEN TRY_PARSE(coltobeConverted AS DATETIME) IS NOT NULL THEN TO_TIMESTAMP (coltobeConverted, 'DD-Mon-RR HH24:MI:SS.FF') END
0
 

Author Comment

by:sam_2012
ID: 41829318
about the error Iam getting for rtrim and ltrim , also , I need to remove the " in the data while inserting into the database table.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41829325
Ok so do one thing , please post your query here..
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:sam_2012
ID: 41829388
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Oct 5 02:05:43 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-350: Syntax error at line 6.
Expecting "," or ")", found "),".
(JobID  "RTRIM(LTRIM(:JOBID ,'"'),'"')",

This is the error Iam getting when I run sqlloader.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41829391
Can you provide us the entire query you have used in there?
0
 

Author Comment

by:sam_2012
ID: 41829393
In this question  i want to resolve the sqlldr issue
0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 41829701
I used the below and your sample data loaded just fine.
OPTIONS	(SKIP=1)
LOAD DATA
	INFILE *
	DISCARDMAX 9999
	APPEND INTO TABLE tmp_email
	fields terminated by "|" optionally enclosed by '"'
	trailing NULLCOLS
	(
		WORKID,
		BATCHID,
		SUBID,
		EMAILADDR,
		FIRSTNAME,
		LASTNAME,
		PINCODE,
		SENDDATE TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF9'
	)
BEGINDATA
"JobID"|"BatchID"|"SubID"|"EmailAddr"|"FirstName"|"LastName"|"pincode"|"SendDate"
"2796921"|"1442"|"151719024"|"RAJU@gmail.com"|""|""|""|"2016-07-31 02:40:37.697000000"
"2796926"|"487"|"75326115"|"CHANDRU@VINTEL.EDU"|""|""|""|"2016-07-31 01:39:52.070000000"
"2797764"|"1065"|"33696904"|"RAMCHAMDRA@GMAIL.COM"|""|""|""|"2016-07-29 20:34:14.113000000"
"2797764"|"5749"|"40004462"|"DSOUZA@VINTEL.edu"|""|""|""|"2016-07-30 14:52:42.443000000"
"2797764"|"13077"|"108620732"|"NAGAVENU@GMAIL.com"|""|""|""|"2016-07-31 15:19:48.123000000"
"2797764"|"13790"|"144806831"|"HAPPYTOHELP@outlook.com"|""|""|""|"2016-07-31 18:23:40.883000000"
"2797764"|"10633"|"42705052"|"venkatnarayan@vintel.edu"|""|""|""|"2016-07-31 06:14:53.760000000"
"2797764"|"13922"|"168459259"|"alxendar@outlook.com"|""|""|""|"2016-07-31 19:19:29.643000000"

Open in new window

The optionally enclosed by will remove the double quotes.  Not sure why you are trying to do that with trim commands.  All you should need to do with the control file you have is to remove all the trims and add the format for the timestamp.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ER Diagram 3 42
sum of columns in a row in oracle 3 44
sql server store procedure contains temp tables need to convert oracle? 3 38
SQL Syntax Question 9 30
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

730 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