This discussion is archived
6 Replies Latest reply: Feb 2, 2013 2:54 AM by Srini Chavali-Oracle RSS

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

Muthu Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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