CODEWITHSUNDEEP
sqloracle-database
DeE DEe
DeE DEe

Reputation: 335

Oracle SQL Procedure error

Bellow is my procedure.

CREATE OR REPLACE PROCEDURE "CUSTOMER_INCREMENTAL" (
    ID VARCHAR2, 
    TITLE VARCHAR2, 
    SHORT_NAME VARCHAR2, 
    ACCOUNT_OFFICER VARCHAR2, 
    LEGAL_ID VARCHAR2, 
    LOCAL_REF_23 VARCHAR2, 
    LOCAL_REF_90 VARCHAR2, 
    COUNTRY VARCHAR2, 
    BIRTH_INCORP_DATE VARCHAR2, 
    LOCAL_REF_1 VARCHAR2, 
    CUSTOMER_STATUS VARCHAR2, 
    INDUSTRY VARCHAR2, 
    LEGAL_ID_2 VARCHAR2, 
    NET_MONTHLY_IN VARCHAR2, 
    GENDER VARCHAR2, 
    MARITAL_STATUS VARCHAR2, 
    OCCUPATION VARCHAR2, 
    EMPLOYERS_NAME VARCHAR2, 
    OFF_PHONE VARCHAR2, 
    EMPLOYERS_ADD VARCHAR2, 
    NO_OF_DEPENDENTS VARCHAR2, 
    INTRODUCER VARCHAR2, 
    DATE_TIME DATE, 
    LAST_KYC_REVIEW_DATE VARCHAR2, 
    JOB_TITLE VARCHAR2, 
    EMPLOYMENT_STATUS VARCHAR2, 
    LOCAL_REF_22 VARCHAR2, 
    LOCAL_REF_99 VARCHAR2, 
    LEGAL_EXP_DATE VARCHAR2, 
    DATE_OF_BIRTH VARCHAR2, 
    NATIONALITY VARCHAR2, 
    GIVEN_NAMES VARCHAR2, 
    FAMILY_NAME VARCHAR2, 
    LOCAL_REF_87 VARCHAR2, 
    NAME_2 VARCHAR2, 
    STREET VARCHAR2, 
    ADDRESS VARCHAR2, 
    TOWN_COUNTRY VARCHAR2, 
    LOCAL_REF_20 VARCHAR2, 
    LOCAL_REF_21 VARCHAR2, 
    COUNTRY_2 VARCHAR2, 
    POST_CODE VARCHAR2, 
    PHONE_1 VARCHAR2, 
    FAX_1 VARCHAR2, 
    SMS_1 VARCHAR2, 
    EMAIL_1 VARCHAR2, 
    LOCAL_REF_66 VARCHAR2, 
    LOCAL_REF_67 VARCHAR2, 
    LOCAL_REF_68 VARCHAR2, 
    LOCAL_REF_69 VARCHAR2, 
    LOCAL_REF_70 VARCHAR2, 
    LOCAL_REF_71 VARCHAR2, 
    LOCAL_REF_81 VARCHAR2, 
    LOCAL_REF_72 VARCHAR2, 
    RELATION_CODE VARCHAR2, 
    BASEL_SG VARCHAR2, 
    LEGAL_DOC_NAME VARCHAR2, 
    LEGAL_ID_3 VARCHAR2, 
    LEGAL_ISS_AUTH VARCHAR2, 
    LEGAL_EXP_DATE_2 VARCHAR2, 
    DATE_TIME_2 VARCHAR2, 
    LOCAL_REF_4 VARCHAR2, 
    MNEMONIC VARCHAR2, 
    PEP VARCHAR2, 
    RESIDENCE VARCHAR2, 
    OTHER_NATIONALITY VARCHAR2, 
    FADCA_CUS_POB VARCHAR2,
    RUN_DATE DATE,
    TABLE_NAME VARCHAR2 
    )  IS   
