Solved

SQL Loader

Posted on 2013-01-30
7
552 Views
Last Modified: 2013-05-28
Hi ,

I have an input file which contains the below columns namely ,
Name, DataofJoin,emailid, address
s1,2001-03-01 00:00:00.000, s1@yahoo.com chr(10) chr(13)s2@yahoo.com,s1chr(10)3rdstreet
s2,2002-05-02 00:10:00.000,s3@yahoo.com chr(10) chr(13)s4@yahoo.com,s2chr(10)3rdstreet

iam new to using sqlloader , I need help in creating the control file for loading the above data. The problem is that, in the columns of date of join , I have a 3 Milli seconds , that I have to convert to oracle format timestamps format , columns email id and address have character 13 and character 10 in between the values for the respective columns . I need to replace them with a space.

Another problem iam facing is that the records appear in two lines because of the newline and the form feed character. Before loading I have to make then as single record and then load into Table.

Any help is much appreciated.
0
Comment
Question by:sam_2012
  • 3
  • 3
7 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
Dates and times are pretty straight forward and examples are out there.

Check out the example at:
http://nazeemdba.wordpress.com/2012/03/23/sql-loader-conversion-of-timestamp-and-dates-datatypes/

timestampcol TIMESTAMP “dd-Mon-yy hh:mi:ss:ff9AM”

You just need the proper format to handle the format in the file.

Now for your multiple lines.

This may or may not be possible. It depends on your actual data.

Check out:  How does one load multi-line records?
http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_control_file.htm#i1005509

If there are fixed lines per record, it is pretty simple.  Use concatenate.

If you have the possibility of more than one line in the file as a specific record, you need some delimiter to decide what is the start of the next record to use continueif.

If you can upload a sample of your data and post expected results we might be able to post a complete working example.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Sample data and expected results would truly be helpful (just need a few records to test with) . A description of your target table (i.e. column names and datatypes) would also help.
0
 

Author Comment

by:sam_2012
Comment Utility
hi ,
I have attached the data for your reference. when iam loading the data sql loader should treat each record as single record i.e the new line character after 3 rd column should not be treated as a newline character but instead column value spanning mutliple  rows.

s1,2001-03-01 00:00:00.000,"s1@yahoo.com

s2@yahoo.com","s1
3rdstreet"
S2,2002-05-02 00:10:00.000,"s3@yahoo.com

s4@yahoo.com","s2
3rdstreet"
S3,2002-05-02 00:10:00.000,"s4@yahoo.com

s5@yahoo.com","s2
3rdstreet"
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.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Do you have fixed lines per row or can you have a fidderent number of lines per row?

From the data you posted it looks like 5 lines in the file per row in the database.  Is this the case for ALL rows in the file?

If this is the case, look at concatenate in the link I posted.

For removing the chr(10) and chr(13), it is a simple matter of a replace function in the sql loader control file.

There are many examples out there on these options.
0
 

Author Comment

by:sam_2012
Comment Utility
Do you have fixed lines per row or can you have a fidderent number of lines per row?

From the data you posted it looks like 5 lines in the file per row in the database.  Is this the case for ALL rows in the file?

The answer to both the above questions is that we have may fidderent number of lines per row. Also , for all the rows we  may have 5 or 3 lines or one line , if it does not contain the chr(10) and chr(13) character.


Also , Iam not supposed to remove the chr(10) and chr(13) character for the columns email id and address.  I have to load them as is in the database.

It is bit complex , any solution is much appreciated.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Since you can have a different number of lines, you will need to use CONTINUEIF (referenced in the link I posted above).

For this to work, you need a way to determine when the next record starts.  You need some way of knowing this or I don't think it will be possible.

From the sample you posted, I don't see a record delimiter so this might not be possbible with this data.
0
 

Author Closing Comment

by:sam_2012
Comment Utility
thanks
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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

772 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

10 Experts available now in Live!

Get 1:1 Help Now