Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    13

    Unanswered: Loading data using sqlldr

    Hello,

    I have troubles inserting a flat file with data in our database. I receive duplicate key errors... This is in fact normal because the data is bad . One of the PK fields is a sequence number. Because it is the same for certain records it gives these errors. Would it be possible to increment the sequence number by one if a record with the same key already exists in the table using a command in the .ctl file?

    I hope I have been clear in my explaination, otherwise a simplified example...

    TABLE

    FIELD1 X(20)
    SEQNUM X(03)

    FIELD1 + SEQNUM is Primary Key

    This is my ficticious table....

    DATA

    BLABLA|001
    BLABLA|001

    I want to insert this data that is found in a flat file into the database using sqlldr...

    and want it to look like this

    BLABLA|001
    BLABLA|002

    I suppose I could insert the data, take the .bad file... rework it with some sort of shell script and reinsert it again. But I want to know if there is another solution.


    Thanks!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As the primary key value is bad (you said so), I'd say that there's no reason for you to pay attention to this value at all. It, obviously, means nothing as it is possible to change this value (add 1 or something like that).

    Therefore, you could either use SQL*Loader "SEQUENCE" function, or create a database trigger which would populate primary key column with a (database) sequence number.

    For example:
    Code:
    -- control file
    
    LOAD DATA
    INFILE *
    APPEND INTO TABLE table_name
    FIELDS TERMINATED BY ","
    
    (  field1 SEQUENCE(MAX, 1)),
    ...
    )
    Or
    Code:
    CREATE OR REPLACE TRIGGER trg_tab_f1
    BEFORE INSERT ON TABLE_NAME
    FOR EACH ROW
    BEGIN
      SELECT seq.NEXTVAL INTO :NEW.field1 FROM dual;
    END;
    /

  3. #3
    Join Date
    Feb 2006
    Posts
    13
    The sequence numbering should start over from 1 for each new value of FIELD1. The SEQUENCE approach does not seem to work in this case. Any other ideas?

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    load everything and then run a prodcedure or something to
    augment the sequence to however you want it.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Feb 2006
    Posts
    13
    Quote Originally Posted by The_Duck
    load everything and then run a prodcedure or something to
    augment the sequence to however you want it.
    Can u give me an example of such a procedure?

    I suppose I have to turn the indexes of and then insert everything, run the procedure and rebuild the indexes. Right?

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Or, you could use the input file as an external table and insert correctly sorted data into the real database table. This is the walkthrough: as a privileged user, create external directory and grant required privileges to the user which will work with the external table (Scott in this example):
    Code:
    C:\TEMP>sqlplus SYSTEM/password@ora10g
    
    CREATE OR REPLACE DIRECTORY ext_dir AS 'c:\temp';
    
    CONNECT sys/password@ora10g
    
    GRANT READ, WRITE ON DIRECTORY ext_dir TO scott;
    Now connect as Scott, create external table and insert data found in there into the database:
    Code:
    CONNECT scott/tiger@ora10g
    
    CREATE TABLE EXT_TABLE_CSV 
    (  id NUMBER,
       naziv VARCHAR2(20)
    )
    ORGANIZATION EXTERNAL
    ( TYPE oracle_loader
      DEFAULT DIRECTORY ext_dir
      ACCESS PARAMETERS 
      ( RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ';'
        MISSING FIELD VALUES ARE NULL
      )
      LOCATION ('test.txt')
    )
    REJECT LIMIT UNLIMITED
    /
    
    INSERT INTO real_database_table (id, naziv)
    (SELECT rank() over (PARTITION BY naziv ORDER BY rownum), naziv
     FROM EXT_TABLE_CSV
     GROUP BY naziv, rownum
    );
    Finally, the output would be like this:
    Code:
    SQL> select naziv, id from ext_table_csv order by naziv, id;
    
    NAZIV                        ID
    -------------------- ----------
    karte                        23
    karte                        24
    majica                       40
    majica                       53
    majica                       66
    patike                       49
    patike                       49
    
    7 rows selected.
    
    SQL> select naziv, id from real_database_table order by naziv, id;
    
    NAZIV                                  ID
    ------------------------------ ----------
    karte                                   1
    karte                                   2
    majica                                  1
    majica                                  2
    majica                                  3
    patike                                  1
    patike                                  2
    
    7 rows selected.
    
    SQL>
    Last edited by Littlefoot; 07-15-06 at 20:09.

Posting Permissions

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