Forum Stats

  • 3,768,302 Users
  • 2,252,772 Discussions
  • 7,874,521 Comments

Discussions

Inserting data after validation

User_4LC0G
User_4LC0G Member Posts: 13 Green Ribbon
edited Jun 14, 2021 12:35PM in SQL & PL/SQL

Hello Experts,

Oracle DB version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64 bit Production

As part of a package-function, I am inserting data into a table.

Table tab1 is the source table while table tab2 is the target table.

Data from tab1 is being validated using function : val_fun

Table Structure:

Tab1

create table tab1 (item number(8), item_desc varchar2(100));

Sample Data (around 10,00,000 records in the table)

ITEM      ITEM_DESC
101         Shirt
102         Socks
103         Trousers

Tab2

create table tab2 (item number(8), item_desc varchar2(100), flag1 varchar2(1), flag2 varchar2(1),
create_id varchar2(30), create_time date);

Sample Data (to be inserted using function : fun1)

ITEM      ITEM_DESC    FLAG1     FLAG2      CREATE_ID         CREATE_TIME
101        Shirt         Y         Y           AAA             14-JUN-20
102        Socks         Y         Y           AAA             14-JUN-20
103       Trousers       Y         Y           AAA             14-JUN-20

Approach 1 for function : fun1

create or replace function fun1
return boolean
is

cursor c1
is
  select item
  from tab1 a
  where not exists (select 1 
                      from tab2 b
                    where a.item = b.item);

  type t_c1 is table of c1%ROWTYPE;
  L_c1 t_c1;

 app_error exception;

 L_flag varchar2(1);


begin
    open c1;
    loop
        fetch c1 bulk collect into L_c1 limit 100;
        exit when L_c1.COUNT = 0
           begin
                for i in 1..L_c1.count
                loop
                    if val_fun (L_c1(i),
                                L_flag) = TRUE -- L_flag is out parameter of function : val_fun
                    then
                        if L_flag = Y -- skip, for any other value of L_flag
                        then
                            insert into tab2
                             select item,item_desc,'Y','Y',user,sysdate
                               from tab1
                             where item = L_c1(i);
                        end if;
                    else
                        raise app_error;
                    end if;
                   commit;
                end loop;
           exception
             <handling when others / app_error>
             return false;
           end;
    end loop;

return true;


exception
  <handling WHEN OTHERS>
return false;

end fun1;

return true;


exception
  <handling WHEN OTHERS >


end fun1;


Approach 2 for fun1:


create or replace function fun1
return boolean
is


cursor c1
is
  select item,item_desc,'Y','Y',user,sysdate
  from tab1 a
  where not exists (select 1 
                      from tab2 b
                    where a.item = b.item);


  type t_c1 is table of c1%ROWTYPE;
  L_c1 t_c1;


 app_error exception;


 L_flag varchar2(1);


begin
    open c1;
    loop
        fetch c1 bulk collect into L_c1 limit 100;
        exit when L_c1.COUNT = 0
           begin
                for i in 1..L_c1.count
                loop
                    if val_fun (L_c1(i),
                                L_flag) = TRUE
                    then
                        if L_flag = Y
                        then
                            insert into tab2
                             values (L_c1.item,L_c1.item_desc,
                                    L_c1.flag1,L_c1.flag2,L_c1.create_id,
                                    L_c1.create_time);
                        end if;
                    else
                        raise app_error;
                    end if;
                   commit;
                end loop;
           exception
             <handling when others / app_error>
           end;
    end loop;


return true;


exception
  <handling WHEN OTHERS >


end fun1;

Could you please suggest the best approach with respect to code performance.

Thank You

Tagged:

Answers