So far in this series we have covered the concepts of Strong as well as Weak ref cursor. The only type of cursor which still untouched is PL/SQL SYS_REFCURSOR in Oracle Database. Which we will learn in this blog.
A well structured query, written today could save you from catastrophic events in the future. Query performance is something we all seek, but very few truly find it.
Welcome to the second tutorial in PL/SQL Bulk Collect series where we will be learning how to use BULK COLLECT clause with SELECT-INTO statement.
SELECT-INTO statement is a SQL standard query which means developer does not have much control over the execution of the statement. If we talk about query performance, we cannot go beyond an extent with SELECT-INTO statement. Let’s see how we can overcome all these shortcomings of SELECT-INTO statement.
Learn how to overcome the problem of memory exhaustion caused by bulk collect using LIMIT clause with FETCH-INTO statement in Oracle Database.
Bulk Data Processing using PL/SQL FORALL statement in Oracle Database by Manish Sharma
In the previous tutorial we learnt that there are three bound clauses which we can use with FORALL statement to optimize the DML queries. From those three bound clause we will explore the first one i.e. Lower and Upper Bound in this tutorial.
FORALL statement with Lower & Upper Bound clause is probably the easiest way of bulk data processing until we are referencing a dense collection.
So far we have learnt how to use FORALL statement with a dense collection using Lower & Upper bound clause and a sparse collection using INDICES OF bound clause. What if we want to use FORALL statement only with selected elements of the collection? Can we do that?
Oracle gave a mid-summer gift in May 1994 to all the developers when it introduced the Dynamic SQL concept in its Database version 7.1 (a.k.a. Oracle 7.1). With Oracle 7.1 it enabled developers to write the dynamic SQL through “DBMS_SQL” package.