Translate

Comparing Table Structure in ORACLE

Query to Compare Table Structure(Columns/Data Types) in ORACLE

You can use below SQL Queries to compare two tables based on your requirement:

WITH 
T1 As (Select USER_TAB_COLUMNS.COLUMN_NAME, USER_TAB_COLUMNS.DATA_TYPE From USER_TAB_COLUMNS Where Table_Name='EMP_TEST'),
T2 As (Select USER_TAB_COLUMNS.COLUMN_NAME, USER_TAB_COLUMNS.DATA_TYPE From USER_TAB_COLUMNS Where Table_Name='DEPT_TEST')
Select 
  T1.COLUMN_NAME EMP, 
  T1.DATA_TYPE EMP_TYPE, 
  T2.COLUMN_NAME DEPT, 
  T2.DATA_TYPE DEPT_TYPE  
From T1 Full Join T2 
On (T1.COLUMN_NAME=T2.COLUMN_NAME);

Output:













You can also use below Query:

Select 'TABLE',Column_Name, Data_Type
From 
(
Select Column_Name, Data_Type 
From USER_TAB_COLUMNS
Where Table_Name='EMP_TEST'
Minus
Select Column_Name, Data_Type
From USER_TAB_COLUMNS
Where Table_Name='DEPT_TEST') 
Union All
Select 'DEPT',Column_Name, Data_Type 
From 
(
Select Column_Name, Data_Type
From USER_TAB_COLUMNS
Where Table_Name='DEPT_TEST'
Minus
Select Column_Name, Data_Type
From USER_TAB_COLUMNS
Where Table_Name='EMP_TEST'
);

Output:















No comments:

SYS_REFCURSOR Vs. REF CURSOR

Difference between SYS_REFCURSOR and REF CURSOR?


REF CURSOR and SYS_REFCURSOR type is used interchangeably in PL/SQL. SYS_REFCURSOR is predefined REF CURSOR defined in standard package of Oracle so that we need not to write code again and again 😊

Its declaration will be located @ %ORACLE_HOME%/rdbms/admin/stdspec.sql
For me it is: E:\app\Admin\product\11.2.0\dbhome_1\RDBMS\ADMIN



 Now, we can simply use this Type in our code and this has already been declared and ready to use.

In the same way, we create Ref Cursor Type and then declare its variables to use in our code.

/* Ref Cursor Type Declaration*/
Type My_Ref_Type Is Ref Cursor;

Following code demonstrate the difference between them where we will be using both SYS_REFCURSOR and REF CURSOR.

CREATE OR REPLACE PROCEDURE 
FETCH_EMPLOYEES_NAMES(V_DeptNo NUMBER, V_REF OUT SYS_REFCURSOR ) 
/*Note: SYS_REFCURSOR as parameter type used here 
because it has been declared in standard package it is a ref cursor */
IS
Begin
OPEN V_REF For Select FIRST_NAME, LAST_NAME From EMP_TEST where DEPTNO = V_DeptNo;
End FETCH_EMPLOYEES_NAMES;

Using Ref Cursor
We will use the above Procedure to get the names into Ref Cursor and Display output.

DECLARE
/* Ref Cursor Type Declaration */
Type MyRefCur Is Ref Cursor;
V_Fname   VARCHAR2(10);
V_Lname   VARCHAR2(10);
V_Cur MyRefCur ;/* Declare Ref Cursor Variable*/
V_Deptno NUMBER(2) := 20;
BEGIN
FETCH_EMPLOYEES_NAMES(V_Deptno,V_Cur);
DBMS_OUTPUT.PUT_LINE('FIRST_NAME' || ' ' || 'LAST_NAME');
DBMS_OUTPUT.PUT_LINE('---------------------------------');
LOOP
 FETCH V_Cur INTO V_Fname, V_Lname;
 EXIT WHEN V_Cur%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(V_Fname || '      ' || V_Lname);
END LOOP;
CLOSE V_Cur;
END;


Using SYS_REFCURSOR
We will use the same Procedure to get the names into SYS_REFCURSOR and Display output.

