Rollback behavior - bulk bind performance improvement, PL-SQL Programming

Rollback Behavior

When a FORALL statement fails, the database changes are rolled back to an implicit savepoint marked before each of the SQL statement execution. The Changes that are made during the earlier executions are not rolled back. For illustration, assume that you create a database table that stores department numbers and the job titles, as shown below:

CREATE TABLE emp2 (deptno NUMBER(2), job VARCHAR2(15));

Next, you insert some rows into the table, as follows:

INSERT INTO emp2 VALUES(10, 'Clerk');

INSERT INTO emp2 VALUES(10, 'Clerk');

INSERT INTO emp2 VALUES(20, 'Bookkeeper'); -- 10-char job title

INSERT INTO emp2 VALUES(30, 'Analyst');

INSERT INTO emp2 VALUES(30, 'Analyst');

Then, you attempt to append the 7-character string ' (temp)' to certain job titles using the UPDATE statement which is shown below:

DECLARE

TYPE NumList IS TABLE OF NUMBER;

depts NumList := NumList(10, 20);

BEGIN

FORALL j IN depts.FIRST..depts.LAST

UPDATE emp2 SET job = job || ' (temp)'

WHERE deptno = depts(j);

-- raises a "value too large" exception

EXCEPTION

WHEN OTHERS THEN

COMMIT;

END;

Posted Date: 10/4/2012 3:29:13 AM | Location : United States







Related Discussions:- Rollback behavior - bulk bind performance improvement, Assignment Help, Ask Question on Rollback behavior - bulk bind performance improvement, Get Answer, Expert's Help, Rollback behavior - bulk bind performance improvement Discussions

Write discussion on Rollback behavior - bulk bind performance improvement
Your posts are moderated
Related Questions
Rollback Behavior When a FORALL statement fails, the database changes are rolled back to an implicit savepoint marked before each of the SQL statement execution. The Changes t

IN OUT Mode An IN OUT parameter passes initial values to the subprogram being called and return efficient values to the caller. Within the subprogram, an IN OUT parameter acts

%ROWCOUNT When its cursor or cursor variable is opened, the %ROWCOUNT is zeroed. Before the first fetch, the %ROWCOUNT yields 0. Afterward, it yields the number of rows fetche

Defining and Declaring Records To create records, you have to define a RECORD type, and then declare records of that type. You may also define RECORD types in the declarative

Cursors   To execute the multi-row query, the Oracle opens an unnamed work region which stores the processing information. The cursor names the work region, access the informa

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illus

Mutual Recursion The Subprograms are mutually recursive if they directly or indirectly call each other. In the illustration below, the Boolean functions odd & even, that dete

Advantages of PL/SQL The PL/SQL is a high-performance transaction processing, completely portable language that offers the following advantages as shown: 1) Support for SQL

CURRVAL and NEXTVAL The series is a schema object which generates the sequential numbers. Whenever you form a sequence, you can specify its primary value and an increment. T

If two relations R and S are joined, then the non matching tuples of both R and S are ignored in __________________.