This discussion is archived
3 Replies Latest reply: Nov 30, 2012 12:54 AM by BluShadow RSS

CALL USER DIFINED FUNCTION IN SQLLDR

OraFighter Newbie
Currently Being Moderated
Is is it possible to call User defined function in SQLLDR ?

COL1 COL2 COL3
1 2 06
1 2 06
1 3 06
1 4 06
2 1 06
2 1 06

I have to ignore 2nd and 6th row while loading the data . because it is duplicate records .
Is it possible in SQLLDR.
  • 1. Re: CALL USER DIFINED FUNCTION IN SQLLDR
    hm Expert
    Currently Being Moderated
    Why do you need a user defined function for that task?
    When you define a unique constraint for these three columns in the table, the loader will not load these data and they will go in the discard-file (if you have defined one).
  • 2. Re: CALL USER DIFINED FUNCTION IN SQLLDR
    BluShadow Guru Moderator
    Currently Being Moderated
    Or, if you use an External Table rather than SQL Loader, you can select the distinct rows from the external table as part of your SQL query.... no need for any UDF.
  • 3. Re: CALL USER DIFINED FUNCTION IN SQLLDR
    Mohamed Houri Pro
    Currently Being Moderated
    You are using duplicate thread. Please keep only one in action and delete the other one.

    If you are looking for a method to avoid duplicate key why not let the unique/primary key in place and then track down the duplicate record at the end of the load. One method (thought it might be not the best method) would consist of loading your data using a direct path load in which duplicate keys will be silenlty ignored and then re-enable your primary key at the end of the load using the exceptions table to locate and delete the duplicate keys before validating the unique/primary key contraints. The below links might give you an idea about this method

    http://hourim.wordpress.com/category/direct-path/
    http://hourim.wordpress.com/?s=exceptions
    SQL> create table t1 (id number);
    
    Table created.
    
    SQL> alter table t1 add constraint t1_pk primary key (id);
    
    Table altered.
    
    
    SQL> select constraint_name, status from user_constraints where table_name = 'T1';
    
    CONSTRAINT_NAME                STATUS
    ------------------------------ --------
    T1_PK                          ENABLED
    
    SQL> select count(1) from t1;
    
      COUNT(1)
    ----------
             0
    
    C:\>sqlldr user/pssword@database control=c.ctl direct=true           
    
    SQL> select count(1) from t1;
    
      COUNT(1)
    ----------
             4
    
    SQL> select constraint_name, status from user_constraints where table_name = 'T1';
    
    CONSTRAINT_NAME                STATUS
    ------------------------------ --------
    T1_PK                          ENABLED
    
    SQL> select index_name, status from user_indexes where table_name = 'T1';
    
    INDEX_NAME                     STATUS
    ------------------------------ --------
    T1_PK                          UNUSABLE
    
    SQL> alter index t1_pk rebuild;
    alter index t1_pk rebuild
    *
    ERROR at line 1:
    ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
    
    
    SQL> alter table t1 drop constraint t1_pk;
    
    Table altered.
    
    SQL> select index_name, status from user_indexes where table_name = 'T1';
    
    no rows selected
    
    
    SQL> alter table t1 add constraint t1_pk primary key(id) validate exceptions into exceptions;
    alter table t1 add constraint t1_pk primary key(id) validate exceptions into exceptions
                                  *
    ERROR at line 1:
    ORA-02437: cannot validate (D174_1.T1_PK) - primary key violated
    
    
    SQL> select * from exceptions;
    
    ROW_ID             OWNER      TABLE_NAME                     CONSTRAINT
    ------------------ ---------- ------------------------------ ------------
    AACokNAC3AAAHvMAAC D174_1     T1                             T1_PK
    AACokNAC3AAAHvMAAB D174_1     T1                             T1_PK
    
    SQL> delete from t1 where rowid = 'AACokNAC3AAAHvMAAC';
    
    1 row deleted.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> alter table t1 add constraint t1_pk primary key(id) validate exceptions into exceptions;
    
    Table altered.
    For those how want to test I include here the c.ctl controle file
    LOAD DATA  
    INFILE *  
     REPLACE 
     INTO TABLE T1  
     (  
      id position(1:1) TERMINATED BY "," 
    )  
     BEGINDATA  
    1;  
    2;  
    2;  
    3;  
    Best regards
    Mohamed Houri
    www.hourim.wordpress.com

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points