BEGIN
  INSERT INTO NDB_AML_CUSTOMER 
    (ID, TITLE, SHORT_NAME, ACCOUNT_OFFICER, LEGAL_ID, LOCAL_REF_23, 
     LOCAL_REF_90, COUNTRY, BIRTH_INCORP_DATE,LOCAL_REF_1, CUSTOMER_STATUS, 
     INDUSTRY, LEGAL_ID_2, NET_MONTHLY_IN, GENDER, MARITAL_STATUS, 
     OCCUPATION, EMPLOYERS_NAME, OFF_PHONE, EMPLOYERS_ADD, NO_OF_DEPENDENTS, 
     INTRODUCER, DATE_TIME, LAST_KYC_REVIEW_DATE, JOB_TITLE, 
     EMPLOYMENT_STATUS, LOCAL_REF_22, LOCAL_REF_99, LEGAL_EXP_DATE, 
     DATE_OF_BIRTH, NATIONALITY, GIVEN_NAMES, FAMILY_NAME, LOCAL_REF_87, 
     NAME_2, STREET, ADDRESS, TOWN_COUNTRY, LOCAL_REF_20, LOCAL_REF_21, 
     COUNTRY_2, POST_CODE, PHONE_1, FAX_1, SMS_1, EMAIL_1, LOCAL_REF_66, 
     LOCAL_REF_67, LOCAL_REF_68, LOCAL_REF_69, LOCAL_REF_70, 
     LOCAL_REF_71, LOCAL_REF_81, LOCAL_REF_72, RELATION_CODE, BASEL_SG, 
     LEGAL_DOC_NAME, LEGAL_ID_3, LEGAL_ISS_AUTH, LEGAL_EXP_DATE_2, 
     DATE_TIME_2, LOCAL_REF_4, MNEMONIC, PEP, RESIDENCE, OTHER_NATIONALITY, 
     FADCA_CUS_POB)
  SELECT ID, TITLE, SHORT_NAME, ACCOUNT_OFFICER, LEGAL_ID, LOCAL_REF_23,  
         LOCAL_REF_90, COUNTRY, BIRTH_INCORP_DATE, LOCAL_REF_1, 
         CUSTOMER_STATUS, INDUSTRY, LEGAL_ID_2, NET_MONTHLY_IN, GENDER, 
         MARITAL_STATUS, OCCUPATION, EMPLOYERS_NAME, OFF_PHONE, 
         EMPLOYERS_ADD, NO_OF_DEPENDENTS, INTRODUCER, DATE_TIME, 
         LAST_KYC_REVIEW_DATE, JOB_TITLE, EMPLOYMENT_STATUS, LOCAL_REF_22,
         LOCAL_REF_99, LEGAL_EXP_DATE, DATE_OF_BIRTH, NATIONALITY, 
         GIVEN_NAMES, FAMILY_NAME, LOCAL_REF_87, NAME_2, STREET, ADDRESS, 
         TOWN_COUNTRY, LOCAL_REF_20, LOCAL_REF_21, COUNTRY_2, POST_CODE, 
         PHONE_1, FAX_1, SMS_1, EMAIL_1, LOCAL_REF_66, LOCAL_REF_67, 
         LOCAL_REF_68, LOCAL_REF_69, LOCAL_REF_70, LOCAL_REF_71, 
         LOCAL_REF_81, LOCAL_REF_72, RELATION_CODE, BASEL_SG, 
         LEGAL_DOC_NAME, LEGAL_ID_3, LEGAL_ISS_AUTH, LEGAL_EXP_DATE_2, 
         DATE_TIME_2, LOCAL_REF_4, MNEMONIC, PEP, RESIDENCE, 
         OTHER_NATIONALITY, FADCA_CUS_POB
    FROM NDB_CUSTOMER_NEW
    WHERE DATE_TIME > (SELECT RUN_DATE
                         FROM CHECK_POINT
                         WHERE TABLE_NAME = (SELECT TABLE_NAME
                                               FROM ALL_TABLES
                                               WHERE TABLE_NAME='NDB_CUSTOMER_NEW'));

  UPDATE CHECK_POINT
    SET RUN_DATE = SYSDATE;

  COMMIT;
END;
/

When I execute I'm getting

Wrong number or types of arguments

Upvotes: 0

Views: 57

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

You've declared lots of formal parameters for your procedure, but you don't ever use them. All of the values you're inserting into NDB_AML_CUSTOMER are coming from NDB_CUSTOMER_NEW.

So your procedure declaration just needs to be:

CREATE OR REPLACE PROCEDURE "CUSTOMER_INCREMENTAL"
IS
BEGIN
  INSERT INTO ...
  UPDATE ...
  COMMIT;
END;

The subquery against ALL_TABLES isn't necessary; all it can really do is check the table exists, but if it doesn't then the parser will have thrown an error before it executes that subquery. The subquery to get the RUN_DATE could just be:

SELECT RUN_DATE FROM CHECK_POINT WHERE TABLE_NAME = 'NDB_CUSTOMER_NEW'

But assuming that means you have multiple records with different run dates for each target table, your update needs to only touch that same single record:

UPDATE CHECK_POINT SET RUN_DATE = SYSDATE WHERE TABLE_NAME = 'NDB_CUSTOMER_NEW';

You also potentially have some issues with the tracking you're doing. Firstly sessions could execute this at the same time and see the same RUN_DATE value, potentially causing data duplication or constraint violations. Secondly you're leaving a (small) gap between when the time you ran the insert and the time you record when you update RUN_DATE; if records are added to NDB_CUSTOMER_NEW with DATE_TIME in that gap they will never be processed. You might want to read about for update and current of to manage that more safely.

Upvotes: 2

Related Questions