[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • Last Modified:

SQL Loader

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
sam_2012
Asked:
sam_2012
  • 3
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
awking00Commented:
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
 
sam_2012Author Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
slightwv (䄆 Netminder) Commented:
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
 
sam_2012Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sam_2012Author Commented:
thanks
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now