This discussion is archived
10 Replies Latest reply: Feb 5, 2013 2:27 AM by stefan nebesnak RSS

Sql loader function attachment....

Muthu Newbie
Currently Being Moderated
Hello all,

I have a doubt on sql loader....

Now i'm having like below tables..

TableA
empno|ename
1|A
2|B
3|C

Table B
salary|empno
10|1
12|2
14|3

My requirement is, in my data file instead of empno i'm having ename. Now, I need to right a function where if in datafile ename is A its should update 1 in that empno field.

Please guide me to achieve this...

I am new to technical... So i'm not confortable in writing...

I come to know that we can attach the function in to sql loader..

select statement in sql loader

Thanks and Regards,
Muthu
  • 1. Re: Sql loader function attachment....
    Karthick_Arp Guru
    Currently Being Moderated
    Why don't you consider using EXTERNAL TABLE? It would be more simple to handle such things with EXTERNAL TABLE than SQL LOADER.
  • 2. Re: Sql loader function attachment....
    Muthu Newbie
    Currently Being Moderated
    Thank you very much Karthick,

    Since i'm new to this I couldn't achieve.

    Please can you post some syntax ...?

    Thanks and Regards,
    Muthu
  • 3. Re: Sql loader function attachment....
    Karthick_Arp Guru
    Currently Being Moderated
    Muthu wrote:
    Thank you very much Karthick,

    Since i'm new to this I couldn't achieve.

    Please can you post some syntax ...?

    Thanks and Regards,
    Muthu
    I find [url http://www.orafaq.com/node/848]Oracle FAQ Natalka Roshaks blog Explaining it in a very simple way.
  • 4. Re: Sql loader function attachment....
    Muthu Newbie
    Currently Being Moderated
    Hi Karthick,

    Thank you for you guidence...

    Please tell me how to convert below command to external 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

    Thanks and Regards,
    Muthu
  • 5. Re: Sql loader function attachment....
    stefan nebesnak Journeyer
    Currently Being Moderated
    Muthu wrote:
    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

    Re: Sql loader 1: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", ....
    Try this..
    VENDOR_INTERFACE_ID expression "select VENDOR_INTERFACE_ID from AP_SUPPLIERS_INT where CUSTOMER_NUM=:CUSTOMER_NUM",
    
    VENDOR_SITE_INTERFACE_ID "ap_supplier_sites_int_s.NEXTVAL",
    --------------
    Muthu wrote:
    VENDOR_INTERFACE_ID expression "to_char('select VENDOR_INTERFACE_ID from AP_SUPPLIERS_INT where CUSTOMER_NUM=:CUSTOMER_NUM')",
    The select statement below does not return the appropriate result:
    SQL> select to_char('select VENDOR_INTERFACE_ID from AP_SUPPLIERS_INT where CUSTOMER_NUM=:CUSTOMER_NUM') result from dual;
     
    RESULT
    --------------------------------------------------------------------------------
    "select VENDOR_INTERFACE_ID from AP_SUPPLIERS_INT where CUSTOMER_NUM=:CUSTOMER_NU"
    
    See..
    
    SQL> select to_number(to_char('select VENDOR_INTERFACE_ID from AP_SUPPLIERS_INT where CUSTOMER_NUM=:CUSTOMER_NUM')) result from dual;
    
    select to_number(to_char('select VENDOR_INTERFACE_ID from AP_SUPPLIERS_INT where CUSTOMER_NUM=:CUSTOMER_NUM')) result from dual
     
    ORA-01722: invalid number
  • 6. Re: Sql loader function attachment....
    Muthu Newbie
    Currently Being Moderated
    Hello Stefan,

    Thank you for your guidence....

    I have created the below function. Its working fine. But if I attached the function in sql loader, Its giving the same error..

    SQL> create or replace function VDR_INF_F(V_NUM number)
    2 return number is
    3 VDR_INF_id AP_SUPPLIERS_INT.VENDOR_INTERFACE_ID%type;
    4 begin
    5 select VENDOR_INTERFACE_ID
    6 into VDR_INF_id
    7 from AP_SUPPLIERS_INT
    8 where CUSTOMER_NUM = V_NUM;
    9 return VDR_INF_id;
    10 end;
    11 /

    Function created.

    SQL> SELECT VDR_INF_F(1005) from dual;

    VDR_INF_F(1005)
    ---------------
    10014



    ctl file:

    load data
    infile *
    into TABLE AP_SUPPLIER_SITES_INT
    REPLACE
    fields terminated by ","
    OPTIONALLY ENCLOSED BY '"'
    (
    VENDOR_INTERFACE_ID expression "VDR_INF_F(':CUSTOMER_NUM')",
    VENDOR_SITE_INTERFACE_ID "to_number(ap_supplier_sites_int_s.NEXTVAL)",
    ...........................

    Please guide me if you find any mistake from me....

    Regards,
    Muthu
  • 7. Re: Sql loader function attachment....
    Muthu Newbie
    Currently Being Moderated
    By using last update function, I modified the control file like below.

    load data
    infile *
    into TABLE AP_SUPPLIER_SITES_INT
    REPLACE
    fields terminated by ","
    OPTIONALLY ENCLOSED BY '"'
    (
    VENDOR_INTERFACE_ID "VDR_INF_F(':CUSTOMER_NUM','9999')",
    VENDOR_SITE_INTERFACE_ID "(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
    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,,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
    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
    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

    *Now I am getting different error like below. Please look that error and give me your suggestion...*

    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 FIRST * , O(") CHARACTER
    SQL string for column : "VDR_INF_F(':CUSTOMER_NUM','9999')"
    VENDOR_SITE_INTERFACE_ID NEXT * , O(") CHARACTER
    SQL string for column : "(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 30
    Record 1: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
    ORA-06553: PLS-306: wrong number or types of arguments in call to 'VDR_INF_F'

    Record 2: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
    ORA-06553: PLS-306: wrong number or types of arguments in call to 'VDR_INF_F'

    Record 3: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
    ORA-06553: PLS-306: wrong number or types of arguments in call to 'VDR_INF_F'

    Record 4: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
    ORA-06553: PLS-306: wrong number or types of arguments in call to 'VDR_INF_F'

    Record 5: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
    ORA-06553: PLS-306: wrong number or types of arguments in call to 'VDR_INF_F'


    Thanks and Regards,
    Muthu
  • 8. Re: Sql loader function attachment....
    stefan nebesnak Journeyer
    Currently Being Moderated
    Muthu wrote:
    Hello Stefan,

    Thank you for your guidence....

    I have created the below function. Its working fine. But if I attached the function in sql loader, Its giving the same error..

    SQL> create or replace function VDR_INF_F(V_NUM number)
    return number is
    Muthu wrote:VENDOR_INTERFACE_ID "VDR_INF_F(':CUSTOMER_NUM','9999')",

    value used for ROWS parameter changed from 64 to 30
    Record 1: Rejected - Error on table "AP"."AP_SUPPLIER_SITES_INT", column VENDOR_INTERFACE_ID.
    ORA-06553: PLS-306: wrong number or types of arguments in call to 'VDR_INF_F'
    This function accept one parameter which is a number value.
    VDR_INF_F(V_NUM number)
    
    VDR_INF_F(':CUSTOMER_NUM','9999') 
    
    --> Try this:
    
    VDR_INF_F(:CUSTOMER_NUM) 
  • 9. Re: Sql loader function attachment....
    Muthu Newbie
    Currently Being Moderated
    [http://www.pafumi.net/sql_loader.htm#3|http://www.pafumi.net/sql_loader.htm#3] Hi Stefan,

    Thanks for your help...

    Now *"ORA-06553: PLS-306: wrong number or types of arguments in call to 'VDR_INF_F'"* Error is not appearing...

    But still old error is appearing after changing like below..

    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 FIRST * , O(") CHARACTER
    SQL string for column : "VDR_INF_F(TO_CHAR(':VENDOR_INTERFACE_ID','999999'))"
    VENDOR_SITE_INTERFACE_ID NEXT * , O(") CHARACTER
    SQL string for column : "(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 30
    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

    Below I gave the link which I have the sample syntax. Its similarly to my control file, even though its not working.

    http://www.pafumi.net/sql_loader.htm#3

    Please guide me if i'm missing anything...

    Thanks and Regards,
    Muthu
  • 10. Re: Sql loader function attachment....
    stefan nebesnak Journeyer
    Currently Being Moderated
    What is your SQL*Loader version?
    VDR_INF_F( V_NUM number)
    Check that the input value ( V_NUM ) contain only numbers, a sign and a decimal point and retry the operation.
    select TO_CHAR(':VENDOR_INTERFACE_ID','999999') from dual;
    ORA-01722: "invalid number"
    It should be:
     
    select TO_CHAR(:VENDOR_INTERFACE_ID,'999999') from dual;
    Try this..
    --TO_CHAR(regexp_replace (/*MY_INPUT_V_NUM*/, '[^0-9.-]+'),'999999')
    TO_CHAR(regexp_replace (:VENDOR_INTERFACE_ID, '[^0-9.-]+'),'999999')

Legend

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