Checking Data Changes in ADF

Sometimes you want to check about data changes in attributes which exposed in data control only.
You can use below code

     DCBindingContainer dcBCon = 

         (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();  
     if (dcBCon.getDataControl().isTransactionModified()) {  
       //commit the transaction  
       dcBCon.getDataControl().commitTransaction();  
     }  

I used DCDataControl.isTransactionModified() method to check about data changes.
I used DCDataControl.commitTransaction() method to commit data changes in transaction.

Thanks
Mahmoud A. El-Sayed

Oracle DB 11g New Feature ( Compound Triggers )

In previous post I explained Virtual Column new feature in Oracle Database 11g, you can read it from here
Today I will produce new feature which called Compound Triggers.

In previous version of database you can control the execution sequence of triggers using FOLLOWS key word when creating triggers.

 CREATE OR REPLACE TRIGGER XXX_TRG  
   BEFORE INSERT  
   ON XXX_TABLE_NAME  
   REFERENCING NEW AS NEW OLD AS OLD  
   FOR EACH ROW  
   FOLLOWS YYY_TRG  
 DECLARE  
 BEGIN  
   NULL;  
 END;  

Oracle database 11g support new feature called compound triggers which can do the same purpose of FOLLOWS but in different manner.

ADF : Dynamic View Object

Today I want to write about dynamic view object which allow me to change its data source (SQL query) and attributes at run time.

I will use oracle.jbo.ApplicationModule::createViewObjectFromQueryStmt method to do this issue.

I will present how to do this step by step

ORA-01691: unable to extend lob segment in Oracle UCM

Today every user complains from unable to upload files in Oracle Universal Content Management server.
After checking log file, I find below exception

Event generated by user 'sysadmin' at host '10.32.209.196'. Unable to save the file <undefined>. Unable to execute query 'IfileStorage'. ORA-01691: unable to extend lob segment UCM.SYS_LOB0000071911C00006$$ by 14 in tablespace UCM_TBS
java.sql.SQLException: ORA-01691: unable to extend lob segment UCM.SYS_LOB0000071911C00006$$ by 14 in tablespace UCM_TBS 

Avoid Null Pointer Exception Part 2

I present in previous post Avoid Null Pointer Exception part 1 some best practice regarding my topic today, I will continue in providing more best practice and advices.

In part 1 post I listed how to avoid NPE in equalsIgnoreCase() method and enumerator, today I will write about below cases
1- Empty Collection
2- Use some Methods
3- assert Keyword
4- Assert Class
5- Exception Handling
6- Too many dot syntax
7- StringUtils Class

Encrypt and Decrypt Passwords in Database

Sometimes we store passwords in database table regarding to business requirement.
If we store password as plain text in table, Everyone who have access to database can read password easily. That's mean big security hole.

So I decided to develop package for encrypting and decrypting password.
I used DBMS_OBFUSCATION_TOOLKIT, DBMS_CRYPTO built-ins package to help me doing encryption and decryption.

I developed MAHMOUD_ENCRYPT_DECRYPT package which contains four functions (ENCRYPT1, ENCRYPT2, DECRYPT1, DECRYPT2) .

ENCRYPT1 and  DECRYPT1 functions use DBMS_OBFUSCATION_TOOLKIT package.
ENCRYPT2 and  DECRYPT2 functions use DBMS_CRYPTO package.

Commit After n Updates

If you have table has a millions of rows and you want to update whole rows in table like below statement

UPDATE TABLE_NAME

SET COLUMN_NAME='XXXXXX';

COMMIT;

It will raise an exception because of limited size of UNDO/ROLLBACK log file.
ORA-1555 errors, contact your DBA to increase the undo/ rollback segments. 

To solve this problem by code, you can commit after n updates to ignore overloading redo log file.

Get attribute Default Value from another Entity Object

I want when creating new record in entity object to get default value of attribute from another attribute in another entity object.

For example when creating new employee I should get his manager who is manager of his department.

Let's do previous example step by step in HR schema

1- Create DepartmentsEO and EmployeesEO entity objects

 1-a Right click on model project and select  New

 1-b In the new popup window choose from left pane ADF Business Component and from Items choose Entity Object

 1-c Type in Name "DepartmentsEO" and choose from Database Schema drop-down list HR and type in Schema Object "DEPARTMENTS"

1-d Click Next until reach step 4 of 6 and check "Generate Entity Object Class" and then click Finish button

Repeat steps 1-a to 1-d for EmployeesEO
After creating EmployeesEO entity object it automatically add EmpDeptFkAssoc association which associate between Employee and his department.

Cumulative Summary in Hierarchical Query using CONNECT_BY_ROOT

Today I will explain how to write hierarchical query, then modify it for getting cumulative summary function of every child using CONNECT_BY_ROOT which is supported within hierarchical queries.

For example I will create hierarchical query for employees and their manager, then display for every manager  how many  employees whose he manages cumulatively.

Java JDBC VS Java in Database

We can use Java code directly in PLSQL to manipulate database (insert data , update , do transaction .... etc) , and also we can write java code to manipulate database through JDBC connection.

When you have two solutions to do the same task, you should wait and choose the best solution.
So in my post today I will illustrate the difference between embedded java in PLSQL and Java run through JDBC connection.

For our demo I will create java code to delete and insert record in SCOTT.EMP table.
I will write code to run from PLSQL and write the same code to run through JDBC connection.

I created below Java class contains dmlOperation() method which do delete and insert record in SCOTT.EMP table and track time used for finishing transaction.

Oracle Forms : Get First Navigation Item in Tab Page

One member in araboug forum asked a question about how to get first navigation item in tab page when click on the tab page.
I answered him with below dynamic function to get first navigation item at tab page and then use GO_ITEM built in procedure to navigate to item.

I posted this function in this blog to be as reference.

ADF & OAF : Add Attribute to View Object Progmatically

You can add transient attribute to view object progmatically at run time.
You can use this transient attribute to store any temporary data for every row in view object or create generic solution in your custom framework for general purpose.

ADF

in ِADF you can use below code anywhere in ApplicationModuleImpl class
I will check existence of attribute XXAttr, If it is not exist I will add it to view object

     ViewObject vo = this.findViewObject("ViewObjectName");  
     if (vo != null) {  
       try {  
         String transientAttr = vo.findAttributeDef("XXAttr").toString();  
       } catch (Exception e) {  
         vo.addDynamicAttribute("XXAttr");  
       }  
     }  

Logging Data Changes(DML) in Database

I have posted old post about Log DDL Changes in Your Schema, Today I decided to post new post about Log Data Changes in Database(DML Operations)
I decided to create generic solution that can be used in any database.

The Idea

I will create two tables only for storing every data changes in application.
I will create generic GENERATE_TRIGGER function (pass table name as parameter )to return script of trigger which I can use to log data changes in table.

I will store Data Logging in two separate tables(Master and detail Table) as below
Master table is for storing details about every transaction (DML) in database table
Detail table is for storing data changes in table data.

LOGGING_DATA_HDR Master Table

Contains main data about every DML applied to any table
1-LOG_ID sequence column that based on LOG_ID_SEQ sequence.
2-TABLE_NAME refers to table which DML applied on it.
2-PK_DATA contains primary key value of table.
    If primary key is composite key, it separated columns by "-" string.
3-ROW_ID refers to ROWID of table..
4-LOG_DATE refers to Time stamp execution of DML in table.
5-OPERATION_TYPE refers to DML type.
    INSERT ==> "I"
    DELete  ==> "D"
    UPDATE==> "U"
6-DB_USER refers to database user which executed the DML statement.
7-CLIENT_IP refers to IP of machine which from it DML statement is executed.
8-CLIENT_HOST refers to host name of machine which from it DML statement is executed.
9-CLIENT_OS_USER refers to operating system user of machine which from it DML statement is executed.
10-APP_USER refers to application user if I used application user, I get it from  GC$APP_USER variable in MAHMOUD_LOGGING package.

LOGGING_DATA_DTL Detail Table

1-LOG_ID is foreign key to LOGGING_DATA_HDR table.
2-COLUMN_NAME refers to column name in table.
3-OLD_VALUE refers to old value before execution of DML statement.
4-NEW_VALUE refers to new value after execution of DML Statement.