Home » SQL & PL/SQL » SQL & PL/SQL » insert next 5 records (10g, xp)
insert next 5 records [message #329067] Mon, 23 June 2008 23:57 Go to next message
rzkhan
Messages: 345
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.
Re: insert next 5 records [message #329072 is a reply to message #329067] Tue, 24 June 2008 00:10 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
INSERT Statement, error logging clause.
You need to pre-create the logging table; the most comfortable way is using DBMS_ERRLOG.CREATE_ERROR_LOG. Follow the example in the previous link.
Previous Topic: How to pass the value to four different columns in a view
Next Topic: Locking a table before doing an Insert.....Pls help
Goto Forum:
  


Current Time: Mon Dec 05 07:02:56 CST 2016

Total time taken to generate the page: 0.10897 seconds