Skip to main content

Posts

Showing posts from June, 2019

Make the Most of PL/SQL Bulk Processing

The bulk processing features of PL/SQL (BULK COLLECT and FORALL) are key tools for improving performance of programs that currently rely on row-by-row processing, an example of which is shown below.

Use this blog post to quickly get to some of the best resources on bulk processing - from articles to quizzes to workouts to tutorials.
LiveSQL Tutorial I offer a 19-module tutorial on all things bulk processing here. I complement the explanations with lots of code to run and explore, along with:
Fill in the Blanks: partially-written code that you need to finish up, that reinforces the content of that moduleExercises: You do all the coding to solve the stated requirement (be on the lookout for copy/paste opportunities from the module to speed things up). Oracle-BASE Content You can always depend on Tim Hall to offer comprehensive coverage of SQL and PL/SQL features, with straightforward, easy-to-run code snippets to drive the points home. You'll find his coverage of bulk processing her…

How to make sure your code FAILS to compile

Huh, what?

Make sure my code fails to compile?

Why would I want to do that.

Well, suppose that you had a compute-intensive procedure that ran every hour and benefited greatly from full PL/SQL compiler optimization (level set to 3, to take advantage of subprogram inlining and everything else it does).

Next, suppose that somehow as the procedure (newly enhanced, fully tested) was being deployed to production, the optimization level was mistakenly set to 0 or 1. This would cause severe performance problems.

So in that case, wouldn't it be nice if you could build a "circuit breaker" into that procedure so that the compiler says "No go" even if the code itself compiles just fine?

I think it would be nice - and you can accomplish precisely that with the error directive of the conditional compilation feature of PL/SQL.

First, here's the code that demonstrates precisely the scenario outlined above.
CREATE OR REPLACE PROCEDURE compute_intensive AUTHID DEFINER IS BE…