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
Declaring Records Whenever you define a RECORD type, you may declare records of that type, as the illustration shows: DECLARE TYPE StockItem IS RECORD ( item_no INTEG

Bulk Binds advantages In the Embedded Oracle RDBMS, the PL/SQL engines accept any valid PL/SQL subprogram or block. As the figure shows, the PL/SQL engine executes all procedur

Example of NOT EXISTS Operator - SQL Example is a translation into SQL of the corresponding example, which is included there merely to show that for any scalar comparison the

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

IN Mode An IN parameter pass the values to the subprogram being called. Within the subprogram, an IN parameter acts like a constant. And hence, it cannot be assigned a value.

Nested Tables versus Index-by Tables The Index-by tables and nested tables are just similar. For e.g.  They have similar structure and their individual elements are accessed in

Providing Results of Queries Expressing queries in SQL is the (big) subject. Here I present just a simple example to give you the flavour of things to come in those chapters.

ALTER TABLE bb_basketitem ADD CONSTRAINT bitems_qty_ck CHECK (quantity BEGIN INSERT INTO bb_basketitem VALUES (88,8,10.8,21,16,2,3); END; Brewbean’s wants to add a check

Declaring Subprograms   You can declare subprograms in any PL/SQL subprogram, block, or package. But, you should declare subprograms at the end of the declarative part after a

Dynamic Ranges The PL/SQL lets you determine the loop range dynamically at run time, as the example below shows: SELECT COUNT(empno) INTO emp_count FROM emp; FOR i IN 1..emp_cou