Solved

Need help on  oracle data load tool -- Challenging Question

Posted on 2016-10-04
7
43 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 25

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 25

Expert Comment

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

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

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 shows how to recover a database from a user managed backup

863 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

27 Experts available now in Live!

Get 1:1 Help Now