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
Table Represents an Extension - SQL It describes how each tuple in a relation represents a true instantiation of some predicate and each true instantiation is represented by s

Parameter Aliasing   To optimize the subprogram call, the PL/SQL compiler can decide between the two techniques of the parameter passing. With the by-value techniques, the v

Implicit Cursor Attributes The Implicit cursor attributes returns the information about the execution of an INSERT, DELETE, UPDATE, or SELECT INTO statement. The cursor attribu

i NEED THE QURIES TO SOME OF THE PROBLEMS USING plsql

Using DEFAULT You can use the keyword DEFAULT rather than that of the assignment operator to initialize the variables. For e.g. the declaration blood_type CHAR := ’O’; it can b

Effects of NULL for Table Expression Here's an important distinction between expressions denoting tables and expressions denoting multisets of rows: a table expression cannot

Initializing and Referencing Collections Until you initialize a collection, a nested table or varray is automatically null (i.e. the collection itself is null, not its elements)

THEO R Y OF SPECIAL CREATION - Life originated on the earth due to natural events by the super natural power. The biblical story of creation of world within six days was p

Updating Variables For assignment, SQL uses the key word SET, as in SET X = X + 1 (read as "set X equal to X+1") rather than X: = X + 1 as found in many computer languages.

Initial thought process: Design a script which was simple and user friendly. Integrate procedures/functions to extract data under the hood. I focused on giving the user the opt