insert next 5 records [message #329067] |
Mon, 23 June 2008 23:57  |
rzkhan
Messages: 370 Registered: March 2005
|
Senior Member |
|
|
Hi.. I have primay key field in a table. Can I continue inserting more records and the record voilating dup_val key in error table.
In the following scenario, I want 11,12,13,14,15 to be inserted
in test table and the other records having regno (5,6,7,8,9,10) to be inserted in error table.
I am not having expereince in EXCEPTION handling.Please guide me
thanks
rzkhan
SQL> create table test(regno number(4));
Table created.
SQL> alter table test add constraint pk_test primary key(regno);
Table altered.
SQL> ed
Wrote file afiedt.buf
1 create or replace PROCEDURE ft as
2 begin
3 for i in 1..10 loop
4 insert into test values (i);
5 end loop;
6* end;
SQL> /
Procedure created.
SQL> set serveroutput on
SQL> exec ft;
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 create or replace PROCEDURE ft as
2 begin
3 for i in 5..15 loop
4 insert into test values (i);
5 end loop;
6 EXCEPTION when dup_val_on_index then
7 dbms_output.put_line('Duplicate Value Found ');
8* end;
SQL> /
Procedure created.
SQL> exec ft;
Duplicate Value on
PL/SQL procedure successfully completed.
SQL> select * from test;
REGNO
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
|
|
|
|