Problem while inserting a record into a table

#1

hi,

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!

#2

On Sep 7, 7:44*am, Sandy80 <svarshneym... (AT) gmail (DOT) com> wrote:

Quote:

hi,

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!

Sandy80,
I may not understand your requirement clearly, but look at MERGE
statement:
http://download.oracle.com/docs/cd/B...htm#SQLRF01606
HTH
Thomas

#3

Hi Thomas,

The MERGE command seems to be restricted to using one UPDATE and a
INSERT command but my requirement is to have 2 INSERT commands.I am
not sure if we can MERGE 2 INSERT commands. Any help would be
appreciated. Thanks!

#4

On Sep 7, 7:44*am, Sandy80 <svarshneym... (AT) gmail (DOT) com> wrote:

Quote:

hi,

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_bac ktrace);
19 end;
20 /

Procedure created.

SQL>
SQL> select * From table_1;

EMPNO EMPNAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
1234 SMITHY CLERK 7902 17-DEC-80
800 20
7369 SMITH CLERK 7902 17-DEC-80
800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30
7566 JONES MANAGER 7839 02-APR-81
2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250
1400 30
7698 BLAKE MANAGER 7839 01-MAY-81
2850 30
7782 CLARK MANAGER 7839 09-JUN-81
2450 10
7788 SCOTT ANALYST 7566 09-DEC-82
3000 20
7839 KING PRESIDENT 17-NOV-81
5000 10
7844 TURNER SALESMAN 7698 08-SEP-81
1500 0 30

11 rows selected.

SQL>
SQL> select * From table_2;

no rows selected

SQL>
SQL> exec cond_insrt(1234)

PL/SQL procedure successfully completed.

SQL>
SQL> select * From table_1;

EMPNO EMPNAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
1234 SMITHY CLERK 7902 17-DEC-80
800 20
7369 SMITH CLERK 7902 17-DEC-80
800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30
7566 JONES MANAGER 7839 02-APR-81
2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250
1400 30
7698 BLAKE MANAGER 7839 01-MAY-81
2850 30
7782 CLARK MANAGER 7839 09-JUN-81
2450 10
7788 SCOTT ANALYST 7566 09-DEC-82
3000 20
7839 KING PRESIDENT 17-NOV-81
5000 10
7844 TURNER SALESMAN 7698 08-SEP-81
1500 0 30

11 rows selected.

SQL>
SQL> select * From table_2;

EMPNO EMPNAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
1234 SMITHY

SQL>
SQL> exec cond_insrt(2345);

PL/SQL procedure successfully completed.

SQL>
SQL> select * From table_1;

EMPNO EMPNAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
1234 SMITHY CLERK 7902 17-DEC-80
800 20
7369 SMITH CLERK 7902 17-DEC-80
800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30
7566 JONES MANAGER 7839 02-APR-81
2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250
1400 30
7698 BLAKE MANAGER 7839 01-MAY-81
2850 30
7782 CLARK MANAGER 7839 09-JUN-81
2450 10
7788 SCOTT ANALYST 7566 09-DEC-82
3000 20
7839 KING PRESIDENT 17-NOV-81
5000 10
7844 TURNER SALESMAN 7698 08-SEP-81
1500 0 30

EMPNO EMPNAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
2345 AAAA

12 rows selected.

SQL>
SQL> select * From table_2;

EMPNO EMPNAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
1234 SMITHY

SQL>

#5

On 7 Sep., 18:18, ddf <orat... (AT) msn (DOT) com> wrote:

Quote:

On Sep 7, 7:44*am, Sandy80 <svarshneym... (AT) gmail (DOT) 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 whereempno =
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_bac ktrace);
*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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Problem while inserting a record into a table

comp.databases.oracle.misc comp.databases.oracle.misc

Problem while inserting a record into a table - 09-07-2010 , 06:44 AM

Re: Problem while inserting a record into a table - 09-07-2010 , 08:18 AM

Re: Problem while inserting a record into a table - 09-07-2010 , 08:38 AM

Re: Problem while inserting a record into a table - 09-07-2010 , 11:18 AM

Re: Problem while inserting a record into a table - 09-10-2010 , 02:00 AM