Tuesday, 17 October 2017

PL/SQL Block Structure

Here we will understand the basic PL/SQL block structure. The PL/SQL block structure consist of four sections as shown below.

DECLARE (Optional): The declarative sections begins with the keyword DECLARE and ends with the executable section (BEGIN) starts. It contains declaration of all variables, constants, cursors, and user-defined exceptions that are referenced in the executable sections and the exception sections.

BEGIN (Mandatory): The executable section begins with the keyword BEGIN. This section needs to have at least one statement. However, the executable section of a PL/SQL block can include any number of PL/SQL blocks. It contains SQL statements to retrieve data from the database; contains PL/SQL statements to manipulate data in the block.

EXCEPTION (Optional): The exception section is nested within the executable section. This section begins with the keyword EXCEPTION. It specifies the actions to perform when errors and abnormal conditions arise in the executable section.

END (Mandatory): All PL/SQL blocks must conclude with an END statement. Observe that END is terminated with a semicolon.

Note: In a PL/SQL block, the keywords DECLARE, BEGIN, and EXCEPTION are not terminated by a semicolon. However, the keyword END, all SQL statements, and PL/SQL statements must be terminated with a semicolon.

Monday, 16 October 2017

Why we need PL/SQL?

To explain the need of PL/SQL, let us consider an example as shown below:

   SELECT first_name, department_id, salary FROM EMPLOYEES;

The above SQL statement is very simple and straight forward. But, if we want to change or alter any data that is retrieved in a conditional manner, we will soon experience the limitations of SQL.

To make it simple to understand, let us consider a scenario, where for every Employee retrieved, we want to check the DEPARTMENT_ID and SALARY. Now based on the DEPARTMENT_ID and SALARY we want to provide varying bonuses to the EMPLOYEES.

Looking at the above problem, we know that we have to execute the above SQL statement, collect the data, and apply logic to the data.
  1. One solution that we might think of is to write a SQL statement for each DEPARTMENTS to give bonuses to the EMPLOYEES in that DEPARTMENT. Please note, in this case we also have to check the SALARY component before deciding the bonus amount. This makes it a little complicated.
  2. The second and the more effective solution that we might think of is to include conditional statements. Thus, PL/SQL is designed to meet such requirements. It provides a programming extension to the already-existing SQL.
If you like the post, please comment, share the post and do like me on Facebook.

Thanks & Regards,
Susanto Paul

Friday, 13 October 2017

Introduction to PL/SQL

  • PL/SQL stands for “Procedural Language extension to SQL”. It is tightly integrated with SQL.
  • PL/SQL is Oracle Corporation’s standard data access language for relational databases.
  • PL/SQL provides a block structure for executable units of code. Here maintenance of code is made easier with a very well-defined structure.
  • PL/SQL seamlessly integrates procedural constructs with SQL. It provides procedural constructs such as:
    • Variables, constants, and data types
    • Control structures such as conditional statements and loops
    • Reusable program units that are written once and executed many times
  • PL/SQL offers modern software engineering features such as data encapsulation, exception handling, information hiding, and object orientation. It brings state-of-the-art programming to the Oracle Server and tool set.

Saturday, 30 September 2017

Set default values to attributes in the EntityObject for every insert in Oracle ADF

Requirement: Here in this post, we will be demonstrating the case where we will be setting default values to the attributes in the EntityObject for every new insert in Oracle ADF.
For demonstration purpose we will be using the Employees table present in the HR Schema of the Oracle Database XE 11g.
For solution of the above requirement follow the steps as shown below:
Step 1: Create an Oracle ADF Fusion Web Application named as DefaultValueInEO.
Step 2: Create an ADF Business components from tables (EmployeesEO, EmployeesVO and AppModule) from the Employees table. Click on the EmployeesEO to open EmployeeEO.xml in the editor window à Click Java à Check Generate Entity Object Class: EmployeesEOImpl à Check Accessors àCheck Create Method àClick OK as shown below.
Step 3: Open EmployeesEOImpl.java and edit the create method as shown below.
Here we have set the default values for the LastName, Email, and PhoneNumber as Paul, susanto@abc.com, and 7878787878 respectively.
Also, we can set the default value to the attribute by following the below steps. Click on the EmployeesEO to open EmployeeEO.xml in the editor window à Click Attributes à Click DepartmentId à Open Details tab à Set Default Value (Select Literal) to 20 (This will set the default value of the DepartmentId to 20).
Step 4: Create a demo.jspx page. Drag and drop EmployeesVO1 as an ADF Form from the Data Control. Also Drag and Drop CreateInsert (available under AppModuleDataControl à EmployeesVO1 à Operations à CreateInsert) as an ADF Button, and Commit (available under AppModuleDataControl à Operations à Commit) as an ADF Button.
Thus, the complete demo.jspx UI will look as below.
Save All and Run the application. Thus, the ran application is shown below.
Click on CreateInsert button, which will prepare the form available for new Insert.
In the below form we can see that the default values for the LastName, Email, and PhoneNumber as Paul, susanto@abc.com, and 7878787878 respectively. These values are set from the EmployeesEOImpl.java class.
Also DepartmentId is having default value as 20 which is set dynamically.
Fill the above form and click on Commit button to Save the changes in the Database.
Just to verify we will query in the database to check if the record is inserted successfully.
Hence, the solution to our requirement.
If you like the post, please comment, share the post and do like me on Facebook.

