5 Replies Latest reply on Jul 30, 2019 8:09 AM by 3125660

    How can append constant value for fields

    3125660

      HI friends,

      first time using sql loader

      i have csv file with many filelds delimeted by ;

      but in table emp_dept we have specific column hinum that don;t have in csv file and want to allocate value =1

      load data

      CHARACTERSET UTF8

      infile 'f:\import.csv'

      APPEND into table  emp_dept

      fields terminated by ";" OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

      (

      hinum ":1",

      dummy1  FILLER,

      dummy2  FILLER,

      dummy3 FILLER,

      dummy4 FILLER,

      dummy5 FILLER,

      FIRSTNAME,

      LASTNAME,

      LECAPNAME,

      dummy6 FILLER,

      dummy7 FILLER,

      dummy8 FILLER,

      dummy9 FILLER,

      dummy10 FILLER,

      NUMBER,

      dummy11 FILLER,

      dummy12 FILLER

              )

      file format are below

      xxx;xxx;xxx;xxx;xxx;А Н;ELIZ;ELIZ;342;5;xxx;xxx;SUX;2000014298;2;IYT

      xxx;xxx;xxx;xxx;xxx;К А;OKUL;К А О;342;5;xxx;xxx;SUX;2000011130;3;TYI

       

       

      I would appreciate for help, could you please so kind tel me how i can assign =1 value to hinum fileds

       

      Many Thanks

       

      Message was edited by: 3125660

        • 1. Re: How can append constant value for fields
          3125660

          in logs

           

          Table emp_dept, loaded from every logical record.

          Insert option in effect for this table: APPEND

          TRAILING NULLCOLS option in effect

           

           

             Column Name                  Position   Len  Term Encl Datatype

          ------------------------------ ---------- ----- ---- ---- ---------------------

          hinum                           FIRST     *   ;  O(") CHARACTER           

              SQL string for column : ":1"

          DUMMY1                               NEXT     *   ;  O(") CHARACTER           

            (FILLER FIELD)

          DUMMY2                               NEXT     *   ;  O(") CHARACTER           

            (FILLER FIELD)

          DUMMY3                               NEXT     *   ;  O(") CHARACTER           

            (FILLER FIELD)

          DUMMY4                               NEXT     *   ;  O(") CHARACTER           

            (FILLER FIELD)

          DUMMY5                               NEXT     *   ;  O(") CHARACTER           

            (FILLER FIELD)

          FIRSTNAME                            NEXT     *   ;  O(") CHARACTER           

          LASTNAME                             NEXT     *   ;  O(") CHARACTER           

          CHECKNAME                            NEXT     *   ;  O(") CHARACTER           

          DUMMY6                               NEXT     *   ;  O(") CHARACTER           

            (FILLER FIELD)

          DUMMY7                               NEXT     *   ;  O(") CHARACTER           

            (FILLER FIELD)

          DUMMY8                               NEXT     *   ;  O(") CHARACTER           

            (FILLER FIELD)

          DUMMY9                               NEXT     *   ;  O(") CHARACTER           

            (FILLER FIELD)

          DUMMY10                              NEXT     *   ;  O(") CHARACTER           

            (FILLER FIELD)

          OBJECTNUMBER                         NEXT     *   ;  O(") CHARACTER           

          DUMMY11                              NEXT     *   ;  O(") CHARACTER           

            (FILLER FIELD)

           

           

          Record 1: Rejected - Error on table emp_dept.

          ORA-01008: not all variables bound

           

           

          Record 2: Rejected - Error on table emp_dept.

          ORA-01008: not all variables bound

           

           

          Record 3: Rejected - Error on table emp_dept.

          ORA-01008: not all variables bound

           

           

          Record 4: Rejected - Error on table emp_dept.

          ORA-01008: not all variables bound

           

           

          Record 5: Rejected - Error on table emp_dept.

          ORA-01008: not all variables bound

           

           

          Record 6: Rejected - Error on table emp_dept.

          ORA-01008: not all variables bound

           

           

          Record 7: Rejected - Error on table emp_dept.

          ORA-01008: not all variables bound

           

           

          Record 8: Rejected - Error on table emp_dept.

          ORA-01008: not all variables bound

           

           

          Record 9: Rejected - Error on table emp_dept.

          ORA-01008: not all variables bound

           

           

           

           

          Table EMPLOYEE:

            0 Rows successfully loaded.

            9 Rows not loaded due to data errors.

            0 Rows not loaded because all WHEN clauses were failed.

            0 Rows not loaded because all fields were null.

           

           

           

           

          Space allocated for bind array:                  82560 bytes(64 rows)

          Read   buffer bytes: 1048576

           

           

          Total logical records skipped:          0

          Total logical records read:             9

          Total logical records rejected:         9

          Total logical records discarded:        0

           

           

          Run began on Tue Jul 30 08:48:23 2019

          Run ended on Tue Jul 30 08:48:24 2019

           

           

          Elapsed time was:     00:00:00.05

          CPU time was:         00:00:00.03

          • 2. Re: How can append constant value for fields
            KayK

            Hi 31,

            have you tried it without the : ?

            A simple   hinum "1",    may work.

            regards

            Kay

            1 person found this helpful
            • 3. Re: How can append constant value for fields
              3125660

              Dear, Kay!

               

              now it's work Thank you very much!!!

              • 4. Re: How can append constant value for fields
                KayK

                btw. number is not a good name for column or is it only a typo ?

                • 5. Re: How can append constant value for fields
                  3125660

                  actually its numbers not number, my mistake Thanks !!!!!!!