Posts Tagged ‘oracle’

How to avoid mutation on database tables

Posted in General, java, oracle on September 5th, 2012 by admin – Be the first to comment

What is mutation ?

Clear definition here – http://www.databasejournal.com/features/oracle/article.php/3329121/The-Mutation-error-in-Oracle-Database-Triggers.htm

When will you come across mutation?

In practice, you will come across mutation problems when you are trying to modify a table in the database while that same table is getting updated by another process.

A typical scenario

Think of sort in a table. You let the client modify the sort order for each entry on a table. To accomplish this, you will create a trigger on update, that takes the new sort value of the row that was modified and changes the entries before and after accordingly. So if you have 10 employees, each with incremental sort orders from 1 to 10, changing number 6 to number 7 will perform the following logic.

– Find the employee with a sort order of 7, change it to 6.

Notice that during this operation, you will have to update the same table inside the update trigger. This is a classic Oracle mutation issue. Usually you will get a

ORA-04091: table TRANSACTIONS is mutating, trigger/function may not see it

 

Solution

When you research online, you will find a variety of solution. Asktom’s solution will suggest that you create a custom view based on that table, and then create a trigger on that view which updates the original table. This didn’t really work for us.

Some other solutions also mentioned creating a new package etc.

What works perfectly is inside the trigger, create an autonomous context in the code. This tells oracle to  consider the chunk of code that is inside the autonomous context annotation to work in a new session from the rest of the procedure.

Consider the following for example,

CREATE OR REPLACE TRIGGER TRG_UPDATE
AFTER UPDATE ON TABLEX
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION; — This is where the magic happens
BEGIN

UPDATE TABLEX …
COMMIT;

END TRIGGER TRG_UPDATE;

Related Links and Solutions

 

http://docs.oracle.com/cd/B14117_01/appdev.101/b10807/13_elems002.htm

 

 

Weblogic Server: How to make sure the JSP’s refresh on weblogic

Posted in General on May 8th, 2012 by admin – Be the first to comment

I couldn’t get my head around this problem for a very long time. Basically there is are xml files that tells weblogic what to do in a global level, and then you can specify in application level as well.

All you have to do is find the weblogic.xml file ( if your application has one, then that is the one to change)

and look/add/modify the file so it has the following

   <jsp-descriptor>
      <jsp-param>
         <param-name>keepgenerated</param-name>
         <param-value>true</param-value>
      </jsp-param>
      <jsp-param>
         <param-name>pageCheckSeconds</param-name>
         <param-value>0</param-value>
         <verbose>true</verbose>
		 <precompile>false</precompile>
      </jsp-param>
   </jsp-descriptor>

  • Keepgenerated -> Tells weblogic to keep the java class files for easy debugging
  • pageCheckSecond -> if its -1, weblogic will not refresh jsps. so we set it to 0.
  • verbose: lets you see debug messages in the application
  • Precompile: Does not precompile the jsp
Make sure you are aware that this is basically intended during development. You really do not want to do this in produciton. Also check if your server is running in production mode
Here is a detailed description of the weblogic.xml jsp parameters