DECLARE
V_Fname   VARCHAR2(10);
V_Lname   VARCHAR2(10);
V_Cur SYS_REFCURSOR; /* Declare SYS_REFCURSOR Variable*/
V_Deptno NUMBER(2) := 20;
BEGIN
FETCH_EMPLOYEES_NAMES(V_Deptno,V_Cur);
DBMS_OUTPUT.PUT_LINE('FIRST_NAME' || ' ' || 'LAST_NAME');
DBMS_OUTPUT.PUT_LINE('---------------------------------');
LOOP
 FETCH V_Cur INTO V_Fname, V_Lname;
 EXIT WHEN V_Cur%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(V_Fname || '      ' || V_Lname);
END LOOP;
CLOSE V_Cur;
END;

So, SYS_REFCURSOR is just a synonym for the REF CURSOR type.  SYS_REFCURSOR used as cursor type otherwise we would have to create a cursor type and use it every time in different situations.


 Execution of the Above Codes
SQL> CREATE OR REPLACE PROCEDURE
  2  FETCH_EMPLOYEES_NAMES(V_DeptNo NUMBER, V_REF OUT SYS_REFCURSOR )
  3  /*Note: SYS_REFCURSOR as parameter type used here because it has been declared in standard package it is a ref cursor */
  4  IS
  5  Begin
  6  OPEN V_REF For Select FIRST_NAME, LAST_NAME From EMP_TEST where DEPTNO = V_DeptNo;
  7  End FETCH_EMPLOYEES_NAMES;
  8  /

Procedure created.



/*Using Ref Cursor*/

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  /* Ref Cursor Type Declaration */
  3  Type MyRefCur Is Ref Cursor;
  4  V_Fname   VARCHAR2(10);
  5  V_Lname   VARCHAR2(10);
  6  V_Cur MyRefCur ;/* Declare Ref Cursor Variable*/
  7  V_Deptno NUMBER(2) := 20;
  8  BEGIN
  9  FETCH_EMPLOYEES_NAMES(V_Deptno,V_Cur);
 10  DBMS_OUTPUT.PUT_LINE('FIRST_NAME' || ' ' || 'LAST_NAME');
 11  DBMS_OUTPUT.PUT_LINE('---------------------------------');
 12  LOOP
 13   FETCH V_Cur INTO V_Fname, V_Lname;
 14   EXIT WHEN V_Cur%NOTFOUND;
 15   DBMS_OUTPUT.PUT_LINE(V_Fname || '      ' || V_Lname);
 16  END LOOP;
 17  CLOSE V_Cur;
 18  END;
 19  /

FIRST_NAME LAST_NAME
---------------------------------
SURYA      Baluni
RAJAN      Kumar
MANU       Pillai
KARAN      Sareen

PL/SQL procedure successfully completed.



/*Using SYS_REFCURSOR*/

SQL> DECLARE
  2  V_Fname   VARCHAR2(10);
  3  V_Lname   VARCHAR2(10);
  4  V_Cur SYS_REFCURSOR; /* Declare SYS_REFCURSOR Variable*/
  5  V_Deptno NUMBER(2) := 20;
  6  BEGIN
  7  FETCH_EMPLOYEES_NAMES(V_Deptno,V_Cur);
  8  DBMS_OUTPUT.PUT_LINE('FIRST_NAME' || ' ' || 'LAST_NAME');
  9  DBMS_OUTPUT.PUT_LINE('---------------------------------');
 10  LOOP
 11   FETCH V_Cur INTO V_Fname, V_Lname;
 12   EXIT WHEN V_Cur%NOTFOUND;
 13   DBMS_OUTPUT.PUT_LINE(V_Fname || '      ' || V_Lname);
 14  END LOOP;
 15  CLOSE V_Cur;
 16  END;
 17  /

FIRST_NAME LAST_NAME
---------------------------------
SURYA      Baluni
RAJAN      Kumar
MANU       Pillai
KARAN      Sareen

PL/SQL procedure successfully completed.



7 comments:

Prevent a VALUE_ERROR Exception

If I try to use a FOR loop to iterate from FIRST to LAST and my collection is empty, PL/SQL raises a VALUE_ERROR exception. What’s the best way to avoid raising this error?

VALUE_ERROR comes whenever PL/SQL Runtime engine tries to execute a numeric For Loop and either the low and high expression in the Loop’s header evaluates to NULL. So, Raising VALUE_ERROR has nothing to do with your Collection.

It’s clear that to avoid this Exception, you must need to ensure that neither the low nor the high expression evaluates to NULL. There are several ways to avoid this error while working with Collections which we can use by checking their advantages and drawbacks.

Each approach example is an implementation of the DISPLAY_NAMES  Procedure defined in this package specification: 

