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

From: Giovanni Azua <bravegag_at_hotmail.com>
Date: Wed, 13 Nov 2002 12:01:12 +0100
Message-ID: <aqtbfa$5h25h$1_at_ID-114658.news.dfncis.de>

And by the way, the MERGE alternative is too slow to be practicable in these case where the amount of customers to add to the campaigns is more than 10^3:

• Insert/Update into CAMPAIGN_PERSON table... IF ipCUSTOMERSID_VARRAY.COUNT > 0 THEN FORALL indx IN ipCUSTOMERSID_VARRAY.FIRST .. ipCUSTOMERSID_VARRAY.LAST MERGE INTO CAMPAIGN_PERSON D USING (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) S ON (D.CAMPAIGN_ID=S.CAMPAIGN_ID AND D.PERSON_ID=S.PERSON_ID) WHEN MATCHED THEN UPDATE SET D.USER_ID=S.USER_ID, D.LAST_UPDATE=S.LAST_UPDATE WHEN NOT MATCHED THEN INSERT (D.CAMPAIGN_ID, D.PERSON_ID, D.FLAG_REACTED, D.USER_ID, D.LAST_UPDATE) VALUES (S.CAMPAIGN_ID, S.PERSON_ID, S.FLAG_REACTED, S.USER_ID, S.LAST_UPDATE); END IF;

"Giovanni Azua" <bravegag_at_hotmail.com> wrote in message news:aqtb7n$d4bdr$1_at_ID-114658.news.dfncis.de...
> 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 - 12:01:12 CET