10 Replies Latest reply: Feb 5, 2013 4:27 AM by stefan nebesnak RSS

    Sql loader function attachment....

    Muthu
      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_
          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
            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_
              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
                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
                  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
                    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
                      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
                        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
                          [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
                            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')