Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: what is the the best approach for...?

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

From: Giovanni Azua <bravegag_at_hotmail.com>
Date: Wed, 13 Nov 2002 16:57:47 +0100
Message-ID: <aqtsrd$dedjj$1@ID-114658.news.dfncis.de>


Hello,

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

Because the FORALL statement can only
have one DDL statement INSERT, UPDATE, MERGE DELETE but not a BEGIN etc.

Thanks anyway,
Best Regards,
Giovanni

> ie.
> LOOP
> BEGIN
> INSERT STATEMENT ....
> COMMIT;
> EXCEPTION
> WHEN DUPLICATE_VALUE_ON_INDEX THEN
> NULL;
> WHEN OTHERS THEN
> RAISE;
> END
> END LOOP;
>
>
> 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 - 09:57:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US