?
Solved

Exceptions

Posted on 2005-02-27
5
Medium Priority
?
1,130 Views
Last Modified: 2013-12-01
I am trying to write a piece of code
to insert a new item in an object table

I need to use a for loop
and an exception called dup_val... which makes sure that duplicate field are not entered in the tabel
can anyone give me an example where you can check each record with the for loop before inserting a new record which is not duplicating the primary key.

thanks
0
Comment
Question by:cassiusduke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 11

Accepted Solution

by:
sujit_kumar earned 252 total points
ID: 13417223
Here is one example of using DUP_VAL_ON_INDEX,

create table tab_num (n number);

create unique index ind_tab_num on tab_num (n);

insert into tab_num values (3);

insert into tab_num values (10);

insert into tab_num values (22);

begin
    for i in 1..100 loop
      begin
          insert into tab_num values (i);
      exception
          when DUP_VAL_ON_INDEX then
               dbms_output.put_line('Duplicate Record found while inserting '||i);
      end;
   end loop;
end;

Output:

Duplicate Record found while inserting 3
Duplicate Record found while inserting 10
Duplicate Record found while inserting 22

Sujit
0
 

Author Comment

by:cassiusduke
ID: 13420468
thanks for for the suggestion, but I need it to prevent/stop the insert procedure if when performing a check on the existing record any of the exixting primary key matches the one which is about to be inserted?
excuse if I don't know if i am making myself clear?  

my code looks like this

create or replace type body Wet_Ride_objtyp
as
MEMBER PROCEDURE Insert_Wet_Ride(
    p_attraction_id NUMBER,
    p_Attraction_Name VARCHAR2,
    p_cost NUMBER,
    p_year_open number,
    p_height VARCHAR2,
    p_capacity NUMBER,
    p_Category varchar2 ,
    p_name varchar2,
    p_year number,
    p_wetfactor number,
    p_durations varchar2)
is
v_expenditure number;
Too_expensive exception;
budget_limit_reached exception;
e_invalid_year exception;

begin

   updateExpenditure
   (p_Year number, p_value number, p_current_expenditure number
)return number;

  if p_current_expenditure >= 300000
   then raise budget_limit_reached;

   else
   select current_expenditure
   into v_expenditure
   from Budget_objtab
   where year = p_year;
   end if;

  IF SQL%NOTFOUND THEN
  Raise e_invalid_year;
else

  SELECT COUNT(*)
  INTO v_count
  FROM Attraction_objtab;
 
  FOR i IN 1..v_count
   LOOP
  if attraction_id = p_attraction_id
  then raise Dup_on_val_index;
  else if p_cost >=100000 then
  raise Too_expensive;
else
insert into Attraction_objtab values
    (p_attraction_id,
    p_Attraction_Name,
    p_cost,
    p_dateopen,
    p_height,
    p_capacity,
    p_Category,
    p_name,
    p_year,
    p_wetfactor,
    p_durations);
    end if;
   
  If v_expenditure + p_cost >=300000 then
  raise budget_limit_reached;
  end if;
end if;
end loop;
  rollback;

  Exception
 when e_invalid_year then
 DBMS_output.Put_line('please enter a valid year');
 When
 Too_expensive then
 DBMS_output.put_line('');
 
 When
 budget_limit_reached then
 DBMS_output.Put_line('Cannot afford Attraction, budget limit reached');
 
 When
 Dup_val_on_index then
 DBMS_output.Put_line('Cannot afford Attraction, budget limit');
 When others then
DBMS_output.Put_line('Cannot afford Attraction, budget limit ');
end Insert_Wet_Ride;
end;
/
show errors
0
 
LVL 1

Assisted Solution

by:lookingforjava
lookingforjava earned 248 total points
ID: 13460019
Trapping for a DUP_VAL_ON_INDEX exception will stop the insert procedure.  You just need to place the exception block outside of the insertion loop.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This exercise is about for the following scenario: Dmgr and One node with 2 application server. Each application server contains it owns application. Application server name as follows server1 contains app1 server2 contains app1 Prereq…
Most of the developers using Tomcat find it easy to configure the datasource in Server.xml and use the JNDI name in the code to get the connection.  So the default connection pool using DBCP (or any other framework) is made available and the life go…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses
Course of the Month13 days, 23 hours left to enroll

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question