PACKAGE EMPLOYEE_MGR
IS
TYPE NAMES_T IS TABLE OF EMPLOYEES.LAST_NAME%TYPE INDEX BY PLS_INTEGER;
PROCEDURE DISPLAY_NAMES  (NAMES_IN IN NAMES_T);
END EMPLOYEE_MGR;


Approach 1. Use NVL to ensure that the FOR-Loop header’s low and high expressions never return NULL. 

PROCEDURE DISPLAY_NAMES  (NAMES_IN IN NAMES_T)
IS
BEGIN

FOR Indx IN NVL (NAMES_IN.FIRST, 0) .. NVL (NAMES_IN.LAST, -1)
LOOP
   DBMS_OUTPUT.PUT_LINE(NAMES_IN(Indx));
END LOOP;

END DISPLAY_NAMES ;


Approach 2. Execute the loop only if at least one element is defined in the collection. 

PROCEDURE DISPLAY_NAMES  (NAMES_IN IN NAMES_T)
IS
BEGIN

  IF NAME_IN.COUNT > 0 THEN
    FOR Indx IN  NAMES_IN.FIRST .. NAMES_IN.LAST
    LOOP
       DBMS_OUTPUT.PUT_LINE (NAMES_IN(Indx));
    END LOOP;
  END IF;

END DISPLAY_NAMES  ;


Approach 3. Execute the FOR loop with 1 for the low value and COUNT for the high value. 

PROCEDURE DISPLAY_NAMES  (NAMES_IN IN NAMES_T)
IS
BEGIN
   
   FOR Indx IN 1 .. NAMES_IN.COUNT
   LOOP
      DBMS_OUTPUT.PUT_LINE (NAMES_IN(Indx));
   END LOOP;

END DISPLAY_NAMES ;


Approach 4. Use a WHILE loop and the FIRST and NEXT collection methods. 

PROCEDURE DISPLAY_NAMES  (NAMES_IN IN NAMES_T)
IS
  l_index PLS_INTEGER;

BEGIN
  l_index := NAMES_IN.FIRST;
  WHILE (l_index IS NOT NULL)
  LOOP
    DBMS_OUTPUT.PUT_LINE (NAMES_IN(l_index));
    l_index := NAMES_IN.NEXT(l_index);
  END LOOP;
END DISPLAY_NAMES  ;


All four approaches achieve the desired effect: VALUE_ERROR will not be raised, even if the number of elements in the NAMES_IN collection is 0. But still, the first approach should never be used and that the other three techniques should be chosen only when certain conditions are met.


When to use Approach 1?
The first approach, using NVL is hard to understand and maintain.

FOR Indx IN NVL (NAMES_IN.FIRST, 0) .. NVL (NAMES_IN.LAST, -1)

Other users have to study this code to determine what exactly the point of using NVL. SO, there is a chance of misinterpretation and then the introduction of a bug. So, instead of using this approach write code that explains itself.

When to use Approach 2?
Second approach, which uses the COUNT method to ensure that the FOR loop is executed only when there is something in the collection. Here is the relevant code: 

IF NAME_IN.COUNT > 0 THEN
    FOR Indx IN  NAMES_IN.FIRST .. NAMES_IN.LAST
    LOOP
       DBMS_OUTPUT.PUT_LINE (NAMES_IN(Indx));
    END LOOP;
  END IF;

I believe this code speaks for itself. It says: “If the collection contains at least one element, iterate from the lowest to the highest index value and take the specified action. If the collection is empty, skip the FOR loop entirely.”

But not recommended under all circumstances. The problem is that if the actual collection passed to the NAMES_IN parameter is sparse (that is, at least one index value between FIRST and LAST is not defined), the FOR loop will raise a NO_DATA_FOUND exception: 

DECLARE
NAMES_IN EMPLOYEE_MGR.NAMES_T;
BEGIN
NAMES_IN (1) := ‘Ravi’;
NAMES_IN(4) := ‘Ankit’;
NAMES_IN(6):=’Rajan’;
EMPLOYEE_MGR.DISPLAY_NAMES (NAMES_IN); 
END;

Above block will throw below Error:

DECLARE
*
ERROR at line 1:
ORA-01403: no data found

When the PL/SQL runtime engine tries to read the contents of NAMES_IN(2), it finds that there is no element defined at index value 2 and it raises NO_DATA_FOUND.

