1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problem in SQL loader

Discussion in 'Other Development Tools' started by Prafullw, Jun 21, 2011.

  1. Prafullw

    Prafullw Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    75
    I have a problem while loading a data through SQL loader.
    I have a description column in csv file.
    In file data for this column is split in multiple lines
    for example

    "MACRO PC-4000
    4""CUT ON ALL INTERNAL ENDS BEFORE SLICES, MACRO SLICES ALL ENDS PLUS EXTRA MACRO SLICES ALL ENDS, PHOTO QUALITY, BSP-196 + 107. P. KEEFE FOR ??? & PANCAKE PC. LABS: TEST TO ZCMS 408, MTS 1103, DMD 489.22, C/MR/E."

    so while loading using SQL loader it loads only "MACRO PC-4000"
    rest of the line is ignored.
    how to remove this new line to load full data in same column
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Perhaps you can copy the SQL Loader data in an excel file and do a find and replace to replace all newline characters with a space. I am just guessing... :)
     
  3. Prafullw

    Prafullw Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    75
    I have done this to resolve the problem
    But is there any other way(some kind of syntax in control file)
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes but you'll need to modify the data file to accomplish that task. There is a CONTINUEIF directive for SQL*Loader to concatenate physical records into a logical record:

    Code (SQL):
    SQL> CREATE TABLE con_test(
    2 id NUMBER,
    3 txtval varchar2(80));
     
    TABLE created.
     
    SQL>
    SQL> $cat con_test.ctl
     
    LOAD DATA
    INFILE 'c:\oracle_stuff\examples\continue.dat'
    continueif (1:1) = '*'
    INTO TABLE con_test
    (id POSITION(1:3) INTEGER external, txtval POSITION(4:80) CHAR)
     
    SQL> $cat continue.dat
     
    *001This IS the TIME FOR ALL good men
    Xto come TO the aid OF their country
    *002Never do a sand dance
    Xafter a chicken act
     
    SQL> $sqlldr bing/bong con_test.ctl
     
    SQL*Loader: Release 10.2.0.1.0 - Production ON Thu Jun 30 12:13:57 2011
    Copyright (c) 1982, 2005, Oracle. ALL rights reserved.
     
    Commit point reached - logical record COUNT 2
     
    SQL>
    SQL> SELECT * FROM con_test;
     
    ID TXTVAL
    ---------- --------------------------------------------------------------------------------
    1 This IS the TIME FOR ALL good men TO come TO the aid OF their country
    2 Never do a sand dance after a chicken act
     
    SQL>
     
    Prafullw likes this.
  5. Prafullw

    Prafullw Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    75
    Hi David,

    Thanks for your reply,
    I have done the changes as you said it works now.
    :hurray:hurray:hurray