what is the the best approach for...?

[Quoted] 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
[Quoted] 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 [Quoted] ,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 - 11:57:10 CET