This technique useful only when you know without any doubt that the collection through which you are iterating is either empty or densely filled (all index values between FIRST and LAST are defined).
You can be sure of this whenever you populate the collection with a BULK COLLECT query or with the result of a nested table MULTISET operation (UNION, UNION ALL, INTERSECT, or EXCEPT).

When to use Approach 3?
The third approach iterates from 1 to the COUNT of elements in the collection: 

FOR Indx IN 1 .. NAMES_IN.COUNT

This technique has the advantage of avoiding the clutter of an IF statement to ensure that the FOR loop executes only when the collection is not empty. If the collection is empty, COUNT will return 0 (not NULL) and the FOR loop body will not execute, because 0 is less than 1.

But this technique assumes that the collection is either empty or densely filled. It also assumes that the lowest defined index value is 1. If you are not absolutely certain that the collection will always be filled, starting with index value 1, you should not use this technique.

When to use Approach 4?
Now, the Fourth and last approach: don’t use a FOR loop at all. Instead use a WHILE loop and the NEXT method: 

l_index := NAMES_IN.FIRST;
  WHILE (l_index IS NOT NULL)
  LOOP
    DBMS_OUTPUT.PUT_LINE (NAMES_IN(l_index));
    l_index := NAMES_IN.NEXT(l_index);
  END LOOP;


This approach makes no assumptions about the contents of the collection. The NAMES_IN collection can be empty, densely filled, or sparse, and the code will still “do the right thing.” The key to this technique’s flexibility is the use of the NEXT method. This method returns the next (highest) index value after the specified index value that is defined, ignoring all undefined index values.


1 comment:

Returning a REF CURSOR

How to Return a REF CURSOR from a Procedure (PL/SQL)?

We have already seen How to Return a Table from a Function on the below Post.
Click here to Read How To Return Table From a Function?

Now, In this Post, we will Define and Open a REF CURSOR Variable and then pass it to a Stored Procedure.

The Cursor Variable is specified as an IN OUT parameter so that the result set is made available to the caller of the Procedure. Below procedure will Take Location as an Input and return Employees working under that location.

EMPLOYEE Table Data:
SQL> SELECT * From EMP_TEST;

    EMP_NO ENAME          DEPTNO HIREDATE  WORK_LOCATION
---------- ---------- ---------- --------- --------------------
      1001 RAVI               10 19-AUG-16 HYDERABAD
      1002 SURYA              20 26-DEC-16 KOCHI
      1003 ANKIT              30 12-DEC-16 HYDERABAD
      1004 NIKHIL             40 12-DEC-10 DELHI
      1005 PRITESH            50 19-AUG-16 HYDERABAD
      1006 RAJAN              20 16-AUG-10 DELHI
      1007 MANU               20 16-AUG-10 HYDERABAD
      1008 KARAN              20 16-AUG-10 KOLKATA
      1009 GAURAV             50 19-MAR-17 HYDERABAD
      1010 SHAHRUKH           40 11-MAR-17 KOCHI
      1011 KHAN               30 11-MAR-16 HYDERABAD

Procedure Definition:
SQL> CREATE OR REPLACE PROCEDURE EMPLOYEES_BY_LOCATION
  2  (V_LOC VARCHAR2, EMP_REF_CUR IN OUT SYS_REFCURSOR)
  3  IS
  4  BEGIN
  5  OPEN EMP_REF_CUR FOR SELECT EMP_NO, ENAME, HIREDATE FROM EMP_TEST
  6  WHERE WORK_LOCATION = V_LOC;
  7  END;
  8  /

Procedure created.
The EMPLOYEES_BY_LOCATION procedure is invoked in the following anonymous block by assigning the procedure's IN OUT parameter to a cursor variable that was declared in the anonymous block's Declaration section. The Result set is fetched using this cursor variable.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2      V_EMPNO         EMP_TEST.EMP_NO%TYPE;
  3      V_ENAME         EMP_TEST.ENAME%TYPE;
  4      V_HDATE         EMP_TEST.HIREDATE%TYPE;
  5      V_WLOC          EMP_TEST.WORK_LOCATION%TYPE := 'HYDERABAD';
  6      V_EMP_REFCUR    SYS_REFCURSOR;
  7  BEGIN
  8      DBMS_OUTPUT.PUT_LINE('EMPLOYEES Working at ' || V_WLOC);
  9      DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME    HIREDATE');
 10      DBMS_OUTPUT.PUT_LINE('-----   --------  --------');
 11      EMPLOYEES_BY_LOCATION(V_WLOC,V_EMP_REFCUR);
 12      LOOP
 13          FETCH V_EMP_REFCUR INTO V_EMPNO,V_ENAME,V_HDATE;
 14          EXIT WHEN V_EMP_REFCUR%NOTFOUND;
 15          DBMS_OUTPUT.PUT_LINE(V_EMPNO||'     '||V_ENAME||'     '||V_HDATE);
 16      END LOOP;
 17      CLOSE V_EMP_REFCUR;
 18  END;

