Expert
11-25. | Here is a typical use of the SAVEPOINT statement: BEGIN SAVEPOINT start_process; INSERT INTO ...; DELETE FROM ...; In this example the savepoint, start_process, is an “undeclared identifier.” It is a name that is, essentially, hard-coded into your application. How can you specify a savepoint as a variable or string literal, instead of this hard-coded value? |
11-26. | What are all the different ways you can set a savepoint in your program? |
11-27. | What are all the different ways you can roll back your transaction in PL/SQL? |
11-28. | What is wrong with the following block of code? BEGIN DBMS_STANDARD.SAVEPOINT ('start_process'); ROLLBACK TO start_process; END; |
11-29. | As a rule, the transactions in my database are short and frequent, so by default I use a set of small rollback segments. Occasionally, I need to run batch programs that:
At the start of my job, I change the rollback segment for my long-running transaction. I then commit intermittently as shown: DECLARE CURSOR my_million_row_cursor IS SELECT ...; ctr PLS_INTEGER := 1; BEGIN DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT ('big_rb'); FOR every_rec IN my_million_row_cursor LOOP make_the_changes (every_rec); IF ctr > 10000 THEN COMMIT; ctr := 1; ELSE ct := ctr + 1; END IF; END LOOP; END; Yet I still get the following error: rollback segment too small Assuming my rollback segment is big enough, what am I doing wrong? |
11-30. | What are the different ways you ... |
Get Oracle PL/SQL Programming: A Developer's Workbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.