Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Exception Handling for inserts

832302Feb 6 2012 — edited Feb 7 2012
Hi,

My requirement is to populate a table with values got by selecting columns from various table on join conditions. It works fine if all the rows inserted are unique. However if the row to be inserted is duplicate, it violates the uniqueness constraint.

I want an exception wherein if select query returns 100 rows, of which 80 are already there in the table to be populated, it should just insert the 20 records.

Below is the SP i wrote for the same. However, as soon as it meets exception condition, it just prints the condition and exits, without processing the rest of the records. Please look at the SP below and suggest a solution.
------------------------------------------------------------------------------------------------------------------------------------------------------------
create or replace
PROCEDURE PP_CMC_TEST AS 
cursor c1 is 
(select cdu.subscription_id,max(cdu.account_id),max(s.subscription_versn_start_date),
max(s.service_plan_id),max(sbp.billing_period_id),sysdate-1
,cdu.device_name, cdu.resource_id,sum(cdu.usage),max(cdu.unit_of_measurement)
from
subscriptions s, subscription_billing_period sbp, consolidated_daily_usage cdu
where s.version_end_date is null and
sysdate-1 between sbp.start_date and sbp.end_date and
cdu.usage_date between sbp.start_date and sbp.end_date
and s.subscription_id = cdu.subscription_id
and sbp.subscription_id = cdu.subscription_id 
and sbp.subscription_id = s.subscription_id 
and s.subscription_versn_start_date=sbp.subscription_versn_start_date
group by cdu.subscription_id,cdu.device_name, cdu.resource_id);
a number;
b number;
c date;
d number;
e number;
f date;
g varchar2 (255);
h number;
i number;
j varchar2(60);
BEGIN

  OPEN c1;
    LOOP
        FETCH c1 INTO a,b,c,d,e,f,g,h,i,j;
         EXIT WHEN c1%NOTFOUND;
insert into cmc_test
(subscription_id,account_id,subscription_versn_start_date,service_plan_id,billing_period_id,curr_date,
device_name,resource_id,usage,unit_of_measurement) values
(a,b,c,d,e,f,g,h,i,j);
    END LOOP;  
                             EXCEPTION
    WHEN DUP_VAL_ON_INDEX
      THEN 
      DBMS_OUTPUT.PUT_LINE('DUPLICATE RECORD FOUND');
      
      commit;
    CLOSE c1;  

END PP_CMC_TEST;
Edited by: BluShadow on 07-Feb-2012 09:03
added {noformat}
{noformat} tags (for what it was worth).  Please read {message:id=9360002} and learn to do this yourself in future.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Comments

60437
Leigh - If you can put your application on apex.oracle.com, I will try it there and see what I can figure out. What version of Application Express are you using?

Scott
482478
Scott --

Thank you for the reply...We are currently using version 3.1.1.00.09 on an Oracle 10gR2 database. I will try to post the application to the hosted environment as requested, but it will be the first time I have done so, so be prepared for further questions...or if you guys have a document outlining the how's and "gotcha" that would be good too.

Thanks,
Leigh
482478
Scott --

I'm totally confused now....it usually doesn't take much especially on a Monday morning. I was able to export the three components without errors today. We have been seeing an extraordinarily high load on the system due to other testing and development going on. I'm wondering if the load issue may be related, though I don't see exactly how that could be.

Anyhow, I've attempted to upload my application to the hosted site numerous times now. It is a large application and is timing out during the install. I removed some of the pages and security functions, but it appears I probably removed too much, since I'm getting an Oracle error when installing now.

As the problem is intermittent, even in my environment, I'm going to guess that the issue isn't in APEX, but rather something in my environment.
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 6 2012
Added on Feb 6 2012
6 comments
5,650 views