7 Replies Latest reply on Aug 27, 2019 8:42 PM by francy77

    sqlldr with a function

    francy77

      Hi all,

      i've googled about it, but wasn't able to find a good example.

      i have a file and i need to load in a table using sqlldr, but there is a particular requirement.

       

      this is the file:

       

      CAP;Comune;SiglaProvincia

      00010;CASAPE;RM

      00010;GALLICANO NEL LAZIO;RM

      and i want to load this file in a table like this:

       

      create table cappario(

           id number primary key,

           CAP varchar2(10),

           Comune varchar2(35),

           Sigla_Provincia varchar2(2),

      comune_no_acc varchar2(45)

      );

      where id is a primary key generated by a sequence, CAP;Comune;SiglaProvincia are the field separated by a ";"  and comune_no_acc should be a function

      that takes comune as input and apply this query :

      SELECT REGEXP_REPLACE(comune, '[^0-9A-Za-z]', '')   FROM CAPPARIO; 

       

      Is there a way I can accomplish this?

       

      thanks

        • 1. Re: sqlldr with a function
          KayK

          Hi Francy,

          it will be helpfull if you post also your controlfile.

           

          Maybe something like this works

          ...
          comune_no_acc  char "REGEXP_REPLACE(:comune, '[^0-9A-Za-z]', '') "
          )
          

           

          Or you do an update on this column after the sqlldr has finished his work.
          regards

          Kay

          1 person found this helpful
          • 2. Re: sqlldr with a function
            francy77

            really thanks it works, this is my control file,

             

            OPTIONS (SKIP=1)

            LOAD DATA

            INFILE     'cappario.csv'

            BADFILE     'cappario.bad'

            DISCARDFILE 'cappario.dsc'

            INSERT INTO TABLE cappario

            FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

            (

            id sequence,

            cap,

            comune,

            sigla_provincia,

            comune_no_acc  char "REGEXP_REPLACE(:comune, '[^0-9A-Za-z]', '') "

            )

            sigla_provincia, is 2 character lenght, but if the field in the table is declared has varchar2(3) it complains that:

             

            Record 1: Rejected - Error on table CAPPARIO, column SIGLA_PROVINCIA.

            ORA-12899: value too large for column "PMUSER"."CAPPARIO"."SIGLA_PROVINCIA" (actual: 3, maximum: 2)

            i think it depends that there is a return character, how i can deal with that, i want have a sigla_provincia just 2 character long

             

            thanks, really much

            • 3. Re: sqlldr with a function
              francy77

              and (please) could you tell me also why char :

              char "REGEXP_REPLACE(:comune, '[^0-9A-Za-z]', '') "

               

              i mean what is the meaning of that word?

               

              really thanks

              • 4. Re: sqlldr with a function
                KayK

                With your data and your ctl i got 2 rows.

                < scott:op57@unxsy078 > select * from cappario;
                
                       ID CAP        COMUNE                              SI COMUNE_NO_ACC
                --------- ---------- ----------------------------------- -- -------------------
                        1 00010      CASAPE                              RM CASAPE
                        2 00010      GALLICANO NEL LAZIO                 RM GALLICANONELLAZIO
                

                 

                Maybe your csv-file contains some special characters.

                What happens if you modify the column SIGLA_PROVINCIA to varchar2(100) ?

                1 person found this helpful
                • 5. Re: sqlldr with a function
                  KayK

                  You don't need the keyword 'char' in your case. I've copied it from an old ctl-file ;-)

                  • 6. Re: sqlldr with a function
                    cormaco
                    i think it depends that there is a return character, how i can deal with that, i want have a sigla_provincia just 2 character long

                    You can change your control file like this, return only the first 2 characters of sigla_provincia:

                    sigla_provincia "SUBSTR(:sigla_provincia,1,2)",

                    1 person found this helpful
                    • 7. Re: sqlldr with a function
                      francy77

                      yes at the end there was CR LF character, I've used the same regular expression to get it off. Thanks for help