?
Solved

Need help on  oracle data load tool -- Challenging Question

Posted on 2016-10-04
7
Medium Priority
?
67 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 29

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 29

Expert Comment

by:Pawan Kumar
ID: 41829325
Ok so do one thing , please post your query here..
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 29

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

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

777 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