SQL Server/Oracle data load-SQL loader
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: SQL Server/Oracle data load-SQL loader

  1. #1
    Join Date
    Aug 2002
    Posts
    40

    SQL Server/Oracle data load-SQL loader

    I use to load data from MS-SQL server to Oracle-9i using DTS package on SQL Server.It works fine,but takes more time.

    In order to reduce the load time for this tranformation,I loaded data from SQL Server to flat file using DTS package and
    from flat file to Oracle using SQL Loader which is much faster than using only DTS package.In few cases it works very well .

    BUT THE PROBLEM COMES in the follwoing columns :

    sql server oracle
    datatype datatype
    ---------- ----------

    datetime date
    money number
    Int number
    bit char
    these columns data give the problem for ORA-01722: invalid number when I load from flat file to oracle using sqlloader.THIS PROBLEM DOES NOT COME WHEN I AM USING DTS PACKAGE.

    ANY CLUE to resolve this problem...I would appreciate the help.

    Thanks

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    More info required ... give us the SQL*Loader control file and sample data also please
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    This may be due to date format in the flat file.
    If you use to_date function in the sqlloader control file, you will be ok.

    Tamil

  4. #4
    Join Date
    Aug 2002
    Posts
    40
    Here is content of my control file-

    Load data
    infile 'c:\temp\feeschedule.txt'
    badfile 'c:\temp\feeschedule.bad'
    discardfile 'c:\temp\feeschedule.dsc'
    append into table odstest.feeschedule
    fields terminated by '|'
    trailing nullcols
    (feescheduleid,
    revenue_l,
    revenue_h,
    anualfee,
    lastmodifed "to_char(:lastmodifed,'yyyy-mm-dd')")

    But this control file gives error on last line-ORA-01722: invalid number

    I would appreciate you help.

    Thanks

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    What is the datatype of the column LASTMODIFIED in the table ODSTEST.FEESCHEDULE? Also, can you post a sample of your actual datafile you are loading from. Few lines will be sufficient.

    I'm suspecting the column's datatype is DATE and the corresponding part in your infile is in format "YYYY-MM-DD". If this is true, then you should change xour controlfile to something like:

    Load data
    infile 'c:\temp\feeschedule.txt'
    badfile 'c:\temp\feeschedule.bad'
    discardfile 'c:\temp\feeschedule.dsc'
    append into table odstest.feeschedule
    fields terminated by '|'
    trailing nullcols
    (feescheduleid,
    revenue_l,
    revenue_h,
    anualfee,
    lastmodifed DATE "yyyy-mm-dd")
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Aug 2002
    Posts
    40

    Sql Loader-Date format

    Thanks Guys!The problem has been resolved .It was formatting problem and i used the following format in control file-

    Load data
    infile 'c:\temp\feeschedule.txt'
    badfile 'c:\temp\feeschedule.bad'
    discardfile 'c:\temp\feeschedule.dsc'
    replace into table odstest.feeschedule
    fields terminated by '|'
    trailing nullcols
    (feescheduleid,
    revenue_l,
    revenue_h,
    anualfee,
    lastmodifed "to_date(:lastmodifed,'YYYY-MM-DD HH24:MI:SS')")

    Thanks for all the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width