HomeНаука и техникаRelated VideosMore From: Practically Perfect PL/SQL with Steven Feuerstein

BLK4: Converting from Row-by-Row to Bulk Processing (PL/SQL Channel)

16 ratings | 2607 views
This lesson demonstrates the steps needed to convert from "legacy" code that still relies on row-by-row fetching of data and repeated DMl statement execution, to a FORALL- and BULK COLLECT-driven implementation. This video was taken from PLSQLChannel.com, originally recorded before Steven Feuerstein re-joined Oracle in March 2014. ======================================== Practically Perfect PL/SQL with Steven Feuerstein Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
Html code for embedding videos on your blog
Text Comments (6)
Nasim Chaklader (1 year ago)
Hi, what is purpose to use this procedure "adj_comp_for_arrays", is it to continue in case any exception on insert_history this procedure?
The purpose in the context of this call is to justify using PL/SQL and FORALL, rather than "pure" SQL. If you execute an INSERT-SELECT, and do all the business logic needed inside the SELECT statement that will be faster generally even than FORALL. But if you need to do some processing on the data that requires PL/SQL, then you bulk collect the data into arrays, do your work on the arrays, and then stuff it all back in with FORALL. Does that make sense?
Jinay Shah (2 years ago)
Hi Steven - @minute 21.56 in the video, function update_employee. in case of any update failures, you are handling an exception "bulk_error" but how do you ensure that the corresponding insert gets rolled back? You showed a way to communicate the failed employee records from insert_history function to update_employee, but it needs to be done the other way round too ? to ensure "INSERT+ADJUST+UPDATE" is one transaction like in the case of cursor for loop.
Steven Feuerstein (4 months ago)
Yep, you got me. That is not implemented in this demonstration code. I suppose you would use the key values from the failed update statement to issue individual deletes to remove them from the history table. Hope that helps!
Mercy Jhansi (4 months ago)
+Practically Perfect PL/SQL with Steven Feuerstein Steven, Won't a rollback will rollback all the inserts made? But we need to rollback only the specific inserts, for which the update has been failed, right?
Jinay, first of all, my apologies for not responding sooner. In terms of rolling back successful inserts, that's easy: just put a rollback statement in your exception handler. In addition, if you want to ONLY roll back the inserts and not any other unsaved DML in your session, either make the subprogram an autonomous transaction or issue a savepoint right before the FORALL and then rollback to that savepoint.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.