Following Output will be Displayed by Executing above PL/SQL Block.

EMPLOYEES Working at HYDERABAD
EMPNO    ENAME    HIREDATE
--------------  ---------------  
1001     RAVI     19-AUG-16
1003     ANKIT    12-DEC-16
1005     PRITESH  19-AUG-16
1007     MANU     16-AUG-10
1009     GAURAV   19-MAR-17
1011     KHAN     11-MAR-16



No comments:

Handle CONCURRENT Updates(Pessimistic and Optimistic Oracle Locking)

What is the difference between the Pessimistic and Optimistic Oracle Locking? 
How to handle Concurrent Updates on a TABLE?
Oracle allows you to choose the strategy for locking, either pessimistic or optimistic, depending on your needs.





PESSIMISTIC LOCKING:  

Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks

The developer must declare their intent to update the row set.  This is done with the SELECT xxx FOR UPDATE clause.

The only problem with this approach is The Locking and Deadlock.

Example- User A (Travel Agent) try to book last flight ticket to Delhi. He started the transaction but before clicking book the ticket, he got a phone call and talking. He got a lock on that data.

User B (Another travel agent) tries to book same ticket and wanted to make a lock to that row and he will get

ORACLE.JBO.AlreadyLockedException: JBO-26030: Failed to lock the record, another user holds the lock

The disadvantage of pessimistic locking is that a resource is locked from the time it is first accessed in a transaction until the transaction is finished, making it inaccessible to other transactions during that time.

OPTIMISTIC LOCKING:  

Optimistic Locking is a strategy where you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn’t changed before you write the record back (You re-read data and only update it if it did not change since the initial fetch)
When you write the record back you filter the update on the version to make sure it’s Atomic. (i.e. hasn’t been updated between when you check the version and write the record to the disk) and update the version in one hit.
If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it.

EXAMPLE:
User A (Travel Agent) try to book last flight ticket to Delhi. He started the transaction but before clicking book the ticket, he got a phone call and talking. Here it will not try to acquire lock.

User B (Another travel agent) tries to book same ticket and he click commit. Ticket Booked. Changes will happen in Database. 
But now user A will try to book the Ticket and he will get 

ORA.JBO.RowInconsistentException.

Which one to use
Oracle recommends using Optimistic Locking for Web Applications.
Pessimistic locking, which is the default, should not be used for Web Applications as it creates pending transnational state in the database in the form of ROW-LEVEL locks.


Let’s understand this with the help of one more example and then will understand Practically:

Basically, above methodologies used to handle multi-user issues.
How does one handle the fact that 2 people want to update the same record at the same time?

1. Do Nothing
  • User 1 Reads a Record
  • User 2 Reads the same Record
  • User 1 Updates that Record
  • User 2 Updates the same Record
User 2 has now over-written the changes that User 1 made. They are completely gone, as if they never happened. This is called a 'Lost Update'.

2. Lock the record when it is Read. Pessimistic Locking
  • User 1 reads a Record and locks it by putting an exclusive lock on the Record (FOR UPDATE clause)
  • User 2 attempts to Read and lock the same Record, but must now wait behind User 1
  • User 1 Updates the Record (And Commits)
  • User 2 can now Read the record with the changes that User 1 has made
  • User 2 Updates the Record complete with the changes from User 1

The lost update problem is solved. But the problem with this approach is Concurrency. User 1 is locking a record that they might not ever update. User 2 cannot even read the record because they want an exclusive lock when reading as well. This approach requires far too much exclusive locking, and the locks live far too long.

3. Use Optimistic Locking.

Optimistic locking does not use Exclusive Locks when Reading. Instead, a check is made during the update to make sure that the Record has not been changed since it was Read. This can be done by checking every field in the table.
i.e. UPDATE Table1 SET Col2 = x WHERE COL1=:OldCol1 AND COl2=:OldCol AND Col3=:OldCol3 AND...

