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
Tautology - Equivalences Rules: If there Tautologies are not all the time as much easy to note as the one above so than we can use these truth tables to be definite that a sta

Keyword and Parameter Description select_statement: This is a query which returns a result set of the rows. Its syntax is such that of select_ into_statement without the IN

Pl/SQL Expressions The Expressions are constructed by using the operands and operators. An operand is a constant, literal, variable, or function call which contributes a value

In Packages The Forward declarations also group logically related subprograms in the package. The subprogram specifications go in the package specification, & the subprogram b

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

OUT Mode An OUT parameter returns values to the caller of a subprogram. Within the subprogram, an OUT parameter act like a variable. That means that you can use an OUT formal

Manipulating Objects: You can use an object type in the CREATE TABLE statement to indicate the datatype of a column. When the table is created once, you can use the SQL statem

Anatomy of a Table: Figure shows the terminology used in SQL to refer to parts of the structure of a table. As you can see, SQL has no official terms for its counterpa

Cursor Variables:   To execute the multi-row query, the Oracle opens an unnamed work region that stores the processing information. You can use an explicit cursor that names

Using Cursor Attributes: Every cursor has 4 attributes: %NOTFOUND, %FOUND, %ISOPEN, and %ROWCOUNT. If appended to the cursor name, they return the helpful information about