6 Replies Latest reply: Feb 2, 2013 4:54 AM by Srini Chavali-Oracle RSS

    Sql loader 1: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", ....

    Muthu
      Hello all,

      I am getting error while running below sql loader command...


      load data
      infile *
      into TABLE AP_SUPPLIER_SITES_INT
      REPLACE
      fields terminated by ","
      OPTIONALLY ENCLOSED BY '"'
      (
      VENDOR_INTERFACE_ID expression "to_char('select VENDOR_INTERFACE_ID from AP_SUPPLIERS_INT where CUSTOMER_NUM=:CUSTOMER_NUM')",
      VENDOR_SITE_INTERFACE_ID "to_char(ap_supplier_sites_int_s.NEXTVAL)",
      LAST_UPDATE_DATE "SYSDATE",
      LAST_UPDATED_BY,
      VENDOR_SITE_CODE,
      CREATION_DATE "SYSDATE",
      CREATED_BY,
      PURCHASING_SITE_FLAG,
      PAY_SITE_FLAG,
      ADDRESS_LINE1,
      ADDRESS_LINE2,
      ADDRESS_LINE3,
      CITY,
      STATE,
      ZIP,
      COUNTRY,
      PHONE,
      FAX,
      PAYMENT_METHOD_LOOKUP_CODE,
      TERMS_ID,
      CREATE_DEBIT_MEMO_FLAG,
      HOLD_UNMATCHED_INVOICES_FLAG,
      EMAIL_ADDRESS,
      MATCH_OPTION,
      EXCLUDE_FREIGHT_FROM_DISCOUNT,
      INVOICE_CURRENCY_CODE,
      PAYMENT_CURRENCY_CODE,
      COUNTRY_OF_ORIGIN_CODE,
      FREIGHT_TERMS_LOOKUP_CODE,
      PAY_GROUP_LOOKUP_CODE,
      PAY_DATE_BASIS_LOOKUP_CODE,
      ALWAYS_TAKE_DISC_FLAG,
      ORG_ID)
      begindata
      1005,,SYSDATE,1132,Test MA Site_1,SYSDATE,1132,Y,Y,No#907 62nd Street ,10th Sector,KK Nagar,Chennai,TamilNadu,600078,IN,+91 44 33333333,044 40404040,CHECK,10000,Y,N,apps123@oracle.com,R,Y,INR,INR,IN,TBD,STANDARD,DISCOUNT,Y,120


      Log file:

      Table "AP"."AP_SUPPLIER_SITES_INT", loaded from every logical record.
      Insert option in effect for this table: REPLACE

      Column Name Position Len Term Encl Datatype
      ------------------------------ ---------- ----- ---- ---- ---------------------
      VENDOR_INTERFACE_ID EXPRESSION
      SQL string for column : "to_char('select VENDOR_INTERFACE_ID from AP_SUPPLIERS_INT where CUSTOMER_NUM=:CUSTOMER_NUM')"
      VENDOR_SITE_INTERFACE_ID FIRST * , O(") CHARACTER
      SQL string for column : "to_char(ap_supplier_sites_int_s.NEXTVAL)"
      LAST_UPDATE_DATE NEXT * , O(") CHARACTER
      SQL string for column : "SYSDATE"
      LAST_UPDATED_BY NEXT * , O(") CHARACTER
      VENDOR_SITE_CODE NEXT * , O(") CHARACTER
      CREATION_DATE NEXT * , O(") CHARACTER
      SQL string for column : "SYSDATE"
      CREATED_BY NEXT * , O(") CHARACTER
      PURCHASING_SITE_FLAG NEXT * , O(") CHARACTER
      PAY_SITE_FLAG NEXT * , O(") CHARACTER
      ADDRESS_LINE1 NEXT * , O(") CHARACTER
      ADDRESS_LINE2 NEXT * , O(") CHARACTER
      ADDRESS_LINE3 NEXT * , O(") CHARACTER
      CITY NEXT * , O(") CHARACTER
      STATE NEXT * , O(") CHARACTER
      ZIP NEXT * , O(") CHARACTER
      COUNTRY NEXT * , O(") CHARACTER
      PHONE NEXT * , O(") CHARACTER
      FAX NEXT * , O(") CHARACTER
      PAYMENT_METHOD_LOOKUP_CODE NEXT * , O(") CHARACTER
      TERMS_ID NEXT * , O(") CHARACTER
      CREATE_DEBIT_MEMO_FLAG NEXT * , O(") CHARACTER
      HOLD_UNMATCHED_INVOICES_FLAG NEXT * , O(") CHARACTER
      EMAIL_ADDRESS NEXT * , O(") CHARACTER
      MATCH_OPTION NEXT * , O(") CHARACTER
      EXCLUDE_FREIGHT_FROM_DISCOUNT NEXT * , O(") CHARACTER
      INVOICE_CURRENCY_CODE NEXT * , O(") CHARACTER
      PAYMENT_CURRENCY_CODE NEXT * , O(") CHARACTER
      COUNTRY_OF_ORIGIN_CODE NEXT * , O(") CHARACTER
      FREIGHT_TERMS_LOOKUP_CODE NEXT * , O(") CHARACTER
      PAY_GROUP_LOOKUP_CODE NEXT * , O(") CHARACTER
      PAY_DATE_BASIS_LOOKUP_CODE NEXT * , O(") CHARACTER
      ALWAYS_TAKE_DISC_FLAG NEXT * , O(") CHARACTER
      ORG_ID NEXT * , O(") CHARACTER

      value used for ROWS parameter changed from 64 to 31
      Record 1: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
      ORA-01722: invalid number

      Please guide me what could be an issue...

      Is there any other way to pass function in sql loader..

      Thanks and Regards
      Muthukumar
        • 1. Re: Sql loader 1: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", ....
          sb92075
          Muthu wrote:
          Record 1: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
          ORA-01722: invalid number

          Please guide me what could be an issue...
          Oracle found a non-numeric character that was destined for a numeric column.
          • 2. Re: Sql loader 1: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", ....
            Muthu
            Thank you for you update..

            But I gave numeric value as 1005...

            What do I need to do for this issue?

            Please guide me..

            Regards,
            Muthu
            • 3. Re: Sql loader 1: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", ....
              Muthu
              I created function like below...... retrun value as number

              create or replace function VDR_INF_F(V_NUM number)
              return number is
              VDR_INF_id AP_SUPPLIERS_INT.VENDOR_INTERFACE_ID%type;
              begin
              select VENDOR_INTERFACE_ID
              into VDR_INF_id
              from AP_SUPPLIERS_INT
              where CUSTOMER_NUM = V_NUM;
              return VDR_INF_id;
              end;
              /

              And attached that into loader program...

              load data
              infile *
              into TABLE AP_SUPPLIER_SITES_INT
              REPLACE
              fields terminated by ","
              OPTIONALLY ENCLOSED BY '"'
              (
              VENDOR_SITE_INTERFACE_ID "to_char(ap_supplier_sites_int_s.NEXTVAL)",
              LAST_UPDATE_DATE "SYSDATE",
              LAST_UPDATED_BY,
              VENDOR_SITE_CODE,
              CREATION_DATE "SYSDATE",
              CREATED_BY,
              PURCHASING_SITE_FLAG,
              PAY_SITE_FLAG,
              ADDRESS_LINE1,
              ADDRESS_LINE2,
              ADDRESS_LINE3,
              CITY,
              STATE,
              ZIP,
              COUNTRY,
              PHONE,
              FAX,
              PAYMENT_METHOD_LOOKUP_CODE,
              TERMS_ID,
              CREATE_DEBIT_MEMO_FLAG,
              HOLD_UNMATCHED_INVOICES_FLAG,
              EMAIL_ADDRESS,
              MATCH_OPTION,
              EXCLUDE_FREIGHT_FROM_DISCOUNT,
              INVOICE_CURRENCY_CODE,
              PAYMENT_CURRENCY_CODE,
              COUNTRY_OF_ORIGIN_CODE,
              FREIGHT_TERMS_LOOKUP_CODE,
              PAY_GROUP_LOOKUP_CODE,
              PAY_DATE_BASIS_LOOKUP_CODE,
              ALWAYS_TAKE_DISC_FLAG,
              ORG_ID,
              VENDOR_INTERFACE_ID expression "VDR_INF_F(':CUSTOMER_NUM')"
              )
              begindata
              1005,,SYSDATE,1132,Test MA Site_1,SYSDATE,1132,Y,Y,No#907 62nd Street ,10th Sector,KK Nagar,Chennai,TamilNadu,600078,IN,+91 44 33333333,044 40404040,CHECK,10000,Y,N,apps123@oracle.com,R,Y,INR,INR,IN,TBD,STANDARD,DISCOUNT,Y,120,1005
              1005,,SYSDATE,1132,Test MA Site_2,SYSDATE,1132,Y,Y,No#907 62nd Street ,10th Sector,KK Nagar,Chennai,TamilNadu,600078,IN,+91 44 33333333,044 40404040,CHECK,10000,Y,N,apps123@oracle.com,R,Y,INR,INR,IN,TBD,STANDARD,DISCOUNT,Y,120,1005
              1005,,SYSDATE,1132,Test MA Site_3,SYSDATE,1132,Y,Y,No#907 62nd Street ,10th Sector,KK Nagar,Chennai,TamilNadu,600078,IN,+91 44 33333333,044 40404040,CHECK,10000,Y,N,apps123@oracle.com,R,Y,INR,INR,IN,TBD,STANDARD,DISCOUNT,Y,120,1005
              1006,,SYSDATE,1132,Test1 M Site_1,SYSDATE,1132,Y,Y,No#907 62nd Street ,10th Sector,KK Nagar,Chennai,TamilNadu,600078,IN,+91 44 33333333,044 40404040,CHECK,10000,Y,N,apps123@oracle.com,R,Y,INR,INR,IN,TBD,STANDARD,DISCOUNT,Y,120,1006
              1007,,SYSDATE,1132,Test1 B Site_1,SYSDATE,1132,Y,Y,No#907 62nd Street ,10th Sector,KK Nagar,Chennai,TamilNadu,600078,IN,+91 44 33333333,044 40404040,CHECK,10000,Y,N,apps123@oracle.com,R,Y,INR,INR,IN,TBD,STANDARD,DISCOUNT,Y,120,1007

              Even now sql loader giving same error..

              like below.....

              able "AP"."AP_SUPPLIER_SITES_INT", loaded from every logical record.
              Insert option in effect for this table: REPLACE

              Column Name Position Len Term Encl Datatype
              ------------------------------ ---------- ----- ---- ---- ---------------------
              VENDOR_SITE_INTERFACE_ID FIRST * , O(") CHARACTER
              SQL string for column : "to_char(ap_supplier_sites_int_s.NEXTVAL)"
              LAST_UPDATE_DATE NEXT * , O(") CHARACTER
              SQL string for column : "SYSDATE"
              LAST_UPDATED_BY NEXT * , O(") CHARACTER
              VENDOR_SITE_CODE NEXT * , O(") CHARACTER
              CREATION_DATE NEXT * , O(") CHARACTER
              SQL string for column : "SYSDATE"
              CREATED_BY NEXT * , O(") CHARACTER
              PURCHASING_SITE_FLAG NEXT * , O(") CHARACTER
              PAY_SITE_FLAG NEXT * , O(") CHARACTER
              ADDRESS_LINE1 NEXT * , O(") CHARACTER
              ADDRESS_LINE2 NEXT * , O(") CHARACTER
              ADDRESS_LINE3 NEXT * , O(") CHARACTER
              CITY NEXT * , O(") CHARACTER
              STATE NEXT * , O(") CHARACTER
              ZIP NEXT * , O(") CHARACTER
              COUNTRY NEXT * , O(") CHARACTER
              PHONE NEXT * , O(") CHARACTER
              FAX NEXT * , O(") CHARACTER
              PAYMENT_METHOD_LOOKUP_CODE NEXT * , O(") CHARACTER
              TERMS_ID NEXT * , O(") CHARACTER
              CREATE_DEBIT_MEMO_FLAG NEXT * , O(") CHARACTER
              HOLD_UNMATCHED_INVOICES_FLAG NEXT * , O(") CHARACTER
              EMAIL_ADDRESS NEXT * , O(") CHARACTER
              MATCH_OPTION NEXT * , O(") CHARACTER
              EXCLUDE_FREIGHT_FROM_DISCOUNT NEXT * , O(") CHARACTER
              INVOICE_CURRENCY_CODE NEXT * , O(") CHARACTER
              PAYMENT_CURRENCY_CODE NEXT * , O(") CHARACTER
              COUNTRY_OF_ORIGIN_CODE NEXT * , O(") CHARACTER
              FREIGHT_TERMS_LOOKUP_CODE NEXT * , O(") CHARACTER
              PAY_GROUP_LOOKUP_CODE NEXT * , O(") CHARACTER
              PAY_DATE_BASIS_LOOKUP_CODE NEXT * , O(") CHARACTER
              ALWAYS_TAKE_DISC_FLAG NEXT * , O(") CHARACTER
              ORG_ID NEXT * , O(") CHARACTER
              VENDOR_INTERFACE_ID EXPRESSION
              SQL string for column : "VDR_INF_F(':CUSTOMER_NUM')"

              value used for ROWS parameter changed from 64 to 31
              Record 1: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
              ORA-01722: invalid number

              Record 2: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
              ORA-01722: invalid number

              Record 3: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
              ORA-01722: invalid number

              Record 4: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
              ORA-01722: invalid number

              Record 5: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
              ORA-01722: invalid number

              Please guide me, how to clear this issue?

              Thanks and Regards,
              Muthu
              • 4. Re: Sql loader 1: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", ....
                stefan nebesnak
                Muthu wrote:
                VENDOR_INTERFACE_ID expression "to_char('select VENDOR_INTERFACE_ID from AP_SUPPLIERS_INT where CUSTOMER_NUM=:CUSTOMER_NUM')",
                VENDOR_SITE_INTERFACE_ID "to_char(ap_supplier_sites_int_s.NEXTVAL)",

                value used for ROWS parameter changed from 64 to 31
                Record 1: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
                ORA-01722: invalid number
                Try using to_number() instead of to_char()
                VENDOR_INTERFACE_ID expression "to_number('select VENDOR_INTERFACE_ID from AP_SUPPLIERS_INT where CUSTOMER_NUM=:CUSTOMER_NUM')",
                
                VENDOR_SITE_INTERFACE_ID "to_number(ap_supplier_sites_int_s.NEXTVAL)",
                • 5. Re: Sql loader 1: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", ....
                  Muthu
                  Hi Stefan,

                  Thank you for your update...

                  Even after also i'm getting the same error..I herewith attached the log output FYI.

                  Table "AP"."AP_SUPPLIER_SITES_INT", loaded from every logical record.
                  Insert option in effect for this table: REPLACE

                  Column Name Position Len Term Encl Datatype
                  ------------------------------ ---------- ----- ---- ---- ---------------------
                  VENDOR_INTERFACE_ID EXPRESSION
                  SQL string for column : "to_number('select VENDOR_INTERFACE_ID from AP_SUPPLIERS_INT where CUSTOMER_NUM=&CUSTOMER_NUM')"
                  VENDOR_SITE_INTERFACE_ID FIRST * , O(") CHARACTER
                  SQL string for column : "to_number(ap_supplier_sites_int_s.NEXTVAL)"
                  LAST_UPDATE_DATE NEXT * , O(") CHARACTER
                  SQL string for column : "SYSDATE"
                  LAST_UPDATED_BY NEXT * , O(") CHARACTER
                  VENDOR_SITE_CODE NEXT * , O(") CHARACTER
                  CREATION_DATE NEXT * , O(") CHARACTER
                  SQL string for column : "SYSDATE"
                  CREATED_BY NEXT * , O(") CHARACTER
                  PURCHASING_SITE_FLAG NEXT * , O(") CHARACTER
                  PAY_SITE_FLAG NEXT * , O(") CHARACTER
                  ADDRESS_LINE1 NEXT * , O(") CHARACTER
                  ADDRESS_LINE2 NEXT * , O(") CHARACTER
                  ADDRESS_LINE3 NEXT * , O(") CHARACTER
                  CITY NEXT * , O(") CHARACTER
                  STATE NEXT * , O(") CHARACTER
                  ZIP NEXT * , O(") CHARACTER
                  COUNTRY NEXT * , O(") CHARACTER
                  PHONE NEXT * , O(") CHARACTER
                  FAX NEXT * , O(") CHARACTER
                  PAYMENT_METHOD_LOOKUP_CODE NEXT * , O(") CHARACTER
                  TERMS_ID NEXT * , O(") CHARACTER
                  CREATE_DEBIT_MEMO_FLAG NEXT * , O(") CHARACTER
                  HOLD_UNMATCHED_INVOICES_FLAG NEXT * , O(") CHARACTER
                  EMAIL_ADDRESS NEXT * , O(") CHARACTER
                  MATCH_OPTION NEXT * , O(") CHARACTER
                  EXCLUDE_FREIGHT_FROM_DISCOUNT NEXT * , O(") CHARACTER
                  INVOICE_CURRENCY_CODE NEXT * , O(") CHARACTER
                  PAYMENT_CURRENCY_CODE NEXT * , O(") CHARACTER
                  COUNTRY_OF_ORIGIN_CODE NEXT * , O(") CHARACTER
                  FREIGHT_TERMS_LOOKUP_CODE NEXT * , O(") CHARACTER
                  PAY_GROUP_LOOKUP_CODE NEXT * , O(") CHARACTER
                  PAY_DATE_BASIS_LOOKUP_CODE NEXT * , O(") CHARACTER
                  ALWAYS_TAKE_DISC_FLAG NEXT * , O(") CHARACTER
                  ORG_ID NEXT * , O(") CHARACTER

                  value used for ROWS parameter changed from 64 to 31
                  Record 1: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
                  ORA-01722: invalid number

                  Record 2: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
                  ORA-01722: invalid number

                  Record 3: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
                  ORA-01722: invalid number

                  Record 4: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
                  ORA-01722: invalid number

                  Record 5: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
                  ORA-01722: invalid number

                  Regards,
                  Muthu