There are, of course, several disadvantages to this. First, you must have already SELECTED every single column from the table.  Secondly, you must build and execute this massive statement.

Most people implement this, instead, through a single column, usually called TIMESTAMP. This column is used for no other purpose than implementing Optimistic Concurrency. It can be a Number or a Date. The idea is that it is given a value when the Row is Inserted.

Whenever the Record is Read, the Timestamp column is Read as well. When an Update is performed, the Timestamp column is checked. If it has the same value at UPDATE time as it did when it was read, then all is well, the UPDATE is performed and the timestamp is changed. If the Timestamp value is different at UPDATE time, then an error is returned to the User - they must re-read the record, re-make their changes, and try to Update the Record again.
  • User 1 Reads the Record, including the Timestamp(T1)
  • User 2 Reads the Record, including the Timestamp(T1)
  • User 1 attempts to Update the record. If the Timestamp(T1) in hand matches the Timestamp in the Database(T1), then the Update is performed and the Timestamp is Updated(T2).
  • User 2 attempts to Update the Record. The Timestamp in hand (T1) does not match the timestamp in the Database (T2), so an error is returned. User 2 must now re-read the Record, including the New Timestamp(T2) and User 1's changes, re-apply their changes and re-attempt the Update.


Optimistic Locking with CONCURRENCY In ORACLE

To Demonstrate this, consider an example- It’s the end of the year and Employees has done well. Company(Will use USER1) decides to give Employees a well-earned $300 pay rise. Meanwhile other Users(Will use USER2) are also using the Employee system, giving everybody a 5% annual salary.

USER1 Reads Salary of Employee (EMP_ID=202377) and gets Distracted

SQL> SELECT SALARY From EMPLOYEES Where EMP_ID=202377;

SALARY
--------------
3000

USER2 Reads Salary of Employee (EMP_ID=202377), make update on Salary and Commit the change.

SQL> SELECT SALARY From EMPLOYEES Where EMP_ID=202377;

SALARY
--------------
3000

SQL> UPDATE EMPLOYEES SET SALARY=3150 Where EMP_ID=202377;
1 row updated.

SQL> COMMIT;
Commit complete.

USER1 computes New salary based on his obsolete data and updates the record:

SQL> UPDATE EMPLOYEES SET SALARY=3300 Where EMP_ID=202377;
1 row updated.

SQL> COMMIT;
Commit complete.

The change made by USER2 has been wiped out:

SQL> SELECT SALARY From EMPLOYEES Where EMP_ID=202377;

SALARY
--------------
3300

Since the locking mechanism is Optimistic, No Locks are taken out during the Database Read. When USER1 gets distracted and USER2 make an Update, USER1 has no way of knowing that his data has become obsolete.

Unaware of what USER1 has done, USER2 calculates New salary on his original (and now inconsistent) data producing a result which he commits back to the database wiping out the change made by USER1. This is what is known as a “Buried Update”.
Ideally what we need is a method of allowing optimistic locking whilst ensuring concurrency.


Ensuring Data Concurrency.
The method to ensure data consistency with the use of optimistic locking is quite simple. At its most basic it involves reading a key value along with the record, and then writing that same key value back to the database when the record is updated. The database then modifies the key value on a successful write to ensure that any other current transactions that hold the same key value will not be able to modify the record.

Should any other transaction attempt to update the record with the old (and now obsolete) key value, the database will recognize the key value as obsolete and reject the update.
In Practice, there are a number of different way of achieving this, but the most common is the use of a modification Time-Stamp. Let us consider again our Employee example from earlier:
USER1 would read Employee (EMP_ID=202377) salary data from the database with a Time-Stamp, and then get distracted.

USER2 would read the same record and Update Salary. USER2 would then write back the record with the same Time-Stamp that both they and USER1 have read. The database would compare the Time-Stamps, see that they match, and accept the Update. The Database now updates the Time-Stamp to reflect the fact that the record has changed.

USER1 would then return to his task of increasing Salary for EMPLOYEE (202377) and attempt to Update the record with the original and now Obsolete Time-Stamp. The database would compare the Time-Stamps and see that they no longer match. The database would reject the Update with a Concurrency Failure and the well-written application would inform USER1 that the record he is trying to Update has been changed and must be re-read before any other Updates can be made.

