Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Inserting data after validation

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
Answers
-
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.
-
Thanks, but tab2 will be empty. Hence, I only need to insert data into tab2.
-
Then what is condition is doing ?
where not exists (select 1 from tab2 b where a.item = b.item)
-
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.
-
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.
-
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;
-
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.
-
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.