Thanks & Regards,
Susanto Paul

Friday, 29 September 2017

Dynamically reset af:table filter in Oracle ADF

Introduction: ADF Faces tables can be created with a default table filter for users to further filter the result set of a query, which can be in memory or including re-querying the database. By default, table filters are shown as input text fields with no restriction on what users can type in as a filter pattern.
Now let us type some valid value in the filter and search for it in the table. We will be able to see the corresponding filtered data. Now, if we want to clear the filtered data, we manually have to clear all the text from the filter and hit enter.
Here in this post, we will be demonstrating the case where we will be clearing the filtered data by one click of a Clear Filter Icon as shown below.
For solution of the above requirement follow the steps as shown below:
Step 1: Create an Oracle ADF Fusion Web Application named as ClearTableFilterDemo.
Step 2: Create an ADF Business components from tables (CountriesEO, CountriesVO and AppModule) from the Countries table present in the HR schema of the Oracle Database XE 11g.
Step 3: Create a demo.jspx page. Drag and drop CountriesVO1 from the Data Control as an ADF Read only table Please select Row Selection as Single Row and Check Enable Sorting and Enable Filtering as shown below.
Step 4: Select any column say CountryId column and set rowHeader="true". This will display the Clear Filter Icon on the header of the CountryId column and this will do the resetting of the table filter.
Save All and Run the application. Thus, the ran application is shown below.
Filter table with the RegionId as 2 as shown below.
Click on the Clear Table Filter Icon and this will reset the table filter as shown below.
Hence, the solution to our requirement.
If you like the post, please comment, share the post and do like me on Facebook.

Thanks & Regards,
Susanto Paul

Monday, 25 September 2017

Nested Application Module in Oracle ADF

Introduction: Application modules support the ability to create software components that mimic the modularity of our use cases, for which our higher-level functions might reuse a "subfunction" that is common to several business work flows. We can implement this modularity by defining composite application modules that we assemble using instances of other application modules. This task is referred to as application module nesting. That is, an application module can contain logically one or more other application modules, as well as view objects. The outermost containing application module is referred to as the root application module.
Any application module can be used as a root application module, but it would usually be one that maps to a more complex use case. When a root application module contains other nested application modules, they all participate in the root application module’s transaction and share the same database connection and a single set of entity caches. This sharing is handled for us automatically by the root application module and its Transaction object.
When we nest an instance of one application module inside another, we trigger not only the view objects in its data model, but also any service methods it defines. This feature of nesting, or reusing, an instance of one application module inside of another application module is one of the most powerful design aspects for implementing larger-scale, real-world application systems.
Demonstration:  For demonstration purpose we will use the HR schema of the Oracle Database XE 11g. To create the sample application follow the steps as shown below:
Step 1: Create an Oracle ADF Fusion Web Application named as NestedAMDemo.
Step 2: Create an ADF Business components for tables (only EOs and VOs) from the EMPLOYEES and DEPARTMENTS table. Note: Don’t create Application Module at this point.
Step 3: Let us create two application modules: EmployeesAM containing the instance of EmployeesVO, and DepartmentsAM containing the instance of DepartmentsVO.
Step 4: Create a demo.jspx page. Drag and drop EmployeesVO1 and DepartmentsVO1 from the Data Control as a table on the demo.jspx page as shown below.
Step 5: Open demoPageDef.xml file. We can see that it uses or shows two different DataControl: DepartmentsAMDataControl and EmployeesAMDataControl.
Step 6: Now run the demo.jspx page and check connections in Web Logic Server Administration Console (Servers à DefaultServer à Monitoring à JDBC). It shows two Database connections (one for each Application Modules).
Step 7: Now let see what happens when we use Nested Application Module concept. Let us create a new application module and name it as RootAM and include both the EmployeesAM and DepartmentsAM inside RootAM as shown below:
Thus the screen shot of the Data Control is as below:
Step 8: Now let us look at the demoPageDef.xml file. We can see that it uses or shows only one DataControl: RootAMDataControl.
Step 9: Now run the demo.jspx page and check connections in Web Logic Server Administration Console (Servers à DefaultServer à Monitoring à JDBC). It will show only one Database connection that is used by the Root Application Module.
Thus, the main purpose of using Nested Application Module is to avoid multiple database connection. Hence, the solution to our requirement.
If you like the post, please comment, share the post and do like me on Facebook.

Thanks & Regards,
Susanto Paul 

Sunday, 30 July 2017

Insert records programmatically in Oracle ADF

Requirement: I have an student.jspx page. In that page I have a table displaying Student details from STUDENT table. ON top of the Student table in the ADF page I have a Add button as shown below:

Now when I click on the Add button it will show the popup with the form to enter the new Student details. And there will be one button : Add.
Also the field for StudentId will be pre-populated with the new StudentId and will be a Read Only field. The StudentId will be calcullated based on the last StudentId in the database table. Here In this case as the last StudentId in the Student table is 2, hence the new StudentId 3 is dispalyed in the form. Once we fill the form and enter on Save buttoni it will insert new Student record for that particular StudentId and will return to the Student table. Also it should refresh the Student table automatically.

Solution: For solution of the above requirement we will be using the Student table which is created as shown below:
create table student (student_id number(10) primary key, student_name varchar2(20),student_class varchar2(20));

insert into student values (1,'Susanto','MBA');
insert into student values (2,'Anitha','MCA');

Thus, for solution of the above requirement follow the steps as shown below:
Step1: Create an Oracle ADF Fusion Web Application and named it as ‘AddDemo‘.
Step 2: Create an ADF Business Components from table for the Student table. The ADF Business Components should comprise of StudentEO, StudentVO, and ApplicationModule as shown below:

Step 3: Create a student.jspx page. Now go to the Data Controls and expand AppModuleAMDataControl. Select Student1, and drag and drop Student1 on the student.jspx page as a ADF Table as shown below:

Check Enable Sorting, Enable Filtering, and Read-Only Table. Also select Single Row radio button as shown below:

Click OK.
Step 4: Now surround the af:table with the af:panelCollection. Inside the toolbar facet, drag and drop af:toolbar from the component table. Inside the af:toolbar drag and drop af:button and name the af:button as Add.
Also inside the af:toolbar drag and drop af:popup and make the contentDelivery="lazyUncached“ .
Create the binding of the af:popup as binding="#{pageFlowScope.AddBean.popupBind}". This is needed so that we can hide the af:popup after successfull Addition of the new Student record.
Also create a popupFetchListener for the af:popup as popupFetchListener="#{pageFlowScope.AddBean.popupFetchListener}". This is required to do some processing when the af:popup loads. Inside the popupFetchListener we will be incrementing the StudentId and set the incremented StudentId value in the Form.
Now drag and drop af:dialog inside the af:popup. Set the type="none".
Drag and drop af:panelGroupLayout from the component palette and set halign="left" layout="vertical".
Drag and drop three af:inputText and label it as "Student Id", "Student Name", and "Student Class". Also create the bindings for the three af:inputText as binding="#{pageFlowScope.AddBean.sidBind}", binding="#{pageFlowScope.AddBean.snameBind}", and binding="#{pageFlowScope.AddBean.sclassBind}" respectively.
Also create the value binding for the first af:inputText as value="#{pageFlowScope.AddBean.studentId}" and set readOnly="true".
Now inside the af:dialog we have a facet named buttonBar. Inside buttonBar the drag and drop af:button and set the properties as text="Save" and actionListener="#{pageFlowScope.AddBean.addMethod}".
Step 5: Now goto the student.jspx page and open the Bindings tab. Beside the Bindings section click the green + icon to action action binding as shown below:


