Solved

Need help on  oracle data load tool -- Challenging Question

Posted on 2016-10-04
7
34 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
  • 3
  • 3
7 Comments
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41829325
Ok so do one thing , please post your query here..
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 18

Expert Comment

by:Pawan Kumar Khowal
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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
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

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now