You Asked

Hi,

Before I get to the point this is how the record is defined:

SUBTYPE t_CIdSuf IS VARCHAR2 (100);

TYPE t_CUI_REC IS RECORD
(
uidclus A.UIDCLUS%TYPE,
uidsp B.UIDSPT%TYPE,
da C.DA%TYPE,
clusidsuf t_CIdSuf,
horizonstart DATE,
horizonstop DATE,
contract D.CONTRACT%TYPE
);

TYPE t_CUI_TBL IS TABLE OF t_CUI_REC
INDEX BY BINARY_INTEGER;

v_CUI_TBL t_CUI_TBL;

My problem is following. Cursor results are collected into v_CUI_TBL by using FETCH and BULK COLLECT. In this data the uidclus references some old data (that would need to be "closed" and the rest of the records reference the new data to be created). Due to some new requirements there is a case where there are multiple old records and only one new record. Creating a situation where the stoptiming of old records works ok, but the insert is failing due to duplicate records.

A way around this would be to use the original v_CUI_TBL to stoptime records and then update it to:
1) set uidclus to NULL for all data
2) remove duplicates
In that case the inserts would be fine as the duplicates are removed before insert.

A bit of background to avoid some questions - the query itself cannot be changed due to requirements and complexity!
I would very much like to avoid fetching the cursor twice as the query behind that is very complicated, results depend on execution time and this would definately cause a performance issue for the process in question.
I would also like to avoid any loops if anyway possible.

Regards,
Angela

and Connor said...

Is it feasible just to ignore the duplicates as they go in... Here's an example of what I mean:


SQL> drop table my_emp purge;

Table dropped.

SQL> create table my_emp as select * from scott.emp;

Table created.

SQL> alter table my_emp add primary key (empno);

Table altered.

--
-- I'm going to fetch each row from scott.emp TWICE into the plsql table
-- to mimic that fact that there are duplicates, and hence we will fail to 
-- insert them due to the primary key defined above
--

SQL>
SQL> declare
  2    type t_list is table of scott.emp%rowtype
  3      index by pls_integer;
  4
  5    l_list t_list;
  6  begin
  7    select *
  8    bulk collect into l_list
  9    from
 10      ( select * from scott.emp
 11        union all
 12        select * from scott.emp );
 13
 14    forall i in 1 .. l_list.count
 15      insert into my_emp values l_list(i);
 16  end;
 17  /
declare
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.SYS_C0017363) violated
ORA-06512: at line 14

--
-- Now we repeat using the 'save exceptions' clause to catch the dup errors
-- but then simply cycle through the captured errors and make sure that *only*
-- ora-00001 (dup error) was the error encountered.
--

SQL>
SQL>
SQL> declare
  2    type t_list is table of scott.emp%rowtype
  3      index by pls_integer;
  4
  5    l_list t_list;
  6
  7    forall_failed  EXCEPTION;
  8    PRAGMA EXCEPTION_INIT(forall_failed, -24381);
  9
 10  begin
 11    select *
 12    bulk collect into l_list
 13    from
 14      ( select * from scott.emp
 15        union all
 16        select * from scott.emp );
 17
 18    forall i in 1 .. l_list.count SAVE EXCEPTIONS
 19      insert into my_emp values l_list(i);
 20  exception
 21    when forall_failed then
 22      for i in 1..sql%bulk_exceptions.count loop
 23        if sql%bulk_exceptions(i).error_code != 1 then raise; end if;
 24      end loop;
 25  end;
 26  /

PL/SQL procedure successfully completed.

SQL> select * from my_emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      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                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 30, 2016 - 8:07 am UTC

Hi,

I need to think about this a bit, since the insertion is done by a separate procedure that is called from multiple different places.

But just from curiosity would it be possible to do it the other route and what kind of a performance impact would that hold?

Thanks,
Angela

March 31, 2016 - 3:03 am UTC

An alternative might be to create your record and the table of records as a database object type, and nested table of object types respectively.

Then you have access to use them within SQL, and hence things like:

select distinct ...
from table(my_nested_table)

and the various set operators in PLSQL , ie,

http://docs.oracle.com/database/121/LNPLS/composites.htm#LNPLS99916

would become available to you.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

Updating values and removing duplicates from RECORDs

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs