Friday, 19 January 2018

Explicit Cursors in Oracle PL/SQL

We declare Explicit Cursors in PL/SQL when we have a SELECT statement that returns multiple row and we want to process each row returned by the SELECT statement.
The set of rows returned by the multiple-row query is called ACTIVE SET. Its size is the number of rows that meet the search criteria.
Thus Explicit Cursors:-
  1. Can perform row-by-row processing beyond the first row returned by the query.
  2. Explicit Cursor can keep track of the row that is currently being processed.
  3. Enable the programmer to manually control Explicit Cursors in PL/SQL block.

Types of Cursor in Oracle PL/SQL

There are two types of Cursors in Oracle PL/SQL.
  1. Implicit Cursor, which are declared by PL/SQL implicitly for all DML and PL/SQL SELECT statements.
  2. Explicit Cursor, which are declared and managed by the programmer.

Introduction to Cursor

In Oracle PL/SQL if we want to process multiple records then Cursors comes into pictures.
The Oracle Server uses work areas called Private SQL areas to execute SQL statements and to store processing information. Thus, we can use explicit cursors to name a private SQL area and it accesses its stored information.
Every SQL statement that is executed by the Oracle Server has an associated individual cursor. There are two types of Cursors in Oracle PL/SQL.
  1. Implicit Cursor, which is declared by PL/SQL implicitly for all DML and PL/SQL SELECT statements.
  2. Explicit Cursor, which is declared and managed by the programmer.

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