PL/SQL vs Java Logic in Oracle ADF - What Should Go Where?

When developing applications using Oracle ADF, one of the most important architectural decisions is where to place your business logic - in the database using PL/SQL or in the middle layer using Java.

Making the wrong choice does not just affect code readability - it impacts performance, scalability, and long-term maintainability.

This guide provides clear, experience-based direction on how to decide what belongs where.

Understanding the Architecture

An Oracle ADF application typically consists of three layers:

• Database Layer - PL/SQL, Stored Procedures, Functions
• Middle Layer - ADF Business Components (Entity Object, View Object, Application Module)
• UI Layer - JSF Pages

A simple guiding principle:

• Keep logic close to where it performs best and is easiest to maintain

When to Use PL/SQL

1. Heavy Data Processing

If your logic involves large datasets, complex joins, aggregations, or bulk operations, PL/SQL is the right choice.

Why:

• Data processing happens inside the database
• Minimal data transfer between layers
• Better performance for large-scale operations

Examples:

• Financial calculations across thousands of records
• Batch updates or scheduled jobs
• Complex reporting queries

---

2. Transaction-Critical Operations

For operations where data integrity is critical, PL/SQL provides strong transactional control.

Why:

• Database ensures atomicity and consistency
• Easier rollback and commit handling

Examples:

• Payment processing
• Inventory updates
• Ledger entries

---

3. Reusable Database Logic

If the same logic is required by multiple systems or modules, placing it in PL/SQL ensures consistency.

Examples:

• Validation rules shared across applications
• Common calculation formulas
• Data transformation logic

---

4. Data-Intensive Validations

Validations that depend on large datasets or multiple table checks should be handled in PL/SQL.

Why:

• Avoids unnecessary data fetching into the application layer
• Faster execution within the database

When to Use Java (ADF Business Components)

1. Application-Level Business Logic

Logic related to application behavior, workflows, or user interaction should reside in Java.

Examples:

• Conditional UI-driven logic
• Role-based processing
• Dynamic behavior based on user actions

---

2. Entity Object Validations

ADF Entity Objects are ideal for row-level validations and business rules.

Examples:

• Field validation
• Attribute dependencies
• Default value logic

Why:

• Keeps validation close to the data model
• Integrates well with ADF lifecycle

---

3. Orchestration Logic

When multiple operations need to be coordinated, Java (especially in Application Modules) is the right place.

Examples:

• Calling multiple services or procedures
• Combining different business rules
• Managing application flow

---

4. Integration with External Systems

Any interaction with APIs, web services, or external systems should always be handled in Java.

Examples:

• REST/SOAP calls
• File processing
• Third-party integrations

What to Avoid

Do Not Put Everything in PL/SQL

• Makes the system rigid
• Harder to debug and maintain
• Reduces flexibility in application logic

---

Do Not Put Heavy Data Logic in Java

• Causes unnecessary database calls
• Slows down performance
• Increases network overhead

---

Avoid Duplicating Logic

• Same validation in both PL/SQL and Java leads to inconsistency
• Always define a single source of truth

---

Practical Decision Guide

Use this quick rule:

• Large data processing - PL/SQL
• Critical transactions - PL/SQL
• Shared logic across systems - PL/SQL
• UI-driven logic - Java
• Workflow and orchestration - Java
• External integrations - Java

---

Final Thought

PL/SQL and Java are not competitors - they are complementary.

A well-designed ADF application uses both effectively:

• PL/SQL for performance and data integrity
• Java for flexibility and application control

The goal is not to choose one over the other -
but to place each piece of logic where it naturally belongs.

PL/SQL Function

PL/SQL Function is same as PL/SQL Procedure, The only difference is that function must return a value and a procedure may or may not return a value

PL/SQL Procedure

Previously I have posted about PL/SQL Block structure , A piece of code that is organized in a properly defined sequence is called a block

PL/SQL provides two types of blocks

Function- A PL/SQL block that performs a task or set of tasks and returns a value

Procedure- A PL/SQL block that performs a task or set of tasks and may or may not return a value

PL/SQL Exceptions

An error that occurs during execution of the program is called exception, Like other programming languages, PL/SQL offers a way to catch these exceptions and handle them.

There are two types of exceptions in PL/SQL

1. System-Defined Exceptions 
2. User-Defined Exceptions 

PL/SQL For Loop

PL/SQL FOR loop is used when we need to execute set of statements for the specific number of times and loop operates between the start and end counter values. The counter is always incremented by one and once the counter reaches to end integer value, the loop terminates

The syntax of PL/SQL FOR Loop is like this

FOR counter_variable IN start value.. end value LOOP

statements to execute 

END LOOP;

PL/SQL While Loop

PL/SQL WHILE loop is used to execute statements as long as given condition is true and the condition is checked at the beginning of each iteration

The syntax of PL/SQL While loop is like this

WHILE condition

statements to execute

END LOOP;

PL/SQL Basic Exit Loop

In PL/SQL Basic Loop all statements inside the block are executed at least once before loop termination, Basic loop encloses statement between LOOP and END LOOP and there must be an EXIT or EXIT-WHEN condition to terminate the loop

The syntax of Basic Exit loop is like this

LOOP

statements to execute

EXIT; or EXIT-WHEN

END LOOP;

See these examples for better understanding

PL/SQL Loops , Iterative Statement in PL/SQL

Loops are used to repeat execution of a statement or a set of statements multiple times on base of a condition or expression
EXIT and EXIT-WHEN keywords are used to terminate the loop

EXIT- terminates the loop unconditionally and passes control to the next statement after the loop
EXIT-WHEN- terminates the loop when EXIT-WHEN clause is checked and if returns true then the loop is terminated and control is passed to next statement after the loop

The basic syntax of Loop in PL/SQL is like this

LOOP

Set of statements

END LOOP;

PL/SQL CASE Statement, Decision Making Statement in PL/SQL

Like real life in programming sometimes we need to execute some code on a specific condition, PL/SQL CASE statement allows us to execute a sequence of instructions based on a selector (A variable, function, expression etc)
and if selector value is equal to value or expression in WHEN clause then corresponding THEN clause will execute and process the statements

PL/SQL Conditions, IF-ELSE Conditional Statement

Like other programming languages, PL/SQL supports decision making statements, These statements are also called conditional statement

Basic Syntax of IF-ELSE is like this in PL/SQL

IF (Condition 1)

THEN

Statement to execute (if condition 1 is true)

ELSIF (Condition 2)

THEN 

Statement to execute (if condition 2 is true)

ELSE

Statement to execute (if condition 1& 2 both are false)

END IF;

For a better understanding of concept look at these examples

PL/SQL Variables and Constants

A variable in any programming language is the name of space where values are stored and controlled by our code/program

• We can not use reserve keyword as a variable name 
• Variable length should not exceed 30 characters
• The variable name consists of letters followed by other letters, dollar sign, underscore and numerals
• The variable name should be clear and easy to understand

Here we'll learn how to declare and initialize variables in PL/SQL

The basic syntax for declaring a variable in PL/SQL is following

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value] 

Here variable_name is the identifier of variable and datatype is a valid PL/SQL datatype. CONSTANT and DEFAULT are keywords used to define constants and set default values of variables

PL/SQL Basic Syntax, Block Structure and Anonymous block

PL/SQL is highly structured language and it's program units are written in the block of code, In this tutorial, we'll learn about basic syntax and the block structure of PL/SQL

A piece of code that is organized in a properly defined sequence is called a Block. A PL/SQL Block consists of 3 parts

DECLARE
<<declaration >>
--Declare Variables,Constants, Cursors and all elements

BEGIN
<<executable statements>>
--SQL, PL/SQL Commands 

EXCEPTION
<<exception handling>>
--Code to handle the exception

END;

PL/SQL Tutorial - What is PL/SQL, Features and Advantages of PL/SQL

PL/SQL is developed by Oracle Corporation to increase/enhance capabilities of SQL, PL/SQL stands for Procedural Language extension to SQL . PL/SQL is highly structured and expressive language and because of its expressive syntax it is very easy to understand and learn

PL/SQL is integrated with Oracle Database and can be called from any other programming language. It is tightly integrated with SQL so it's easy to learn PL/SQL if you have knowledge of SQL

Enable DBMS_OUTPUT in Oracle SQL Developer

I hope all of you'll be familiar with Oracle SQL Developer tool , A tool used by database developers to perform DB related tasks efficiently

DBMS_OUTPUT package of PL/SQL enables user to show/print some debugging information and used by learners to run and check small chunks of pl/sql code

Here we'll see how to enable DBMS_OUTPUT package in SQL Developer