Re: Problem while inserting a record into a table

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Fri, 10 Sep 2010 00:00:28 -0700 (PDT)
Message-ID: <20abab7d-90ae-4c1b-aa59-398c2ebea53b_at_a11g2000vbn.googlegroups.com>

On 7 Sep., 18:18, ddf <orat..._at_msn.com> wrote:
> On Sep 7, 7:44 am, Sandy80 <svarshneym..._at_gmail.com> wrote:
>
> > I am trying to insert a record in a employee table 1 based on whether
> > a record already exists in table 1. If a record for that employee
> > already exists in table 1, I need to insert the record into table 2
> > else into table 1 itself. I am trying to do it through a pl/sql db
> > procedure.
> > So the code I have for insert statement is as follows:
>
> > insert when (empno = 1234)
> >         then into table 2 (empname,empno)
> >         values (
> >         AAAA,
> >         1234
> >         )
> >         else
> >         into table 1(empname,empno)
> >         values (
> >         AAAA,
> >         1234
> >         )
> >         select * from table 1 where empno = 1234;
>
> > The insert statement works when there are no records in table 1 for
> > that employee but fails when there are no records. Can anyone help me
> > as what do I need to add/change in this query for it to work in both
> > the conditions. Any help would be appreciated!
>
> The second insert needs to be done in an exception block since you
> raise a  NO_DATA_FOUND error:
>
> SQL> create or replace procedure cond_insrt(p_empno IN varchar2)
>   2  is
>   3          v_empname emp.empname%type;
>   4  begin
>   5          select empname into v_empname from table_1 where empno =
> p_empno;
>   6
>   7          if v_empname is not null then
>   8                  insert into table_2 (empname, empno)
>   9                  values (v_empname, p_empno);
>  10          end if;
>  11  exception
>  12          when no_data_found then
>  13                  insert into table_1 (empname, empno)
>  14                  values ('AAAA', p_empno);
>  15          when others then
>  16              dbms_output.put_line('Displaying the error stack:');
>  17              dbms_output.put(dbms_utility.format_error_stack);
>  18
> dbms_output.put_line(dbms_utility.format_error_backtrace);
>  19  end;
>  20  /
>
> Procedure created.

I suggest a slightly different exception handling: since you expect only no_data_found IMHO the best way to go about other errors is to just propagate them up the call stack so they are made obvious:

exception
  when no_data_found then
    insert into table_1 (empname, empno)       values ('AAAA', p_empno);
  when others then
    raise;

Code is usually more versatile if it leaves handling of exceptions that cannot be handled reasonably at the given level to calling client code.

Kind regards

robert Received on Fri Sep 10 2010 - 02:00:28 CDT