Solved

SQL Loader

Posted on 2013-01-30
7
563 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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 38837708
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 32

Expert Comment

by:awking00
ID: 38840450
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
ID: 38864940
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
Independent Software Vendors: 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!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38864970
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
ID: 38866645
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38868557
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
ID: 39201361
thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

685 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