Oracle Bulk Insert Approach!

Hi Expert,

Please give your valuable inputs on the below query.

How to validate each record based on some conditions and insert into error table if the condition fails during bulk insert into actual table (insert query is having inner join, eg:- insert into sample1(a,b,c) select x,y,z from sample2 s2,sample3 s3 where s2.col1=s3.col2). Which one is the best approach to follow here?

Thanks,
MIHIR KAR#Hadoop #Oracle_DB #UNIX beginnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
If the 'some' conditions are fixed in the database as constraints then check your documentation for the use "forall ..INSERT ... SAVE EXCEPTIONS'  in combination with  sql%bulk_exceptions.  
If the conditions are not in the database, please give some more details.
0
schwertnerCommented:
The Oracle and some other data bases have BEFORE INSERT triggers that are used to check conditions and to populate historical records i historical tables.
0
MIHIR KAR#Hadoop #Oracle_DB  #UNIX beginnerAuthor Commented:
I have created below code for test, please let me know if anything looks blockage here for production env.

declare

type thi_typ is table of bulk_tb.id%type;
v_data thi_typ := thi_typ();
v_ex_count NUMBER(5);
v_idx number(10);
abort_ex EXCEPTION;
PRAGMA EXCEPTION_INIT(abort_ex, -24381);
begin

for i in 1 .. 500 loop
v_data.extend;
v_data(v_data.last) := i;
end loop;
v_data(400) := null;
-- EXECUTE IMMEDIATE 'TRUNCATE TABLE bulk_tb';

--execute immediate 'create table bulk_his(id number)';
BEGIN
FORALL i IN v_data.FIRST .. v_data.count save exceptions
INSERT INTO bulk_tb VALUES (v_data(i));

-- insert into tbl_2(select id from tbl_2);
COMMIT;

EXCEPTION
WHEN abort_ex THEN
v_ex_count := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1..v_ex_count
LOOP
v_idx := SQL%BULK_EXCEPTIONS(i).error_index;
dbms_output.put_line('Error: ' || i || ' Array Index: ' ||v_idx
|| ' Message: ' ||
SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE)); 
insert into bulk_his values(v_idx);

end loop;
-- execute immediate 'create table bulk_his(id number)';
end;
end;

Open in new window


Thanks!
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mark GeerlingsDatabase AdministratorCommented:
1. We would likely respond to your questions here even if you did not include an exclamation mark in your question title.  Lately, it looks like you include that every time, so we are getting used to overlooking that in your questions.

2. "...the below query".  Apparently in India, the English word "below" is used as an adjective.  In the rest of the English-speaking world, the word "below" is an adverb or a proposition, but not an adjective.  So, we would say: "...the query below".  We don't put "below" between the words "the" and a noun.

3. As flow01 indicated, the Oracle documentation describes how to use the EXCEPTIONS clause with bulk inserts.

4. Are you sure this is a good idea:
for i in 1 .. 500 loop
v_data.extend;

I admit, I'm not an expert on using PL\SQL tables.  I thought that extending arrays is a relatively expensive operation in memory.

5. This line does not look correct to me:
v_data(400) := null;

After just using a loop to set the values 1-500, why explicitly set just one of the values in the array now to null?  Or, is this a test to see if 499 rows will be inserted in the next command?

6. Do you have an existing process that does some inserts that you are trying to speed up now by using a bulk insert instead?  Or, are you simply trying to learn how to use Oracle's bulk insert mechanism?
0
MIHIR KAR#Hadoop #Oracle_DB  #UNIX beginnerAuthor Commented:
1. We would likely respond to your questions here even if you did not include an exclamation mark in your question title.  Lately, it looks like you include that every time, so we are getting used to overlooking that in your questions.

2. "...the below query".  Apparently in India, the English word "below" is used as an adjective.  In the rest of the English-speaking world, the word "below" is an adverb or a proposition, but not an adjective.  So, we would say: "...the query below".  We don't put "below" between the words "the" and a noun.

Thank You once again @Mark for your valuable comment. Will take care of these mistakes.


4. Are you sure this is a good idea:
for i in 1 .. 500 loop
v_data.extend;

I admit, I'm not an expert on using PL\SQL tables.  I thought that extending arrays is a relatively expensive operation in memory.

5. This line does not look correct to me:
v_data(400) := null;

-- Ideally this is not gonna install into production, i was just test the code how it works .

6. Do you have an existing process that does some inserts that you are trying to speed up now by using a bulk insert instead?  Or, are you simply trying to learn how to use Oracle's bulk insert mechanism?

-- There 1-2 Million rows it gonna load, so thought to use bulk-collect.

Thanks.
0
slightwv (䄆 Netminder) Commented:
>>-- There 1-2 Million rows it gonna load, so thought to use bulk-collect.

Do you need to loop through those rows more than once?
Do you need to randomly access the rows from memory?

If no, you probably don't need pl/sql tables.

From what you describe, I'm not seeing the need for them.
0
MIHIR KAR#Hadoop #Oracle_DB  #UNIX beginnerAuthor Commented:
Do you need to loop through those rows more than once?

--No i don't need them to in a loop.

Do you need to randomly access the rows from memory?

--Also i don't want to put unnecessary load on run-time memory .

Could you please share if there any appropriate way to resolve this task.

Thanks.
0
slightwv (䄆 Netminder) Commented:
>>Could you please share if there any appropriate way to resolve this task.

I'm not sure what all the validations are that you need to do.

I would set up the exceptions table and perform a straight insert suggest by flow01 in the first post.

If you need more flexibility, a simple cursor loop.
for I in (select ... from ...) loop
--do you validations
end loop;

Open in new window

0
Alexander Eßer [Alex140181]Software DeveloperCommented:
You could do a bulk collect (into a collection), as mentioned above, then iterate over that in-memory collection (should be quite fast) and do your checks/validations and then do a "FORALL insert.... " with that very collection (also shown above)...
You may also remove certain entried from that collection, so that they won't get inserted at all ;-)
0
Mark GeerlingsDatabase AdministratorCommented:
If this will be a one-time task, I like the simple approach suggested by slightwv.  If this will be a recurring interface, then it may make sense to add the complexity of bulk collect, PL/ SQL tables in memory, and a FORALL insert.

Obviously, either approach needs to be tested, if it is new.  Testing and debugging is easier if the program is simpler.  For that reason, I recommend keeping the program simple, unless there is a confirmed reason to add complexity.  Don't just guess that a bulk collect and FORALL insert will be best.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Follow the KISS principle:  https://en.wikipedia.org/wiki/KISS_principle

Even if it is a recurring task you still need to ask basic questions to decide on the level of effort and complexity.

Assuming you find the bulk collect runs twice as fast (I'm betting it probably won't on 2 million rows):
What happens if the cursor loop takes 20 minutes to run and the bulk collect takes 10?

If it runs once a day will you really miss those extra 10 minutes?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.