Solved

SQL Loader

Posted on 2013-01-30
7
567 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
[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 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

726 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