Controlling TaskFlow Programatically

One of the generic solution is handling  taskflow programatically, To achieve this  you should get an object of a taskflow at managed bean and then you can call its methods for controlling in taskflow.

You can use the below method for this purpose.
Note you pass to the method taskFlowName used in page definition not the original taskflow name.

   public static DCTaskFlowBinding getTaskFlow(String taskFlowName) {  
   
     BindingContext bindingCtx = BindingContext.getCurrent();  
   
     DCBindingContainer dcbCon = (DCBindingContainer)bindingCtx.getCurrentBindingsEntry();  
   
     DCTaskFlowBinding taskFlow = (DCTaskFlowBinding)dcbCon.findExecutableBinding(taskFlowName);  
   
     return taskFlow;  
   }  

Thanks

ADF : Open Page in insert Mode

While developing data entry pages, the major request of the user is opening the page in Insert Mode ( Ready for entry).

To do this requirement we do the below steps
1- Execute executeEmptyRowSet() method for master ViewObject used in page.
2- Insert new empty row in master ViewObject
3- Make inserted row as current row in master ViewObject

I developed the below method in ApplicationModuleImpl for doing the previous steps.
You pass view object named used in application module.

   public void initInsertMode(String viewObjectName) {  
     ViewObject viewObject = this.findViewObject(viewObjectName);  
     viewObject.executeEmptyRowSet();  
   
     Row row = viewObject.createRow();  
     viewObject.insertRow(row);  
     viewObject.setCurrentRow(row);  
   }  

Import the following Classes

 import oracle.jbo.Row;  
 import oracle.jbo.ViewObject;  

I published before a post about Insert Rows in ADF View Object Programatically , it may be useful, you can read it from here   

Thanks

Recycle Bin in Database

Oracle introduced in database 10g new feature called "Recycle Bin" to store the dropped database objects.
If any table is dropped then any associated object to this table such as indexes, constraints and any other dependent object are renamed with a prefix of bin$$.

Use of Recycle Bin

If user drop an important object accidentally, and he want to get it again.
With Recycle Bin feature user can easily restore the dropped objects.

Enable and Disable Recycle Bin

You can use the below query to distinguish which Recycle Bin is enabled or no

 SELECT Value FROM V$parameter WHERE Name = 'recyclebin';  

It will return on or off
on means that Recycle Bin is enabled and off is disabled.

You can enable and disable Recycle Bin per session and system, there fore you can use the below scripts to enable and disable Recycle Bin per session or system.

 ALTER SYSTEM SET recyclebin = ON;  
   
 ALTER SESSION SET recyclebin = ON;  
   
 ALTER SYSTEM SET recyclebin = OFF;  
   
 ALTER SESSION SET recyclebin = OFF;  

Oracle Database SQL Expert Certification

Hi all,

I am now an Oracle Database SQL Expert Certified  .

I wrote 1Z0-047 today and passed it with 100% score.

Thanks

Memory Management in PLSQL

While working with PLSQL we should take care of memory usage, So I will provide some tips to avoid memory overhead in PLSQL code.

1- Variables Length
You might  allocate large VARCHAR2 variables when you are not sure how big an expression result will be.
You can conserve memory by declaring VARCHAR2 variables with large sizes, such as 32000, rather than estimating just a little on the high side, such as by specifying 256 or 1000.

PLSQL has an optimization that makes it easy to avoid overflow problems and still conserve memory. 
When you specify a size of more than 4000 characters for the VARCHAR2 variable, PLSQL waits until you assign the variable, then only allocates as much storage as needed.

2- Subprograms into Packages
When you call a packaged subprogram for the first time, the whole package is loaded into the shared memory pool.
Subsequent calls to related subprograms in the package require no disk I/O, and your code executes faster. If the package ages out of memory, and you reference it again, it is reloaded.

You can improve performance by sizing the shared memory pool correctly. Make it large enough to hold all frequently used packages, but not so large that memory is wasted.

3- Pin Packages in the Shared Memory Pool
You can pin frequently accessed packages in the shared memory pool, using the supplied package DBMS_SHARED_POOL. When a package is pinned, it does not age out; it remains in memory no matter how full the pool gets or how frequently you access the package.

4- Use Compiler Warnings
The PLSQL compiler issues warnings about things that do not make a program incorrect, but might lead to poor performance. If you receive such a warning, and the performance of this code is important, follow the suggestions in the warning and make the code more efficient.

Thanks
Mahmoud A. El-Sayed

WITH Clause in SELECT statement

I will explain best practice and benefits of using WITH clause in Oracle Database.

WITH is used with SELECT query to collect the data set first and then query against collected data set in WITH clause, there for the query doesn't start with SELECT, it will start with WITH clause first.

Syntax of WITH clause

WITH with_clause_name AS ( SELECT STATEMENT)

SELECT *

  FROM with_clause_name;