Select action and click OK. Expand AppModuleAMDataControl and Expand Operations and select Commit as shown below:

Click OK. This will add the commit action binding in the student.jspx page as shown below:

Save All the application.
Step 6: Thus the complete student.jspx page is shown below:

<?xml version='1.0' encoding='UTF-8'?>
<jsp:root xmlns:jsp="http://java.sun.com/JSP/Page" version="2.1"
       xmlns:f="http://java.sun.com/jsf/core"
       xmlns:af="http://xmlns.oracle.com/adf/faces/rich">
       <jsp:directive.page contentType="text/html;charset=UTF-8" />
       <f:view>
              <af:document title="student.jspx" id="d1">
                     <af:messages id="m1" />
                     <af:form id="f1">
                           <af:panelCollection id="pc1">
                                  <f:facet name="menus" />
                                  <f:facet name="toolbar">
                                         <af:toolbar id="t2">
                                                <af:button text="Add" id="b1">
                                                       <af:showPopupBehavior popupId="p1" />
                                                </af:button>
                                                <af:popup childCreation="deferred" autoCancel="disabled" id="p1"
                                                       binding="#{pageFlowScope.AddBean.popupBind}"
                                                       popupFetchListener="#{pageFlowScope.AddBean.popupFetchListener}"
                                                       contentDelivery="lazyUncached">
                                                       <af:dialog id="d2" type="none">
                                                              <af:panelGroupLayout id="pgl1" halign="left" layout="vertical">
                                                                     <af:inputText label="Student Id" id="it1"
                                                                            binding="#{pageFlowScope.AddBean.sidBind}"
                                                                            value="#{pageFlowScope.AddBean.studentId}" readOnly="true" />
                                                                     <af:inputText label="Student Name" id="it2"
                                                                            binding="#{pageFlowScope.AddBean.snameBind}" />
                                                                     <af:inputText label="Student Class" id="it3"
                                                                            binding="#{pageFlowScope.AddBean.sclassBind}" />
                                                              </af:panelGroupLayout>
                                                              <f:facet name="buttonBar">
                                                                     <af:button text="Save" id="b2"
                                                                            actionListener="#{pageFlowScope.AddBean.addMethod}" />
                                                              </f:facet>
                                                       </af:dialog>
                                                </af:popup>
                                         </af:toolbar>
                                  </f:facet>
                                  <f:facet name="statusbar" />
                                  <af:table value="#{bindings.Student1.collectionModel}" var="row"
                                         rows="#{bindings.Student1.rangeSize}"
                                         emptyText="#{bindings.Student1.viewable ? 'No data to display.' : 'Access Denied.'}"
                                         rowBandingInterval="0"
                                         selectedRowKeys="#{bindings.Student1.collectionModel.selectedRow}"
                                         selectionListener="#{bindings.Student1.collectionModel.makeCurrent}"
                                         rowSelection="single" fetchSize="#{bindings.Student1.rangeSize}"
                                         filterModel="#{bindings.Student1Query.queryDescriptor}"
                                         filterVisible="true"
                                         queryListener="#{bindings.Student1Query.processQuery}"
                                         varStatus="vs" id="t1">
                                         <af:column
                                                sortProperty="#{bindings.Student1.hints.StudentId.name}"
                                                filterable="true" sortable="true"
                                                headerText="#{bindings.Student1.hints.StudentId.label}" id="c1">
                                                <af:outputText value="#{row.StudentId}"
                                                       shortDesc="#{bindings.Student1.hints.StudentId.tooltip}"
                                                       id="ot1">
                                                       <af:convertNumber groupingUsed="false"
                                                              pattern="#{bindings.Student1.hints.StudentId.format}" />
                                                </af:outputText>
                                         </af:column>
                                         <af:column
                                                sortProperty="#{bindings.Student1.hints.StudentName.name}"
                                                filterable="true" sortable="true"
                                                headerText="#{bindings.Student1.hints.StudentName.label}" id="c2">
                                                <af:outputText value="#{row.StudentName}"
                                                       shortDesc="#{bindings.Student1.hints.StudentName.tooltip}"
                                                       id="ot2" />
                                         </af:column>
                                         <af:column
                                                sortProperty="#{bindings.Student1.hints.StudentClass.name}"
                                                filterable="true" sortable="true"
                                                headerText="#{bindings.Student1.hints.StudentClass.label}"
                                                id="c3">
                                                <af:outputText value="#{row.StudentClass}"
                                                       shortDesc="#{bindings.Student1.hints.StudentClass.tooltip}"
                                                       id="ot3" />
                                         </af:column>
                                  </af:table>
                           </af:panelCollection>
                     </af:form>
              </af:document>
       </f:view>

