3 Replies Latest reply: Nov 30, 2012 2:54 AM by BluShadow RSS

    CALL USER DIFINED FUNCTION IN SQLLDR

    OraFighter
      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
          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
            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
              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