Lecture on Oracle Transactions www assignmentpoint com ORACLE

Lecture on Oracle Transactions www. assignmentpoint. com

ORACLE TRANSACTIONS A transaction begins with the first executable SQL statement after a commit, rollback or connection made to the Oracle engine. All changes made to an Oracle table data via unit a transaction are made or undone at one instance. Specifically a transaction is a group of events that occurs between any of the following. events v Connecting to Oracle. v Disconnecting from Oracle. v Committing changes to the database table v Rollback. Closing Transactions: A transaction can be closed by using either a commit or a rollback statement. By using these statements, table data can be changed or all the changes made to the table data www. assignmentpoint. com undone.

Using COMMIT: A COMMIT ends the current transaction and makes permanent any changes made during the transaction. All transactional locks acquired on tables are released. Syntax: COMMIT; Using ROLLBACK: ROLLBACK does exactly the opposite of COMMIT. It ends the transaction but. : does any changes made during the transaction. All transactional locks acquired on tables are released. syntax ROLLBACK [WORK) [TO [SAVEPOINT] savepoint] Where: WORK: is optional and is provided for ANSI compatibility. SAVEPOINT : is optional and is used to rollback a partial transaction, as far as the specified savepoint : is a savepoint created during the current transaction. www. assignmentpoint. com

Creating SAVEPOINT: SAVEPOIIVT marks and saves the current point in the processing of a transaction. When a SAVEPOINT is used with a ROLLBACK statement, parts of a transaction can be undone. An active savepoint is one that is specified since the last COMMIT or ROLLBACK. Syntax : SAVEPOINT savepointname; ROLLBACK can be fired from the SQL prompt with or without the SAVEPOINT clause. The implication of each is described below: A ROLLBACK operation performed without the SAVEPOINT clause amounts to the following: Ø Ø Ends the transaction. Undoes all the changes in the current transaction. Erases all savepoints in that transaction. Releases the transactional locks. A ROLLBACK operation performed with the TO SAVEPOINT clause amounts to the following: Ø A predetermined portion of the transaction is rolled back. Ø Retains the save point roiled back to, but loses those created after the named savepoint. Ø Releases all transactional locks that were acquired since the savepoint was taken. www. assignmentpoint. com

PROCESSING A PL/SQL BLOCK A PL/SQL block can be run in one of two modes: ØBatch processing wherein records are gathered in a table and at regular intervals manipulated. ØReal Time processing wherein records are manipulated as they are created. Batch Processing is a PL/SQL block run at the SQL prompt at regular intervals to process table data. A technique that Oracle provides for manipulating table data in batch processing mode is the use of Cursors. Oracle and the processing of SQL statements: Whenever an SQL, statement is executed, Oracle engine performs the following tasks: Ø Reserves a private SQL area in memory ØPopulates this area with the data requested in the SQL sentence. ØProcesses the data in this memory area as required. ØFrees the memory area when the processing of data is www. assignmentpoint. complete.

WHAT IS A CURSOR? The Oracle Engine uses a work area for its internal processing in order to execute an SQL statement. This work area is private to SQL's operations and is called a Cursor. The data that is stored in the cursor is called the Active Data Set. Conceptually, the size of the cursor in memory is the size required to hold the number of rows in the Active Data Set. The actual size however, is determined by the Oracle engine's built in memory management capabilities and, the amount of RAM available. Oracle has a pre-defined area in main memory set aside, within cursors are opened. Hence the cursor's size will be limited by the size of this pre-defined area. Example: When a user fires a select statement as, SELECT empno, ename, job, salary FROM employee WHERE deptno = 20; Types of Cursors: Cursors are classified depending on the circumstances under which they are I opened. if the Oracle Engine for its internal processing has opened a cursor they Implicit Cursors. A user can also open a cursor for processing data as required. Such user-defined cursors are known as explicit Cursors. are known as www. assignmentpoint. com

Explicit Cursor: When individual records in a table have to be processed inside a PLISQL code block a cursor is used. This cursor will be declared and mapped to an SQL query in the Declare Section of the PL/SQL block and used within the Executable Section. A cursor thus created and used is known as an Explicit Cursor Manaegment: The steps involved in using an explicit cursor and manipulating data in its active set are: • Declare a cursor mapped to a SQL select statement that retrieves data for processing. • Open the cursor. • Fetch data from the cursor one row at a time into memory variables. • Process the data held in the memory variables as required using a loop. • Exit from the loop after processing is complete. www. assignmentpoint. com • Close the cursor.

Cursor Declaration: A cursor is defined in the declarative part of a PLISQL block: This is done by naming the cursor and mapping it to a query. When a cursor is declared, the Oracle engine is in formed that a cursor of the said name needs to be opened. T e declaration is only an intimation. There is no memory allocation at this point in time. The three commands used to control the cursor subsequently are open. fetch and close. of Open, Fetch and Close Commands: The functionality Initialization of a cursor takes place via the open statement, this • defines a private SQL area named after the cursor name • executes a query associated with the cursor which • retrieves table data and populates the named private SQL area in memory i. e. creates the Active Data Seta • Sets the cursor row pointer in the Active Data Set to the first record. www. assignmentpoint. com