Example

 WITH with_clause_name AS (SELECT 1 one FROM DUAL)  
 SELECT *  
  FROM with_clause_name;  

From previous example the WITH clause allow you to give name to SELECT statement and then later select from this named SELECT statement.

Generate list of dates and times

I want to create query returns list of dates and time for example

1-Jan-2012

1-Jan-2012 12:30:00 AM

1-Jan-2012 13:00:00 AM

1-Jan-2012 13:30:00 AM

1-Jan-2012 14:00:00 AM

.................

.................

1-Jan-2012 11:00:00 PM

1-Jan-2012 11:30:00 PM

To execute the previous requirement I can use the below query

   SELECT TO_DATE ('1-1-2012', 'DD-MM-RRRR') + (LEVEL - 1) / 48 DATE_TIME  
    FROM DUAL  
 CONNECT BY LEVEL <= 48;  

Execute Code in Page Load in ADF

I want to execute a piece of code in page load, For implementing this request I can do it using two solutions.

1- PagePhaseListener Interface

PagePhaseListener allows to write global code which executes in every page at my application.
I will create a class implements oracle.adf.controller.v2.lifecycle.PagePhaseListener.PagePhaseListener Interface and overide afterPhase method and then add this class as Phase Listener in  /META-INF/adf-settings.xml

2- BeforePhase Property of View

I will bind this property to a method in backing bean which contains the code I want to execute.

Find Unused Columns in Oracle Database

Sometimes during development of new systems, you may add new columns to tables and then you don't use it and forget dropping it.

So you want to know which these columns to drop.
Usually unused columns have NULL value, So I created a function to return array of column names in my schema have NULL value.

I created GET_NULL_COLUMNS function returns VARRAY of varchar2
It has only one parameter (IN_TABLE_NAME)
If I pass a value for IN_TABLE_NAME then it will return NULL columns in this table only, otherwise it will return NULL columns in entire schema.

OAF : Upload Excel File to Database

I want to allow user to upload excel file in database from OAF page.

Suppose that excel file contains below columns

EmpNo

EmpName

Job

Suppose also that I have Entity Object named XxxEmpEO and I created View Object XxxEmpVO based on previous entity object which has below attributes

EmpNo

EmpName

Job

Scenario

I added new item in page of type messageFileUpload ["uploadExcelFile" ] and Button ["uploadButton"].
If user click a button, I will upload excel file that is entered in messageFileUpload item to Entity Object and then commit changes to database.

Reset Sequence Value

I have old table already have a lot of data, I take decision to create new sequence to use it for getting serials in primary key of table to it.

For example : table SCOTT.EMP has EMPNO primary key and I want to create new sequence EMPNO_SEQ to store NEXTVAL of sequence in EMPNO column.

CREATE SEQUENCE SCOTT.EMPNO_SEQ
   START WITH 1
   MAXVALUE 9999999
   MINVALUE 0
   CACHE 25;

Oooooops, EMPNO column already has stored data that is maximum than sequence next value.
So I take decision to create generic procedure to reset sequence next value to maximum value of primary key in any table.

I created RESET_SEQUENCE procedure which takes three parameters
a-IN_SEQUENCE_NAME : name of sequence that I will use.
b-IN_TABLE_NAME : name of table which I will store sequence next value in its column
c-IN_COLUMN_NAME : name of column, If it is null I will get column which is primary key

Execute Javascript code from Java Code

In ADF framework you can execute Javascript code from Java code using the below method

   public static void runJavaScriptCode(String javascriptCode) {  
     FacesContext facesCtx = FacesContext.getCurrentInstance();  
   
     ExtendedRenderKitService service = Service.getRenderKitService(facesCtx, ExtendedRenderKitService.class);  
   
     service.addScript(facesCtx, javascriptCode);  
   }  

Import the following classes

 import javax.faces.context.FacesContext;  
 import org.apache.myfaces.trinidad.render.ExtendedRenderKitService;  
 import org.apache.myfaces.trinidad.util.Service;  

You can call previous method from anywhere from your code
For example I will display alert using javascript

 runJavaScriptCode("alert(\"My name is Mahmoud\");");  

Thanks

PRAGMA INLINE in Subprogram Calling

Introduction

Subprogram is procedure or function that is declare in declaration section like below

 DECLARE  
   PROCEDURE XXX (IN_PARAMATER NUMBER)  
   IS  
   BEGIN  
    --CODE OF PROCEDURE  
   END;  
   
   FUNCTION YYY (IN_PARAMETER NUMBER)  
    RETURN NUMBER  
   IS  
   BEGIN  
    --CODE OF FUNCTION  
   END;  
 BEGIN  
   --CODE OF ANONYMOUS BLOCK  
 END;  

   
When you call subprogram(procedure or function) in your code multiple time, in every call it will be loaded again so it will take more time to execute.
To enhance previous drawback we use inline to replace the subprogram call with the copy of already called subprogram before.