USER1 re-reads the record with Time-Stamp and make the changes he wants.


IMPLEMENTATION

The Oracle database, with its Row-Level Triggers and sophisticated PL/SQL programming language provides everything we need to implement Concurrency controls for Database.

·       Using an integer for the concurrency key makes interactive SQL much simpler, and if combined with the GET_TIME function within the DBMS_UTILITY Package offers lock resolutions to 100th second

·       When a concurrency failure is encountered abort the transaction immediately using PL/SQL’s RAISE_APPLICATION_ERROR Procedure. Do not attempt to handle the error any other way - to do so is to risk inconsistent data in your database.
First, we will add our Concurrency key to the Table (In our case EMPLOYEES) as an integer column called TCN (Transaction Control Number). We then set the initial value of the TCN for each row and mark the column as Not- Null.

SQL> Alter Table EMPLOYEES Add TCN Integer;
Table altered

SQL> UPDATE EMPLOYEES Set TCN = DBMS_UTILITY.GET_TIME;
5 rows updated.

SQL> Alter Table EMPLOYEES Modify TCN Not Null;
Table altered.

We can now add the Pre-Insert and Pre-Update Triggers that will enforce concurrency with our optimistic locking strategy. The pre-insert trigger simply sets the initial value of the TCN. Note that the initial value can be any arbitrary integer value, the only purpose at this stage is to comply with the not-null requirement, but using the result of the GET_TIME function provides consistency across both triggers.

CREATE OR REPLACE Trigger EMP_PREINSERT
BEFORE INSERT ON EMPLOYEES
FOR EACH ROW
BEGIN
 /* NOTE - Additional Pre-Insert code may go here */
 /* Set the initial transaction control number */
:NEW.TCN := DBMS_UTILITY.GET_TIME;
END;

The real work is done by the Pre-Update Trigger that checks the value of the updated TCN against the one held in the database. If the two TCNs do not match the transaction is rejected.

CREATE OR REPLACE Trigger EMP_PREUPDATE
BEFORE UPDATE ON EMPLOYEES
FOR EACH ROW
BEGIN
/* Test for Concurrency Failure */
IF( :NEW.TCN != :OLD.TCN+1 ) Then
RAISE_APPLICATION_ERROR ( -20000, 'Concurrency Failure');
END IF;
/* NOTE - additional pre-update code may go here */ /* update the transaction control number */
:NEW.TCN := DBMS_UTILITY.GET_TIME;
END;

Now let’s re-run our example with USER1 and USER2 to see what happens:

USER1 Reads Salary of Employee (EMP_ID=202377) and gets Distracted

SQL> SELECT SALARY, TCN From EMPLOYEES Where EMP_ID=202377;

    SALARY        TCN
---------- --------------------
     3000    69057359

USER2 Reads Salary of Employee (EMP_ID=202377), make update on Salary and Commit the change.

SQL> SELECT SALARY, TCN From EMPLOYEES Where EMP_ID=202377;

    SALARY        TCN
---------- --------------------
     3000    69057359

SQL> UPDATE EMPLOYEES SET SALARY=3150, TCN=69057360 Where EMP_ID=202377;
1 row updated.

SQL> COMMIT;
Commit complete.

USER1 computes New salary based on his obsolete data and updates the record:

SQL> UPDATE EMPLOYEES SET SALARY=3300, TCN=69057360 Where EMP_ID=202377;
UPDATE EMPLOYEES SET SALARY=3300, TCN=69057360 Where EMP_ID=202377;
       *
ERROR at line 1:
ORA-20000: Concurrency Failure
ORA-06512: at "MYUSER. EMP_PREUPDATE ", line 4
ORA-04088: error during execution of trigger 'MYUSER. EMP_PREUPDATE '

Now, USER1 re-reads the Data of Employee (202377) and find a change

SQL> SELECT SALARY,TCN From EMPLOYEES Where EMP_ID=202377;

    SALARY        TCN
---------- --------------------
     3150    70267087

SQL> UPDATE EMPLOYEES SET SALARY=3450, TCN=70267088 Where EMP_ID=202377;
1 row updated.

SQL> COMMIT;
Commit complete.

After USER1 successfully applies his change - pre-update trigger in the database changes the TCN number again to ensure that USER2 nor anyone else can update the database with an obsolete TCN.






Source: http://www.orafaq.co
5 comments:
Subscribe to: Posts (Atom)