</jsp:root>

Step 7: Thus Now goto the AddBean.java and write the code as shown below:

package com.susanto.bean;

import javax.faces.event.ActionEvent;

import oracle.adf.model.BindingContext;
import oracle.adf.model.OperationBinding;
import oracle.adf.model.binding.DCBindingContainer;
import oracle.adf.model.binding.DCIteratorBinding;
import oracle.adf.view.rich.component.rich.RichPopup;
import oracle.adf.view.rich.component.rich.input.RichInputText;

import oracle.adf.view.rich.event.PopupFetchEvent;

import oracle.adfinternal.view.faces.taglib.listener.ResetActionListener;

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

public class AddBean {
       private RichInputText sidBind;
       private RichInputText snameBind;
       private RichInputText sclassBind;
       private RichPopup popupBind;
       private String stuId;
       private String studentId;

       public void setStudentId(String studentId) {
              this.studentId = studentId;
       }

       public String getStudentId() {
              return studentId;
       }

       public AddBean() {
       }

       public void setSidBind(RichInputText sidBind) {
              this.sidBind = sidBind;
       }

       public RichInputText getSidBind() {
              return sidBind;
       }

       public void setSnameBind(RichInputText snameBind) {
              this.snameBind = snameBind;
       }

       public RichInputText getSnameBind() {
              return snameBind;
       }

       public void setSclassBind(RichInputText sclassBind) {
              this.sclassBind = sclassBind;
       }

       public RichInputText getSclassBind() {
              return sclassBind;
       }

       public void addMethod(ActionEvent actionEvent) {

              String sid = sidBind.getValue().toString();
              String sname = snameBind.getValue().toString();
              String sclass = sclassBind.getValue().toString();
              System.out.println("sid : " + sid);
              System.out.println("sname : " + sname);
              System.out.println("sclass : " + sclass);

              DCBindingContainer bindings = (DCBindingContainer) BindingContext.getCurrent().getCurrentBindingsEntry();
              DCIteratorBinding requestIter = bindings.findIteratorBinding("Student1Iterator");
              ViewObject vo = requestIter.getViewObject();
              Row row = vo.createRow();
              row.setAttribute("StudentId", sid);
              row.setAttribute("StudentName", sname);
              row.setAttribute("StudentClass", sclass);
              vo.insertRow(row);
              OperationBinding operationBinding = (OperationBinding) bindings.getOperationBinding("Commit");
              operationBinding.execute();
              popupBind.hide();
              ResetActionListener ral = new ResetActionListener();
              ral.processAction(actionEvent);
       }

       public void setPopupBind(RichPopup popupBind) {
              this.popupBind = popupBind;
       }

       public RichPopup getPopupBind() {
              return popupBind;
       }

       public void popupFetchListener(PopupFetchEvent popupFetchEvent) {
              System.out.println("Inside PopupFetchEvent");

              DCBindingContainer bindings1 = (DCBindingContainer) BindingContext.getCurrent().getCurrentBindingsEntry();
              DCIteratorBinding dcIterBind = (DCIteratorBinding) bindings1.get("Student1Iterator");
              ViewObject vo1 = dcIterBind.getViewObject();
              vo1.last();
              stuId = vo1.getCurrentRow().getAttribute("StudentId").toString();
              System.out.println(stuId);
              Integer studtId = Integer.parseInt(stuId) + 1;
              System.out.println("studtId" + studtId);
              this.studentId = studtId.toString();
       }

}
Step 8: Save all and run the Student.jspx. The ran application is shown below:


Now click on the Add button and it will open a popup with the new StudentId pre-populated as shown below:


Fill the form with the Student Name as Moumita and Student Class as MCA.
Click on Save. Thus we can see below that the data gets reflected in the Student table in the ADF page.


Just for confirmation purpose  I will check if the record is inserted in the database also. Thus the below image shows that the record is also saved in the database.


Hence the solution to our requirement.

If you like the post, please comment, share the post and do like me on Facebook.

Thanks & Regards,
Susanto Paul