Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2006
    Posts
    5

    Unanswered: pl/sql(help me please) insert into

    I have
    For the following instruction:
    >
    > insert into tabel1 select * from tabel2;
    >
    > If I have 1.000.000 of records which i must insert
    > from tabel2 into tabel1 and it is an error.
    > How could i detect where the error appears(at what
    > record?)
    >
    > For example:
    > Tabel1 has the records
    > emp_id
    > 1
    > 2
    > 3
    > 4
    > error(PK,NOT NULL,FK)
    > 5
    > 6
    >
    > Tabel2 has the following structure
    > emp_id number not null
    >
    > For the following instruction:
    >
    > insert into tabel1 select * from tabel2;
    >
    > It detect an error because an emp_id is NULL
    >
    > My question:
    > How could I find out that the error appears at the
    > 5th
    > record.
    >

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Which Oracle version do you use? If it is 10g rel. 2, there's a convenient way for you: error logging. Here's an example. First, create a pair of test tables:
    Code:
    SQL> create table test as select deptno, ename from emp;
    
    Table created.
    
    SQL> create table test_2 as select * From test where 1 = 2;
    
    Table created.
    Now, set some columns to NULL and create a primary key in our second test table:
    Code:
    SQL> update test set deptno = null where deptno > 20;
    
    6 rows updated.
    
    SQL> alter table test_2 add constraint pk_t2 primary key (deptno);
    
    Table altered.
    Trying to insert records for the first time:
    Code:
    SQL> insert into test_2 select * from test;
    insert into test_2 select * from test
                              *
    ERROR at line 1:
    ORA-01400: cannot insert NULL into ("SCOTT"."TEST_2"."DEPTNO")
    Of course, it didn't succeed. Now, create an error logging table:
    Code:
    SQL> execute dbms_errlog.create_error_log('TEST_2', 'ERR_TEST_2');
    
    PL/SQL procedure successfully completed.
    Try to insert records for the second time:
    Code:
    SQL> insert into test_2
      2  select * from test
      3  log errors into err_test_2
      4  reject limit 100;
    
    2 rows created.
    As you can see, it didn't fail - records that are OK are inserted, while the rest is in our error logging table. Check it and see which records aren't valid:
    Code:
    SQL> select ora_err_number$, ora_err_mesg$, deptno, ename from err_test_2;
    
    ORA_ERR_NUMBER$ ORA_ERR_MESG$                  DEPTNO     ENAME
    --------------- ------------------------------ ---------- ----------
               1400 ORA-01400: cannot insert NULL             ALLEN
                    into ("SCOTT"."TEST_2"."DEPTNO
                    ")
                  1 ORA-00001: unique constraint ( 20         JONES
                    SCOTT.PK_T2) violated
    
               1400 ORA-01400: cannot insert NULL             TIGER
                    into ("SCOTT"."TEST_2"."DEPTNO
                    ")
    
    etc.

  3. #3
    Join Date
    Oct 2006
    Posts
    5

    Hi

    I know that it is posible in Oracle 10g but the version is oracle 9i.

    Could you say to me an idea in Oracle 9i?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Why didn't you say so in the first place? You'd save me some time and effort.

    An idea? Build error logging for yourself. Create a PL/SQL procedure which would loop through records. In exception handling section insert invalid records into an error logging table.

    Or, before you start typing, wait for another opinion. You might hear something better and easier to accomplish (to save time and effort, that is).

  5. #5
    Join Date
    Oct 2006
    Posts
    5

    Please

    Do you know if there is a possibility to read from a "queue error" ?
    or
    to use dbms_apply_adm.set_dml_handle(to use another stream, LCR....)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •