Re: what is the the best approach for...?

why not place the insert statement into
an anonymous pl/sql block?

ie.
LOOP

  BEGIN
    INSERT STATEMENT ....
    COMMIT;
  EXCEPTION
    WHEN DUPLICATE_VALUE_ON_INDEX THEN
      NULL;
   WHEN OTHERS THEN
    RAISE;
  END

Giovanni Azua wrote:

> 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;
>
> PROCEDURE 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 - 08:14:05 CST