Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Inserting data after validation

User_4LC0GJun 14 2021 — edited Jun 14 2021

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

Comments

Saubhik

By looking at your first cursor it seems you are inserting those rows which are not in the target table, You can do it more efficiently by using MERGE statement by using WHEN NOT MATCHED condition only.
To process in chunks parallelly you might look for dbms_parallel_execute. There are examples in this forum.

User_4LC0G

Thanks, but tab2 will be empty. Hence, I only need to insert data into tab2.

Saubhik

Then what is condition is doing ?

where not exists (select 1 
                      from tab2 b
                    where a.item = b.item)
BluShadow

What does val_fun actually do?
If this is about performance, I would avoid using PL altogether, especially loops of bulk collecting and inserting, and instead just use a straight SQL INSERT or MERGE statement, and have the validation take place as part of the SQL code without calling any PL code, to avoid context switching.
Without example data to use and without knowing what val_fun actually does it's hard to actually advise fully.

1 person found this helpful
User_4LC0G

Function : fun1 is called from a Shell script as a batch. For any reason if there is a batch failure, then the above query prevents duplication in the target table.

User_4LC0G

undefined (0 Bytes)Function : fun_val contains statements like

create or replace function fun_val (I_item number,
                                    O_flag varchar2)
return boolean

is

  L_dummy varchar2(1); 

cursor c_val1
is
  select 'X'
   from <some_table>
   where item = I_item;

cursor c_val2
is
  select 'X'
   from <some_table>
    where item = I_item; 

begin
     O_flag := 'N';   

    open c_val1;
    fetch c_val1 into L_dummy;
     if c_val1%FOUND
     then
         O_flag := 'Y';
         return true;
     end if;
    close c_val1;


open c_val2;
    fetch c_val2 into L_dummy;
     if c_val2%FOUND
     then
         O_flag := 'Y';
         return true;
     end if;
    close c_val2;

exception
<WHEN OTHERS handling>

end fun_val;
User_H3J7U

Function : fun_val contains statements like
This is just EXISTS. Your task is easier to solve with single insert without a lot of cursors, loops and ifs.

BluShadow

You can either use EXISTS in SQL for these, or use LEFT OUTER JOINS and test if the key value of the target table is null to test for existence. No need for PL code and further cursors for this as they will just degrade performance.

1 - 8

Post Details

Added on Jun 14 2021
#function, #plsql
8 comments
1,348 views