Showing posts from 2012

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; }

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   


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.


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.


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 refer…

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_nameAS ( 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

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.

   MAXVALUE 9999999
   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\");");

PRAGMA INLINE in Subprogram Calling

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

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;

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); } }

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…

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.


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

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.


Handle Business Days

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.
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…

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; 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 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); if (ChildUI.equals(tempUI.getId())) { childUI = tempUI; break; } } return childUI; }
Import the following Classes

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


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.
Let's now select data and lenght of data and dump of data in table 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)…

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 b…

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

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
Note change lookandfeel doesn't require restart service to take effect, It effects directly after saving formsweb.cfg file.


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("==============================================================="); …

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.
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

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]


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.


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 .


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
Mahmoud A. El-Sayed

PL/SQL : Pragma Restrict References

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.

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

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

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

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.

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.

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 ''. 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) .

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
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_IDsequence 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 "-" …