what is the the best approach for...?

Hello all,

I developed a complete Oracle solution Java Web Application deployed on J2EE with a substantial amount of complementing PL/SQL packages. The problem I am facing now is because of calling a PL/SQL method which achieves a bulking operation of adding customers to a campaign like is shown bellow (both procedures do the same but one getting the source customers from an array of IDs and the other from a filtering WHERE clause statement).

In this case I get an exception when two web users are trying to add the same
customers to the same campaign, of course using any level locking is not desirable
at all but a more flexible alternative, I would specifically like that the FORALL
ignore any duplicated key insertion as will happen in this case and react only by jumping to the next insertion candidate something like the Visual Basic "On Error Resume Next" ;-)

PROCEDURE FOGSP_ADDCAMPAIGNCUSTOMERS (

• OUT parameter opERR_DESCRT OUT VARCHAR2
• IN parameters ,ipCAMPAIGN_ID IN NUMBER ,ipCUSTOMERSID_VARRAY IN INT_VARRAY ,ipUSER_ID IN VARCHAR2 ) AS BEGIN
• Clear error description... opERR_DESCRT := ' ';
• Insert into CAMPAIGN_PERSON table... IF ipCUSTOMERSID_VARRAY.COUNT > 0 THEN FORALL indx IN ipCUSTOMERSID_VARRAY.FIRST .. ipCUSTOMERSID_VARRAY.LAST INSERT INTO CAMPAIGN_PERSON (SELECT ipCAMPAIGN_ID AS CAMPAIGN_ID, ipCUSTOMERSID_VARRAY(indx) AS PERSON_ID , 0 AS FLAG_REACTED, ipUSER_ID AS USER_ID, SYSDATE AS LAST_UPDATE FROM DUAL); END IF; END FOGSP_ADDCAMPAIGNCUSTOMERS;

• OUT parameter opERR_DESCRT OUT VARCHAR2
• IN parameters ,ipCAMPAIGN_ID IN NUMBER ,ipSEARCHCUSTOMERS_STAT IN VARCHAR2 ,ipUSER_ID IN VARCHAR2 ) AS

pvCUSTOMERSID_VARRAY INT_VARRAY;

BEGIN

• Clear error description... opERR_DESCRT := ' ';
• Execute the statement and bulk colecct it... EXECUTE IMMEDIATE 'SELECT PERSON_ID FROM FOGVW_SEARCHCUSTOMERS ' || ipSEARCHCUSTOMERS_STAT BULK COLLECT INTO pvCUSTOMERSID_VARRAY;

  IF pvCUSTOMERSID_VARRAY.COUNT > 0 THEN    FORALL indx IN pvCUSTOMERSID_VARRAY.FIRST .. pvCUSTOMERSID_VARRAY.LAST

      INSERT INTO CAMPAIGN_PERSON
    (SELECT ipCAMPAIGN_ID AS CAMPAIGN_ID, pvCUSTOMERSID_VARRAY(indx) AS PERSON_ID
          , 0 AS FLAG_REACTED, ipUSER_ID AS USER_ID, SYSDATE AS LAST_UPDATE FROM DUAL);
  END IF;
END FOGSP_ADDCAMPAIGNCUSTOMERS; TIA,
Best Regards,
Giovanni Received on Wed Nov 13 2002 - 04:57:10 CST