ADF : Refresh Current Page

You can use the below code to refresh ADF page programatically

FacesContext context = FacesContext.getCurrentInstance()
String viewId = context.getViewRoot().getViewId()

ViewHandler vh = context.getApplication().getViewHandler()
UIViewRoot page = vh.createView(context, viewId);

context.setViewRoot(page);

Import the following classes

 
import javax.faces.application.ViewHandler;
import javax.faces.component.UIViewRoot;
import javax.faces.context.FacesContext;

Thanks

ADF : Redirect to another View Programatically

You can use the below method to redirect to another view in ADF programatically.

 
    public static void redirectToView(String viewId) {
        FacesContext fCtx = FacesContext.getCurrentInstance();
        ExternalContext eCtx = fCtx.getExternalContext();

        String activityUrl = ControllerContext.getInstance().getGlobalViewActivityURL(viewId);
        try {
            eCtx.redirect(activityUrl);
        } catch (IOException e) {
            e.printStackTrace();
            JSFUtils.addFacesErrorMessage("Exception when redirect to " + viewId);
        }
    }

Thanks

Oracle Forms : Print Report Directly

Usually we display Oracle Reports in Oracle Forms in PDF format and then the user print it through Adobe Reader  program or any other  PDF reader program.

But the requirement is to print Oracle Report directly without  displaying the report at screen.

To achieve this requirement in Oracle Forms 6i it is easy as you you set DESTTYPE parameter in report file to PRINTER and It will work correctly, But this feature not work with Oracle Forms 10g.

Some developers use java bean for this purpose but I will produce the below workaround to print report directly without using java bean.
1- Run Report and save it as PDF file at application server.
    I will share folder c:\temp at application server for everyone for read only and save reports PDF files in this folder. The share path of the folder is \\ApplicationServer_IP\Temp\
2- Silent Print of PDF file at user machine using PDF reader programs like Adobe Reader
3- Close PDF reader program after printing.

Some Developers may use ORARRP utility and do its required configuration at server and client machine for direct printing too.

Oracle ADF Certification

Hi all,

I am now an Oracle Certified Oracle Application Development Framework Certified Implementation Specialist

I wrote 1Z0-554 today and passed it.

Thanks

Change Database 11g Port Number

Sometimes after installing database you want to change port of Enterprise Manager.

To change it you should edit the following file and change ports number 
%ORACLE_HOME%\install\portlist.ini

You will find the file as below
Enterprise Manager Console HTTP Port (orcl) = 1158
Enterprise Manager Agent Port (orcl) = 3938

Change the ports as you like.

Thanks

Handle Business Days

Introduction
In real business life we consider days as business days only, we exclude holidays and weekends from our calendar days.
So today I will produce solution of how to work with business days.

At first public holidays are different from country to others so I will create table to store every public holidays.

 CREATE TABLE HOLIDAYS (HOLIDAY_DATE DATE, DESCR VARCHAR2 (100));  
   
 ALTER TABLE HOLIDAYS ADD (  
  CONSTRAINT HOLIDAYS_PK  
  PRIMARY KEY  
  (HOLIDAY_DATE));  

