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
Procedural Constraint Enforcement (Triggers) SQL has an alternative method of addressing database integrity, involving event-driven procedural code. The special procedures th

Example of Null operator - NiNo Rule If we wanted to make HIGHER_OF adhere to "NULL in, NULL out"-let's call it the NiNo rule-we would have to write something like what is sho

Tables within a Table - SQL Figure here is an exact copy of the one in the theory book and as before it is just an alternative way of representing some of the information con

Example of Shorthand for a row constraint Example: Shorthand for a row constraint ALTER TABLE EXAM_MARK ADD CONSTRAINT Mark_in_range CHECK (Mark BETWEEN 0 AND 100);

Cursor FOR Loops In most cases that need an explicit cursor, you can simplify the coding by using a cursor FOR loop rather of the OPEN, FETCH, and CLOSE statements. A cursor FO

Data Types in SQL - Interval, Boolean INTERVAL for values denoting, not intervals (!) but durations in time, such as 5 years, 3 days, 2 minutes, and so on. BOOLEAN, con

Example of NOT EXISTS in SQL Example: Use of NOT EXISTS CREATE ASSERTION Must_be_enrolled_to_take_exam_alternative1 CHECK ( NOT EXISTS (SELECT StudentId, CourseId

Using FORALL and BULK COLLECT Together You can unite the BULK COLLECT clause with the FORALL statement, in that case, the SQL engine bulk-binds column values incrementally. In

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

Using Cursor Attributes To process the SQL data manipulation statements, the SQL engine must opens an implicit cursor named SQL. This cursor's attributes (%FOUND, %NOTFOUND, %