DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Sqlloader , validate data before load

  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Sqlloader , validate data before load

    Hello everybody,

    some application generates ASCII-Files
    like monday.dat, tuesday.dat etc.
    I would like to load data form this tables
    into a table in Oracle using following validation rules:
    If a record already exists in the table then skip it.
    If a record has been updated on Monday evening - update it in the table.
    If a record doesn't exist - insert it.
    Hope you understand what I mean

    Thanks in advance.

    WinNT, Oracle 8.1.7

  2. #2
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    I think the best thing would be to go for UTL_FILE rather than SQLLOADER unless its huge amount of data.

    If you use UTL_FILE, you can do all your checks

    SS

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    . . . or SQL*loader into a work table and use PL/SQL to transfer the insert/updates to the target table.

  4. #4
    Join Date
    Jan 2004
    Posts
    4
    Srinivas_Sharma

    thx, can you recommend some good examples for usage of the UTL_FILE ?


    DaPi

    thx, should I use the trigger to compare this 2 tables? how can one define the validation rules for it?

  5. #5
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    http://www.adp-gmbh.ch/ora/plsql/utl_file.html

    comparative analysis of sqlloader and utl_file

    http://www.orafaq.com/papers/sqlload.doc

    check out documentation in oracle site as well......

    HTH

    Srini

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    If you have the choice, I see several advantages of using SQL*loader with a work table:
    - you don't have to have access to the server, other than as an Oracle client, and no changes in init.ora are required
    - you can sort the data at will and can deal with "duplicate" rows
    - the data is parsed (separated into columns) for you

    UTL_FILE comes into its own when the file can not be loaded (easily) as a table: reading a transaction stream where the record format is a function of the transaction code; reading free text (e.g. alrt.log).

    The validation rules (written in PL/SQL) would be much the same in either case. In this case, if you use SQL*loader, you might even be able to do it with a collection of SQL statements.

    I don't think I would use a trigger . . . .

  7. #7
    Join Date
    Mar 2002
    Posts
    534
    In 9i you could do it over the external table feature ;-)
    So in case your are looking for arguments to move from 8i to 9i this would be a good one (depending on how often you would use it)
    Last edited by mike9; 01-07-2004 at 10:57 AM.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by mike9
    In 9i you could do it over the external table feature ;-)
    So in case your are looking for arguments to move from 8i to 9i this would be a good one (depending on how often you would use it)
    Indeed -- with a combination of an external table + a MERGE statement, it'd be a one-step process.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    The external table has to be on the server - this may or may not be a problem . . .

    "If a record already exists in the table then skip it.
    If a record has been updated on Monday evening - update it in the table."
    Will MERGE do that?

  10. #10
    Join Date
    May 2001
    Posts
    73
    As DaPi suggested before, using a work table and pl/sql is much simplier.
    OCP 7.3,8.0,8i,9i

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