I should get weekends days also so I will get using below query
Note that at my country weekend is Fridays and Saturday.

   SELECT TO_DATE ('05-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999  
 UNION 
   SELECT TO_DATE ('06-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999  

The previous query contains whole weekends from 5th January 1900 to 18th August 2091.

Now I will create database view for whole holidays( Public holidays and weekends)

 CREATE OR REPLACE VIEW HOLIDAYS_VIEW  
 (HOLIDAY_DATE)  
 AS   
 SELECT HOLIDAY_DATE FROM HOLIDAYS  
 UNION  
   SELECT TO_DATE ('05-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999  
 UNION  
   SELECT TO_DATE ('06-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999;  
   

Get Iterator of ADF Table

You can use the below method to get an iterator of ADF Rich Table.
You pass ADF Rich Table component and you will get DCIterator of table.

 
     public static DCIteratorBinding getDciteratorFromTable(RichTable table) {
        CollectionModel model = (CollectionModel)table.getValue();
        JUCtrlHierBinding treeBinding = (JUCtrlHierBinding)model.getWrappedData();
        return treeBinding.getDCIteratorBinding();
    }

You can import the following classes

 
import oracle.adf.model.binding.DCIteratorBinding;
import oracle.adf.view.rich.component.rich.data.RichTable;
import oracle.jbo.uicli.binding.JUCtrlHierBinding;
import org.apache.myfaces.trinidad.model.CollectionModel; 

Thanks

Format Dates in Java

I posted before about different date classes in Java and conversions between them.

Sometimes I want to display date type in different formats in Java like DD-MM-RRRR , DD-MON-YYYY , MON-YY .... etc

I will post about about different Java Classes that can be used for this purpose.
1- java.text.SimpleDateFormat
2- java.text.DateFormat
3- org.apache.commons.lang.time.DateFormatUtils

Interchange the Values of Columns

Sometimes you want to interchange the values of two columns in specific database table.

Suppose that I have EMP table with column EMPNO, ENAME, JOB, and by wrong the value in ENAME column  is the value of JOB column and vice versa.
To correct the data I should interchange the values of ENAME and JOB.

I have three solutions for doing this
1- Add temporary column to table
2- Rename columns
3-DML statement

ADF : Get Key from Resource Bundle

Resource bundles contain locale-specific objects.
In this way, you can write program code that is largely independent of the user's locale isolating most, if not all, of the locale-specific information in resource bundles.

This allows you to write programs that can:
    a- be easily localized, or translated, into different languages
    b- handle multiple locales at once
    c- be easily modified later to support even more locales

DBMS_COMPARISON Package

DBMS_COMPARISON is a new package introduced by oracle in database 11g which is used for comparing database objects in different databases.

The DBMS_COMPARISON package can compare the following types of database objects:
    a- Tables
    b- Single-table views
    c- Materialized views
    d- Synonyms for tables, single-table views, and materialized views

The DBMS_COMPARISON package cannot compare data in columns of the following data types:
    a- LONG
    b- LONG RAW
    c- ROWID
    d- UROWID
    e- CLOB
    f- NCLOB
    g- BLOB
    h- BFILE
    i- User-defined types (including object types, REFs, varrays, and nested tables)
    j- Oracle-supplied types (including any types, XML types, spatial types, and media types)

Footer in Ireport

I always print in report footer  some data about displaying date and pages counter like below image

I will explain how to execute this in Ireport

ADF Faces : Get Child Component

You can use the code snippet for finding child component under parent component  in ADF Faces.

   public static FacesContext getFacesContext() {  
     return FacesContext.getCurrentInstance();  
   }  
   
   public static UIComponent getChildUIComponent(String ParentUI, String ChildUI) {  
     UIComponent parentUI = getFacesContext().getViewRoot().findComponent(ParentUI);  
     UIComponent childUI = null;  
     UIComponent tempUI = null;  
   
     Iterator childrens = parentUI.getFacetsAndChildren();  
     while (childrens.hasNext()) {  
       tempUI = (UIComponent)childrens.next();  
       if (ChildUI.equals(tempUI.getId())) {  
         childUI = tempUI;  
         break;  
       }  
     }  
     return childUI;  
   }  

Import the following Classes

 import javax.faces.component.UIComponent;  
 import javax.faces.context.FacesContext;  

Thanks

CHAR vs VARCHAR vs VARCHAR2

Today I will present short notes about popular string data types in SQL and PLSQL.

CHAR

it is used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store variable length strings, it will waste a lot of disk space.
Example

 CREATE TABLE CHAR_TAB (CHAR_COL CHAR (10));  

 INSERT INTO CHAR_TAB (CHAR_COL)  
    VALUES ('Mahmoud');  
 COMMIT;  

Let's now select data and lenght of data and dump of data in table CHAR_TAB

 SELECT CHAR_COL, LENGTH (CHAR_COL), DUMP (CHAR_COL) FROM CHAR_TAB;  

The output will be like

As We noticed in result that length is 10 characters inspire of I entered 'Mahmoud' which has only 7 characters.
We also noticed that in dump he padded at latest ASCII code three times number 32 which is ASCII code of space.

ADF : Filter View Object Rows

In this post I explain how to filter rows in ViewObject and RowSetIterator.
Primarily, filter the rows means return a set of rows from ViewObject or RowSetterator according specific criteria which is filtered in memory only.

1- Filter ViewObject

     //Get ViewObjectImpl object  
     ViewObjectImpl vo = getDeptVO();  
       
     //Filter using specific attribute value  
     Row[] filteredRows = vo.getFilteredRows("AttributeName", "AttributeValue");  
   
     //Filter using RowQualifier Class  
     //Use RowQualifier if you have more than one condition in filtering rows  
     RowQualifier rowQualifier = new RowQualifier(vo);  
     rowQualifier.setWhereClause("AttributeName=AttributeValue");  
     filteredRows = vo.getFilteredRows(rowQualifier);  

2- Filter RowSetIterator

     //Get ViewObjectImpl object  
     ViewObjectImpl vo = getAllAdvisorView();  
          
     //Get RowSetIteratorImpl object  
     RowSetIterator rsIterator=vo.createRowSetIterator(null);  
       
     //Filter using specific attribute value  
     Row[] filteredRowsRSI = rsIterator.getFilteredRows("AttributeName", "AttributeValue");  

Thanks

Generate Random Password in Oracle

In my application I need to generate password for users first time after registration.
The generated password must has some criteria which system administrator will configure it.
1- Character should be UPPERCASE                              =====> Abbreviation [U]
2- Character should be LOWERCASE               =====> Abbreviation [L]
3- Character should be NUMBER                  =====> Abbreviation [N]
4- Character should be any character                      =====> Abbreviation [A]
5- Character should be NON-ALPHANUMERIC character =====> Abbreviation [S]

So I thought to create dynamic function "RANDOM_PASSWORD" to return random password regarding to previous criteria.
The the system administrator will pass criteria per every character in password text to function and it will return random password
For example :-
first character should be UPPERCASE               ======> U
second character should be LOWERCASE          ======> L
third character should be NUMBER                   ======>N
forth character should be any character             ======>A
fifth character should be NON-ALPHANUMERIC  ======>S
sixth character should be Number                    ======> N   

This will generate string "ULNASN" regarding to abbreviation.

ADF : Call Method from PageDefinition Programatically

I will explain how to call  a method in Managed Bean from PageDefinition using Java code.

1- Create methodAction binding in PageDefinition for a method

You can add  methodAction  binding in PageDefinition for a methods exists in ViewObjects or Application Modules at Data Control


Open PageDefinition and add new action 

Change Look and Feel of oracle Forms

To change look and feel of Oracle Forms open the following file

Forms 11g
%MiddleWare_HOME%\user_projects\domains\<domain_name>\config\fmwconfig\servers\WLS_FORMS\applications\formsapp_11.1.1\config\formsweb.cfg

Forms 10g

%FormsHome%\forms\server\formsweb.cfg

Regarding different version of Oracle Forms 11g you can search about  formsweb.cfg file in MiddleWare Home also.

Locate element lookandfeel in any section like the following

 [sepwin]  
  lookandfeel=Generic  
   
 [webutil]  
 lookAndFeel=windows  

lookandfeel can be one of the following
1-oracle
2-Generic
3-windows

Note change lookandfeel doesn't require restart service to take effect, It effects directly after saving formsweb.cfg file.

Thanks

Oracle DB 11g New Feature (Read Only Tables)

I posted before about new features about Oracle Database 11g you can read it from below

Oracle DB 11g New Feature ( Virtual Columns ) 

Oracle DB 11g New Feature ( Compound Triggers )

Today I will produce new feature which called Read Only Tables

Read only tables are like normal  tables but it restricts any transaction to perform any DML(Insert, Update, Delete) operation against it.

Before oracle database version 11g READ ONLY was related to DATABASE and TABLE SPACE only but in version 11g you can do READ ONLY to tables too.

ArrayList in Java

My post today is about java.util.ArrayList in java and how to perform popular operation on ArrayList.

When writing this post I find the best thing is to write java class for doing operations  and commenting the operation in class.

 import java.util.ArrayList;   
  import java.util.Arrays;   
  import java.util.Collections;   
  import java.util.Iterator;   
  import java.util.List;   
  import java.util.ListIterator;   
  public class ArrayListDemo {   
   public static void arrayListOperations() {   
   
    
   
   //Create new two objects of ArrayList   
    System.out.println("===============================================================");   
    System.out.println("Create new two objects of ArrayList");   
    ArrayList list1 = new ArrayList();   
    ArrayList list2 = new ArrayList();   
   
    
   
  // Adding to list1 object 10 elements using loop   
    System.out.println("===============================================================");   
    System.out.println("Adding to list1 object 10 elements using loop");   
    for (int i = 0; i <= 10; i++) {   
     list1.add(i);   
    }   
   
    
   
  //Adding to list1 object String Objects   
    System.out.println("===============================================================");   
    System.out.println("Adding to list1 object String Objects");   
    list1.add("Mahmoud");   
    list1.add("El-Sayed");   
    //Retrieve every elements stored in ArrayList using Iterator   
    System.out.println("===============================================================");   
    System.out.println("Retrieve every elements stored in ArrayList using Iterator");   
    Iterator iterator = list1.iterator();   
    while (iterator.hasNext()) {   
     System.out.println(iterator.next().toString());   
    }  
   
    
   
  //Retrieve every elements stored in ArrayList using ListIterator   
    System.out.println("===============================================================");   
    System.out.println("Retrieve every elements stored in ArrayList using ListIterator");   
    ListIterator listIterator = list1.listIterator();   
    while (listIterator.hasNext()) {   
     System.out.println(listIterator.next().toString() + " PreviousIndex" + listIterator.previousIndex() +   
          " NextIndex" + listIterator.nextIndex());   
    }  
   
    
   
  //Retrieve every elements stored in ArrayList using indexing   
    System.out.println("===============================================================");   
    System.out.println("Retrieve every elements stored in ArrayList using indexing");   
    for (int i = 0; i < list1.size(); i++) {   
     System.out.println(list1.get(i).toString());   
    }  
   
    
   
  //Search about particular object and get its index in ArrayList   
    System.out.println("===============================================================");   
    System.out.println("Search about particular object and get its index in ArrayList");   
    int index = list1.indexOf("Mahmoud");   
    System.out.println("Mahmoud exists in index " + index);  
   
    
   
  //search about last index of particular object   
    System.out.println("===============================================================");   
    System.out.println("search about last index of particular object");   
    index = list1.lastIndexOf("Mahmoud");   
    System.out.println("Last index of Mahmoud is " + index);  
   
    
   
  //Create sub list from index 10 to end of the array list   
    System.out.println("===============================================================");   
    System.out.println("Create sub list from index 11 to end of the array list");   
    List subList = list1.subList(11, list1.size());   
    System.out.println("The sublist is " + subList);  
   
    
   
  //Sort the created sub list   
    System.out.println("===============================================================");   
    System.out.println("Sort the created sub list");   
    Collections.sort(subList);   
    System.out.println("Sublist after sorting " + subList);  
   
    
   
  //Reverse the created sublist   
    System.out.println("===============================================================");   
    System.out.println("Reverse the created sublist");   
    Collections.reverse(subList);   
    System.out.println("sublist after reversing " + subList);  
   
    
   
  //Check if array list and list is empty   
    System.out.println("===============================================================");   
    System.out.println("Check if array list and list is empty ");   
    System.out.println("list1 is empty " + list1.isEmpty());   
    System.out.println("list2 is empty " + list2.isEmpty());   
    System.out.println("subList is empty " + subList.isEmpty());  
   
    
   
  //check if list1 is equal to list2   
    System.out.println("===============================================================");   
    System.out.println("check if list1 is equal to list2");   
    System.out.println("Is list1 is equal to list2? " + list1.equals(list2));   
   
    
   
  //Convert List to array and print array   
    System.out.println("===============================================================");   
    System.out.println("Convert List to array and print array");   
    Object objs[] = list1.toArray();   
    System.out.println("Print list after converting to Array " + Arrays.toString(objs));   
   
    
   
  //Remove whole elements from Array List   
    System.out.println("===============================================================");   
    System.out.println("Remove whole elements from Array List");   
    list1.clear();   
    System.out.println("Elements in list1 after clearing " + list1);   
   }   
   
    
   
  public static void main(String[] args) {   
    ArrayListDemo.arrayListOperations();   
   }   
  }   
   

Play with NULL Value

NULL is big problem in whole programming language, Today I will write about NULL and  what's the problems we face in code and tips to play with NULL values.

Calling any method or variable of Class has NULL value raise exception in other programming language like C++, Java, C# ,.... etc but in PLSQL it doesn't raise any exception.
If I use NULL in any calculation or logical condition, the output will be NULL.

I wrote before about Three-Valued Logic and the problem  in three-valued logic in PLSQL is NULL value.

Lets see example work with NULL before begin illustration.

CREATE OR REPLACE PROCEDURE MY_NAME (IN_NAME VARCHAR2)
IS
BEGIN
   IF IN_NAME != 'Mahmoud'
   THEN
      DBMS_OUTPUT.PUT_LINE ('My name is not Mahmoud');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('My name is Mahmoud');
   END IF;
END;

BEGIN
   MY_NAME (NULL);
END;

If I run previous code it will print

 My name is Mahmoud  

Create Insert Statement for Table Data

In every site we have more than one environment (Testing, Development, Production, .....etc).
Sometimes we insert any data in one environment and want to migrate it to another environment.

Usually we use database editors to do this task like (Toad, Plsql Developer, SQL developer, .... etc), but in my post today I will create PLSQL function that will generate insert statement for you.

Here is the GEN_INSERT_STATEMENT function is used return SQL select  statement against input table parameter which we can use it to generate insert statement

Custom Exception in ADF

Sometimes in your business logic you want to throw an exception regarding business requirements not Java syntax coding exception.
For example you want to raise an exception that birthday is not later than hire date.

To implement this cased in ADF I prefer the below steps.
1- Create new "ApplicationException" class and extend oracle.jbo.JboException

Create new custom class which extends oracle.jbo.JboException for adding your custom code to exception later.

Search about Text in Schema

I will present today solution help us in searching about specific text in entire schema.

Suppose you want to search about 'MANAGER' string at entire tables in your schema.
You will do select statement against every table in your schema and you will will identify every column in table at select statement.

So I developed generic procedure has input search text and generate select statement against every table in schema and execute it and return the result in DBMS OUTPUT console.

The procedure return ROWD per every table has search text in any of its own columns and print it in DBMS OUTUT console in below format

<<ROWID>> IN TABLE <<TABLE_NAME>>

Add Validation at Runtime in ADF

An user request the below requirement.
He want to change validation condition in specific attribute at Entity Object at runtime.

For example there is a maximum of employee salary which can be changed at runtime, therefore the user doesn't want the maximum salary of employee to be fixed.

So I will create a new method for setting Validation Expression at runtime using Groovy.

   public void addExpressionValidator(AttributeDef attributeDef, String groovyExpression, String errorMessage) {  
     //create new ExpressionValidator  
     JboExpressionValidator jboExpressionValidator = new JboExpressionValidator(false, groovyExpression);  
   
     //Set an error message  
     jboExpressionValidator.setErrorMsgId(errorMessage);  
   
     //adding the validator to the attribute  
     ((AttributeDefImpl)attributeDef).addValidator(jboExpressionValidator);  
   
   }  

Playing with LOB Data Types

Lobs are the most difficult data type to store and retrieve in oracle database.

In this article, I am going to discuss extensively how to manipulate LOBs in Oracle database.

LOBs that are stored in the database itself like BLOB,CLOB,NCLOB.

BFILE which is stored outside the database as Operating System files.

BFILEs act as a pointer and store the location of the external OS files in database tables.

Center Canvas and Window in Oracle Forms

Today I will present two dynamic procedure for centering canvas or windows in middle center of the screen.

We always want to show canvas at middle center in another canvas and also for window in oracle forms.

So I will produce today two generic procedures for centering canvas/window at middle center of others.

Centering Canvas

To implement displaying canvas at middle center of another canvas (Container canvas) I will create procedure has two parameters ( canvas [C1] and container canvas[C2] ) and procedure will change x,y coordination of canvas[C1] at middle center of canvas[C2]

 PROCEDURE CENTER_VIEW (IN_VIEW_NAME VARCHAR2, IN_CONTAINER_VIEW VARCHAR2)  
 IS  
 BEGIN  
   SET_VIEW_PROPERTY (IN_VIEW_NAME,  
            VIEWPORT_X_POS,  
             (GET_VIEW_PROPERTY (IN_CONTAINER_VIEW, WIDTH) / 2  
             )  
            - (GET_VIEW_PROPERTY (IN_VIEW_NAME, WIDTH) / 2)  
            );  
   SET_VIEW_PROPERTY (IN_VIEW_NAME,  
            VIEWPORT_Y_POS,  
             (GET_VIEW_PROPERTY (IN_CONTAINER_VIEW, HEIGHT) / 2  
             )  
            - (GET_VIEW_PROPERTY (IN_VIEW_NAME, HEIGHT) / 2)  
            );  
 END;  

ADF : Iterate ViewObject

In some cases you want to iterate through ViewObject, To do this you have two choice
1- Iterate through ViewObject and change current row in ViewObject
2- Iterate through ViewObject without changing current row

I will present a code snippet  for every one
Assume that you will do this code in ApplicationModuleImpl class

Cursor Declaration in Packages

When you use global cursor in package, you should determine where cursor declaration in your code.
Some developers declare cursor in package specification and others in package body.
My post today to illustrate difference between different approach of cursor declaration in package.

ADF : Get Current Logged User Name

Sometime we need getting current authenticated user name in ADF application.
To get user name we can do it using three ways

1- Using Groovy 

We can set default expression in view object attribute as below

adf.context.securityContext.getUserPrincipal().getName() 

or

adf.context.securityContext.getUserName() 

2- Java Code

You can get User Name in Java code also using the following code.

     ADFContext adfCtx = ADFContext.getCurrent();  
     SecurityContext secCntx = adfCtx.getSecurityContext();  
     String user = secCntx.getUserPrincipal().getName();  
     String _user = secCntx.getUserName();  

3-Expression Lnagugae

You can bind ADF Faces componenets with the following EL to get logged User Name.

 #{securityContext.userName}  

Thanks

Max Function vs Databse Sequence

In database design we always use sequence numbers for primary keys(unique values) and We can implement this by database sequences or getting max value in the column using MAX function.
I called this approach "Sequence VS MAX()".
I will explain every approach first then and list drawbacks for every one.

Run Oracle Reports from Oracle Forms

I will explain how to run and display Oracle Reports within Oracle Forms 10g/11g

1- In Oracle Forms add new Report

At your form add new report from object navigator and change following properties
Name                                      :  ID for report in Oracle Forms
Filename                                 :  Physical file name path of report at application server
Report Destination Type       :  You can choose (File, Preview, Printer, Cache, Mail, Screen)
Report Destination Format   :  Output format of report (PDF, spreadsheet)

Create View with Parameter

From the title of this post you guess that oracle give us capability to create view with parameter, but this is wrong, don't think good of oracle to give you this capability as straight forward.

I have workaround to do this capability by the following techniques

1-virtual private database context
2-global package variable
3-Lookup Tables

Strings in Java

I will present an article about different string Classes in java and comparison between them.
There are three String Classes in Java

1- java.lang.String

2- java.lang.StringBuilder

3- java.lang.StringBuffer

The most common class used in Java programming  is java.lang.String class.
To identify which Class we should use it, it depends on requirement, So let's first make comparison between them and at final get conclusion about guidance use.

Insert only One Record in Table

Sometimes you have table in your system that has only one record like Configuration, Setup, Settings tables.
You want to prevent users from inserting more than one record in this table, To do this you can use the below INDEX .

CREATE UNIQUE INDEX ONE_ROW_INDEX
   ON TABLE_NAME (1);

If you try to insert more than one record in previous table you will get below exception

ORA-00001: unique constraint (SCHEMA.ONE_ROW_ALLOWED) violated

Thanks
Mahmoud A. El-Sayed

PL/SQL : Pragma Restrict References

Introduction
In my packages functions and procedure, sometimes I have some rules to control purity rules in my database like reading from tables only, not query the database, ...... etc
So I will  use PRAGMA RESTRICT_REFERENCES to control and preserve the database state in database packages.

Syntax of PRAGMA RESTRICT_REFERENCES

PRAGMA RESTRICT_REFERENCES(SUBPROGRAM_NAME, IDENTIFIER)

SUBPROGRAM_NAME can be default which will affect all the subprograms in packages or subprogram name(function or procedure) in package

IDENTIFIER can be RNDS, WNDS, RNPS, WNPS and TRUST. I will explain every one separately.

1- RNDS

select query is restricted against any of database tables

2- WNDS

DML operations are restricted against any of database tables

3- RNPS

Selection of package variables is restricted

4- WNPS

Modification in packages variables is restricted

5- TRUST

Asserts that the subprogram can be trusted not to violate one or more rules

Genius ADF Buttons

I will present some genius ADF buttons that doing specific actions using Javascript

Oracle Forms : Data Block Based on multiple Table

As usual, data block in oracle forms is based on single table.
Today I will present a case to create single data block based on multiple tables.

I will implement solution on SCOTT schema.
I will display one block based on two tables(EMP and DEPT)

1- Create single data block using data block wizard based on EMP table
2- Create Layout wizard of EMP block and create it as tabular form like below image

Dates in Oracle Database

Introduction

Oracle database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default display and input format for any date is DD-MON-YY(You can change it). Valid Oracle dates are between January 1, 4712 B.C. and December 31, 9999  .

Oracle database supports simple date(True Date) and time(Date and Time) which stores in standard internal format.
Oracle supports a set of built ins function for manipulating date and time.

Date Format

As we mentioned before that oracle stores Dates internal in numeric format, but in displaying dates it displays it in different formats.
The default date format is "DD-MON-YY", the conversion of formats is done by TO_CHAR function that has below syntax

 TO_CHAR(DATE,'DATE_FORMAT','NLS_PARAMETERS');  

Date : is date value
NLS_PARAMETERS : is an optional and determine different NLS parameters in conversion.
DATE_FORMAT: It contains different format from below table

Setter and Getter of Session Parameters in ADF

We usually use session parameters as global variables that is still alive for entire session.

I will produce today the getter and setter of session parameters.

Setter of Session Parameters 

   public void setSessionParameter(String name, Object value) {  
     HttpServletRequest request =  
       (HttpServletRequest)FacesContext.getCurrentInstance().getExternalContext().getRequest();  
     HttpSession session = request.getSession(false);  
     session.setAttribute(name, value);  
   }  

Getter of Session Parameters

   public Object getSessionParameter(String name) {  
     HttpServletRequest request =  
       (HttpServletRequest)FacesContext.getCurrentInstance().getExternalContext().getRequest();  
     HttpSession session = request.getSession(false);  
     return session.getAttribute(name);  
   }  

Insight Code in Toad

In our daily programming  life we write repeating code more times so we want a tool to help us in doing our tasks.
I usually use toad insight so I will illustrate how can TOAD help us in this issue.

I have previous post about doing code template in Toad  "Toad Code Template" you can read it from here.

In this post I will explain how to use code insight in TOAD.
In your code editor when writing for example package name and type dot ".", the editor should display whole members of packages subprograms (function, procedures,types and global variables) like below

The previous drop-down list is displayed after specific period after typing dot "." or immediately by pressing CTRL+T keys

Playing with XML in Oracle Database

I posted before about storing physical  XML files in Database Table, You can read it from Here

Today I want to generate XML file from database based on certain query, then store XML file in database table.

First, I will create table to store employees XML files

 CREATE TABLE EMP_XML  
 (  
   EMPNO     NUMBER,  
   EMP_XML_FILE  XMLTYPE  
 );  

Store Physical XML Files in Database Table

I have XML files stores as physical files and I want to store these file on database table.

Scenario

1- Create a table for storing XML files
2- Create Directory object in database to refer to path of XML files
3- Use my previous post List Contents of Directory to list all files in directory
4- Read every XML file and store it on database table

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.