Javainhand Tutorial | Learn Oracle APEX,PL/SQL,SQL

IF-THEN-ELSIF Statement in Oracle PLSQL with Examples

2026-05-11T22:35:00.001+05:30

Welcome Back to Javainhand Tutorial! In our previous tutorial, we covered the basics of IF-THEN-ELSE statement. Today We are going to explore a more powerful condition control structure: the IF-THEN-ELSIF Statement.

When you need to choose between more than two alternatives based on different conditions, the ELSIF ladder is the most efficient way to write your logic in PLSQL.

What is the IF-THEN-ELSIF Statement?

The IF-THEN-ELSIF statement allows you to check multiple conditions sequentially. It works like a decision-making chain.

It evaluates the first condition
If the first condition is FALSE, it moves to the next ELSIF block.
As soon as a condition evaluates to TRUE, the corresponding block of code is executed.
Once a match is found, all subsequent conditions are skipped, and control moves to the code after END IF.

Syntax of IF-THEN-ELSIF Statement in PLSQL

The syntax is straightforward. NOTE: In PLSQL, it is spelled ELSIF( without the 'E' in the middle).

IF condition1 THEN
   -- Action when condition1 is TRUE
ELSIF condition2 THEN
   -- Action when condition2 is TRUE
ELSIF condition3 THEN
   -- Action when condition3 is TRUE
ELSE
   -- Default action if NO conditions are TRUE
END IF;

Example of IF-THEN-ELSIF Statement in Oracle PLSQL

Practical Example: Employee Bonus Calculation

Let's take a real-world scenario where we calculate a bonus based on an employee's year of service:

More than 10 Years: 20% Bonus
More than 5 Years: 15% Bonus
More than 2 Years: 10% Bonus
Otherwise: 5% Bonus

PLSQL Code Example

DECLARE
   service_years NUMBER := 7;
   bonus_percent NUMBER;
BEGIN
   IF service_years > 10 THEN
      bonus_percent := 20;
   ELSIF service_years > 5 THEN
      bonus_percent := 15;
   ELSIF service_years > 2 THEN
      bonus_percent := 10;
   ELSE
      bonus_percent := 5;
   END IF;

   DBMS_OUTPUT.PUT_LINE('The eligible bonus is: ' || bonus_percent || '%');
END;
/

OUTPUT

The eligible bonus is: 15%

Key Points for Better Coding

Spelling Matters: Beginners often make the mistake of writing ELSEIF. Remember, Oracle uses ELSIF.
Efficiency: The compiler stops checking conditions as soon as it finds TRUE statement, making it faster than multiple independent IF blocks.
The ELSE Block: While the ELSE block as optional, it is recommended to include as a "catch-all" for scenarios that don't meet any specific criteria.

Video Tutorial

For a step-by-step visual guide and live coding demonstration of this topic, check out my YouTube Video.

Watch Video Tutorial Here

Conclusion

The IF-THEN-ELSIF Statement is an essential tool for any Oracle developer to handle complex login clearly and concisely.

I hope this tutorial helped you understand how to implement multiple conditions in your PLSQL blocks.

If you found this helpful, please share it with your friends and fellow learners. Have any questions? Drop then in the comment below! Happy Coding !

IF-THEN-ELSE Statement in Oracle PLSQL

2026-05-10T13:03:00.001+05:30

Hello Readers, This is another blog post of PLSQL Tutorial. In this Oracle PLSQL Tutorial, we will explain the IF-THEN-ELSE Statement in Oracle PLSQL with syntax and Examples.

IF-THEN-ELSE STATEMENT IN ORACLE SQL

In this post, we will discuss the below following topics:

What is the IF-THEN-ELSE Statement in Oracle PLSQL
Syntax of IF-THEN-ELSE Statement in Oracle PLSQL
Example of IF-THEN-ELSE Statement in Oracle PLSQL

What is the IF-THEN-ELSE Statement in Oracle PLSQL

In the Oracle, the if-then-else statement is control structure statement that provides two alternative path of execution.

If the Boolean Expression(condition) evaluates to TRUE, it executes the sequence of statement inside the THEN block. if condition evaluates to FALSE or NULL, it executes the sequence of statement inside the ELSE block

It is an extension of the simple IF-THEN Statement. By adding ELSE keyword, we guarantee that exactly one set of statement will execute based on the condition.

Syntax of IF-THEN-ELSE Statement in Oracle PLSQL

IF condition THEN 
-- sequence_of_statements if condition is TRUE 
ELSE -- sequence_of_statements if condition is FALSE or NULL 
END IF;

Example of IF-THEN-ELSE Statement in Oracle PLSQL

Let's understand the IF-THEN-ELSE Statement in Oracle PLSQL with a simple example where we check if given number is even or odd.

DECLARE 
   V_NUMBER NUMBER := 15; 
BEGIN 
   -- Checking if the number is divided by 2 
   IF MOD(V_NUMBER, 2) = 0 THEN 
      DBMS_OUTPUT.PUT_LINE('The Given Number ' || V_NUMBER || ' is an EVEN Number.'); 
   ELSE 
      DBMS_OUTPUT.PUT_LINE('The Given Number ' || V_NUMBER || ' is an ODD Number.'); 
   END IF; 
END;
/

OUTPUT

The Given Number 15 is an ODD Number.

Example 2: Comparing Two Number

DECLARE 
   V_NUM1 NUMBER := 50; 
   V_NUM2 NUMBER := 100; 
BEGIN 
   IF V_NUM1 > V_NUM2 THEN 
      DBMS_OUTPUT.PUT_LINE(V_NUM1 || ' is greater than ' || V_NUM2); 
   ELSE 
      DBMS_OUTPUT.PUT_LINE(V_NUM2 || ' is greater than ' || V_NUM1); 
   END IF; 
END;
/

That's all for today, I think (IF-THEN-ELSE Statement in Oracle PLSQL) post will be helpful for you. If you like the post then share your view in the comment box.

If you want to watch this topic then click here

Difference between %ROWTYPE and %TYPE in Oracle PLSQL

2025-04-30T15:09:00.000+05:30

In this Oracle Blog Post, We are sharing another PLSQL Interview question in Oracle PLSQL which is Difference Between %ROWTYPE and %TYPE in Oracle PLSQL.

Before going to understand %ROWTYPE and %TYPE in Oracle PLSQL, First we should understand VARIABLE in Oracle PLSQL.

UNDERSTANDING PLSQL VARIABLES

Variables are fundamental in PL/SQL. They hold data temporarily during program execution, allowing you to manipulate and store values across operations. But here's the catch—if the variable's data type doesn't match the actual column in the table, you might end up with errors or, worse, corrupt data handling.

That's where %TYPE and %ROWTYPE come into play. These attributes ensure that your variables are always consistent with the table or column they're derived from. Instead of manually specifying a data type (and risking mismatches), you let Oracle handle it for you. This makes your code not only cleaner but also future-proof—no need to rewrite your declarations if the schema changes.

What is %TYPE in PL/SQL?

%TYPE is a PL/SQL attribute used to declare a variable with the same data type as a specific table column or another variable. It's like telling Oracle, “Hey, I want this variable to act just like that column over there.”

Syntax of %TYPE in PLSQL

variable_name table_name.column_name%TYPE;

Use Case: You want to create a variable that stores a value from a specific column in a table, and you want to ensure the variable always has the same data type as that column, even if the column’s type changes later.

Real-world Scenario: You’re creating a stored procedure that needs to reference a customer’s email address. Rather than hardcoding the data type as VARCHAR2(100) (or whatever the length is), you simply say:

DECLARE 
customer_email customers.email%TYPE;

Now, if someone changes the column type in the future—say, extends the email field to 150 characters—your code will still work without modification.

Key Features of %TYPE

Let’s break down what makes %TYPE so valuable:

Automatic Data Type Inheritance: %TYPE ensures your variable always aligns with the current definition of the column it references. This eliminates data-type mismatches and enhances maintainability.
Error Reduction: No more guessing or mismatching data types between your code and the table. This helps avoid run-time errors and logic bugs.
Improved Maintainability: When the structure of a table changes (e.g., the column data type changes), you don’t need to update your PL/SQL code. Oracle takes care of that behind the scenes.

Example of %TYPE Usage

DECLARE
   emp_salary employees.salary%TYPE;
   emp_name employees.last_name%TYPE;
BEGIN
   SELECT salary, last_name INTO emp_salary, emp_name
   FROM employees
   WHERE employee_id = 101;

   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
   DBMS_OUTPUT.PUT_LINE('Salary: ' || emp_salary);
END;

Explanation:

Line 2-3: We declare two variables, emp_salary and emp_name, that will inherit the data types of salary and last_name columns from the employees table.
Line 5-7: We select the values from the table directly into these variables.
Line 9-10: We print the values using DBMS_OUTPUT.

If USER ever updates the data type of salary from NUMBER(8,2) to NUMBER(10,2) or changes last_name to a longer VARCHAR2, this code still works flawlessly. No edits needed.

What is %ROWTYPE in PLSQL?

Now that we’ve tackled %TYPE, let’s explore its big brother: %ROWTYPE. If %TYPE lets you mirror a single column’s data type, %ROWTYPE lets you grab the entire row’s structure. Think of it as a shortcut to declare a record that matches a whole table row, field by field.

Syntax of %ROWTYPE in PLSQL

record_variable table_name%ROWTYPE;

Use Case: You need to fetch or process an entire row from a table and don’t want to declare a separate variable for each column manually.

Real-world Scenario: Say you want to retrieve all the data for a specific employee from the employees table. Instead of declaring variables for employee_id, first_name, last_name, salary, and every other field, you just say:

DECLARE
   emp_record employees%ROWTYPE;

Boom! You’ve now got a single variable (emp_record) that contains every column from the employees table, each as a field within that record. It's like grabbing an entire row into one container.

Key Feature's of %ROWTYPE

Schema Synchronization: Just like %TYPE, if the table structure changes—say, a new column is added—your %ROWTYPE variable automatically includes it. No code changes required.
Perfect for Cursor Operations: %ROWTYPE works beautifully with explicit and implicit cursors. It simplifies row-by-row processing in loops.
Readable & Maintainable: One variable that reflects the entire row makes your code easier to read, understand, and maintain over time.

Example of %ROWTYPE

DECLARE
   emp_rec employees%ROWTYPE;
BEGIN
   SELECT * INTO emp_rec
   FROM employees
   WHERE employee_id = 101;

   DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id);
   DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
   DBMS_OUTPUT.PUT_LINE('Salary: ' || emp_rec.salary);
END;

Explanation:

Line 2: We declare emp_rec as a record based on the structure of the employees table.
Line 4: We select an entire row into the record using SELECT * INTO.
Line 6-8: We access individual fields using dot notation to print employee details.

This method is especially handy when dealing with tables that have lots of columns. Instead of juggling 10+ variables, one %ROWTYPE record handles everything gracefully.

Difference Between %TYPE and %ROWTYPE

Feature	%TYPE	%ROWTYPE
Scope	Single column	Entire row
Usage	Declares variable with same type as a column	Declares record matching row structure of table
Syntax	var_name table.column%TYPE	record_name table%ROWTYPE
Access Type	Direct variable	Dot notation (record.field)
Use in Cursors	Not suitable	Ideal for row-by-row cursor operations
Flexibility	Limited to one column	Adapts to table structure entirely
Readability	Good	Better for full-row operations
Maintenance	Easy if column type changes	Easier for dynamic row structure changes

Conclusion

In the world of PL/SQL, %TYPE and %ROWTYPE aren't just conveniences—they're essential tools that boost the robustness, maintainability, and clarity of your code. %TYPE offers a precise way to align variables with column definitions, while %ROWTYPE gives you a powerful mechanism to mirror full rows without the clutter of multiple declarations.

Here's the bottom line:

Use %TYPE when you only need one or two fields and want to stay tightly aligned with the column definitions.
Use %ROWTYPE when you want to handle or retrieve an entire row in one clean statement.
Combine both when your PL/SQL programs become more complex.

Both attributes promote best practices in PL/SQL by eliminating hardcoding, enhancing flexibility, and making your applications more resilient to schema changes. Whether you're building enterprise HR systems, e-commerce platforms, or core banking software, leveraging %TYPE and %ROWTYPE will make your code smarter, cleaner, and more adaptable.

Watch Video of %TYPE and %ROWTYPE in Oracle PLSQL.

Difference Between Implicit and Explicit Cursor in Oracle PLSQL

2025-04-29T14:35:00.003+05:30

In this Oracle Blog Post, We are sharing most asked interview question in Oracle PLSQL which is difference between Implicit and Explicit Cursor in Oracle PLSQL.

Implicit vs Explicit Cursor in Oracle PLSQL: Understanding the Difference

When working with Oracle databases and its procedural language extension, PL/SQL, you'll inevitably need to interact with data retrieved from SQL queries. Cursors are fundamental mechanisms that allow PL/SQL to handle the result sets of these queries, essentially acting as pointers to the memory area where the query results are processed.

Oracle provides two main types of cursors: Implicit Cursors and Explicit Cursors. Understanding when and how to use each is crucial for writing efficient and effective PL/SQL code. Let's dive into the details.

What is Cursor, Briefly?

At its core, a cursor is a pointer to a private SQL area, a region in memory allocated by Oracle to process SQL statements. This area contains information about the statement, including the parsed representation and, for queries, the result set (or a pointer to it). Cursors allow your PL/SQL code to process rows returned by a query, potentially one at a time.

Implicit Cursors: The Automatic Choice

Oracle automatically manages implicit cursors whenever you execute a SQL DML statement (like INSERT, UPDATE, DELETE) or a SELECT INTO statement that is expected to return only one row. You don't declare them; Oracle handles the opening, fetching, and closing behind the scenes.

Key Characteristics:

Automatic: Managed entirely by Oracle.
No Declaration: You don't write code to declare an implicit cursor.
Default Name: Oracle uses the default identifier SQL to refer to the most recently executed implicit cursor.
Use Cases: Primarily for DML statements and single-row SELECT INTO queries.

Cursor Attributes:

Even though they are managed implicitly, you can access information about the execution of the most recent SQL statement using cursor attributes prefixed with SQL%:

SQL%FOUND: Boolean. TRUE if the recent DML statement affected one or more rows, or if a SELECT INTO returned a row. FALSE otherwise.
SQL%NOTFOUND: Boolean. The logical opposite of SQL%FOUND. TRUE if the recent DML affected zero rows, or if SELECT INTO returned no rows.
SQL%ROWCOUNT: Number. Returns the number of rows affected by the most recent DML statement. For SELECT INTO, it's 1 if a row is found, 0 otherwise (though NO_DATA_FOUND exception is usually raised before you check if no row is found).
SQL%ISOPEN: Boolean. Always FALSE for implicit cursors because Oracle opens and closes them automatically around the statement execution.

Example 1: Using Implicit Cursor with UPDATE

Let's assume we have an EMPLOYEE table.

SQL:-

DECLARE
  v_dept_id employees.department_id%TYPE := 90; -- Example department ID
BEGIN
  -- Update the salary for employees in a specific department
  UPDATE employees
  SET salary = salary * 1.05
  WHERE department_id = v_dept_id;

  -- Check if any rows were updated
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Update successful. ' || SQL%ROWCOUNT || ' employee(s) updated in department ' || v_dept_id);
  ELSE
    DBMS_OUTPUT.PUT_LINE('No employees found in department ' || v_dept_id || '. No updates made.');
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Example 2: Using Implicit Cursor with SELECT INTO

This statement implicitly uses a cursor. Oracle expects exactly one row.

SQL:-

DECLARE
  v_employee_name employees.first_name%TYPE;
  v_salary        employees.salary%TYPE;
  v_employee_id   employees.employee_id%TYPE := 100; -- Example employee ID
BEGIN
  -- Retrieve details for a specific employee
  SELECT first_name, salary
  INTO v_employee_name, v_salary
  FROM employees
  WHERE employee_id = v_employee_id;

  -- If SELECT INTO succeeds, SQL%FOUND is TRUE and SQL%ROWCOUNT is 1
  -- We typically don't check SQL%FOUND here because exceptions handle the failure cases.
  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_name || ', Salary: ' || v_salary);

EXCEPTION
  WHEN NO_DATA_FOUND THEN -- Specific exception if no rows are returned
    DBMS_OUTPUT.PUT_LINE('Employee with ID ' || v_employee_id || ' not found.');
  WHEN TOO_MANY_ROWS THEN -- Specific exception if more than one row is returned
    DBMS_OUTPUT.PUT_LINE('Error: Multiple employees found with ID ' || v_employee_id || '.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Watch Out: The primary challenge with SELECT INTO (and implicit cursors) is that it must return exactly one row. If it returns zero rows, Oracle raises NO_DATA_FOUND. If it returns more than one row, Oracle raises TOO_MANY_ROWS. You need to handle these exceptions explicitly.

Explicit Cursors: Taking Control

When you need to process a query that might return multiple rows (or zero rows, without raising an error immediately), you need an explicit cursor. You, the developer, declare, open, fetch rows from, and close the cursor manually.

Key Characteristics:

Manual Control: You explicitly manage the cursor's lifecycle.
Declaration: Declared in the DECLARE section of your PL/SQL block using the CURSOR cursor_name IS select_statement; syntax.
User-Defined Name: You give the cursor a meaningful name.
Use Cases: Processing multi-row result sets, row-by-row processing logic.

The Lifecycle:

DECLARE: Define the cursor and associate it with a SELECT statement.

CURSOR c_employee_cursor IS

SELECT employee_id, first_name, salary

FROM employees

WHERE department_id = 90;

OPEN: Execute the query and populate the result set. The cursor now points before the first row.

OPEN c_employee_cursor;

FETCH: Retrieve the next row from the result set into variables. You typically do this inside a loop.

FETCH c_employee_cursor INTO v_emp_id, v_fname, v_salary;

CLOSE: Release the resources associated with the cursor. It's crucial to close cursors you open.

CLOSE c_employee_cursor;

Cursor Attributes:

Explicit cursors also have attributes, but you access them using the cursor's name:

cursor_name%FOUND: Boolean. TRUE if the most recent Workspace returned a row. FALSE otherwise. Useful for loop termination.
cursor_name%NOTFOUND: Boolean. The logical opposite of %FOUND. TRUE if the most recent Workspace did not return a row. Often used as the exit condition for a loop.
cursor_name%ROWCOUNT: Number. The total number of rows fetched so far.
cursor_name%ISOPEN: Boolean. TRUE if the cursor is open, FALSE otherwise.

Example: Using an Explicit Cursor

DECLARE
  -- Declare variables to hold fetched data
  v_emp_id    employees.employee_id%TYPE;
  v_fname     employees.first_name%TYPE;
  v_salary    employees.salary%TYPE;
  v_dept_id   employees.department_id%TYPE := 90; -- Department to query

  -- Declare the explicit cursor
  CURSOR c_employee_cursor IS
    SELECT employee_id, first_name, salary
    FROM employees
    WHERE department_id = v_dept_id
    ORDER BY salary DESC;

BEGIN
  -- Open the cursor
  OPEN c_employee_cursor;

  DBMS_OUTPUT.PUT_LINE('Employees in Department ' || v_dept_id || ':');

  -- Loop to fetch rows
  LOOP
    -- Fetch the next row into variables
    FETCH c_employee_cursor INTO v_emp_id, v_fname, v_salary;

    -- Exit the loop if no more rows are found
    EXIT WHEN c_employee_cursor%NOTFOUND;

    -- Process the fetched row (e.g., print it)
    DBMS_OUTPUT.PUT_LINE(' ID: ' || v_emp_id || ', Name: ' || v_fname || ', Salary: ' || v_salary);

    -- Optional: Check row count during processing
    -- DBMS_OUTPUT.PUT_LINE(' Rows fetched so far: ' || c_employee_cursor%ROWCOUNT);

  END LOOP;

  -- Check how many rows were processed in total after the loop
  DBMS_OUTPUT.PUT_LINE('Total employees processed: ' || c_employee_cursor%ROWCOUNT);

  -- Close the cursor (VERY IMPORTANT!)
  CLOSE c_employee_cursor;

EXCEPTION
  WHEN OTHERS THEN
    -- Ensure cursor is closed even if an error occurs mid-processing
    IF c_employee_cursor%ISOPEN THEN
      CLOSE c_employee_cursor;
    END IF;
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Implicit vs. Explicit: Quick Comparison

Feature	Implicit Cursor (SQL%)	Explicit Cursor (cursor_name%)
Declaration	Automatic	Manual (CURSOR c IS SELECT...)
Naming	Default (SQL)	User-defined
Management	Oracle (Automatic Open/Fetch/Close)	Developer (Manual Open/Fetch/Close)
Row Handling	Single Row (SELECT INTO), DML	Multi-row (or single/zero)
Error Handling	Raises NO_DATA_FOUND/TOO_MANY_ROWS for SELECT INTO	Requires checking %FOUND/%NOTFOUND
Code Verbosity	Low	High
Typical Use	DML, Single-row lookups	Row-by-row processing of multi-row sets

Conclusion

Use Implicit Cursors (via simple INSERT, UPDATE, DELETE, SELECT INTO) when dealing with DML or when you absolutely expect a query to return exactly one row. Be prepared to handle NO_DATA_FOUND and TOO_MANY_ROWS exceptions for SELECT INTO.
Use Explicit Cursors when you need to process multiple rows from a query result set, requiring fine-grained control over fetching each row.

IF STATEMENT in Oracle PLSQL

2025-04-26T21:58:00.005+05:30

Hello Readers, This is another blog of PLSQL Tutorial, In this Oracle PLSQL Tutorial, We will explain IF Statement in Oracle PLSQL.

In this post, we will discuss below following topic.

What is IF statements in Oracle PLSQL?
Type of IF Statements in Oracle PLSQL?
IF-THEN Statements in Oracle PLSQL
Syntax of IF-THEN Statements in Oracle PLSQL
Example of IF-THEN Statements in Oracle PLSQL

Discuss IF Statement in Oracle PLSQL

In Oracle PLSQL, IF statement is a control structure statement that allow you to executes or skips a sequence of statements depending on the value of Boolean expression.

Let's understand the meaning of Boolean Expression

An expression that returns the boolean value like true, false or null. Examples are comparisons for equality, greater-than, less-than.

Type of IF Statement in Oracle PLSQL

IF-THEN Statement
IF-THEN-ELSE Statement
IF-THEN-ELSIF

In this Post, we will proceed with IF-THEN Statement.

Let's Understand IF-THEN Statement

The simplest form of IF statements associates a condition with a sequence of statements enclosed by the keywords THEN and END IF;(Not ENDIF).

Syntax of IF-THEN Statement

IF condition THEN
 sequence_of_statements
END IF;

Example of IF-THEN Statement

DECLARE
V_NUMBER NUMBER:=10;

BEGIN

IF V_NUMBER IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('V_NUMBER VARIABLE VALUE IS'||' '||V_NUMBER);
END IF;

END;

That's all for today, I think(IF STATEMENT in Oracle PLSQL) post will be helpful for you. If you like the post then share your view in the comment box.

If you want to watch this topic then click here

Difference Between RANK and DENSE_RANK In Oracle SQL

2025-04-26T16:56:00.005+05:30

In this Oracle Blog Post, We are sharing most asked interview question in Oracle SQL which is difference between Rank and DENSE_RANK in Oracle SQL.

Discussion Topic

What is RANK() in Oracle SQL
What is DENSE_RANK() in Oracle SQL
Differentiate between RANK() and DENSE_RANK() in Oracle SQL.

When working with data in Oracle databases, especially for reporting or analysis, you often need to assign ranks to rows based on certain criteria. Maybe you want to find the top-performing salespeople, the highest-scoring students, or the most frequently ordered products.

Oracle provides powerful window functions for this purpose, with RANK() and DENSE_RANK() being two of the most common.
While they sound similar, their behavior, especially when dealing with tied values, is crucially different. Understanding this difference is key to getting accurate results. Let's dive in and clarify RANK() vs. DENSE_RANK() with a practical example.

The Scenario: Ranking Employee Sales Performance

Imagine we have a table called EmployeeSales that tracks the total sales amount for each employee in the last quarter.

-- Sample Table Structure
CREATE TABLE EmployeeSales (
    EmployeeID NUMBER,
    EmployeeName VARCHAR2(100),
    Region VARCHAR2(50),
    TotalSales NUMBER
);

-- Sample Data
INSERT INTO EmployeeSales VALUES (101, 'Alice', 'North', 95000);
INSERT INTO EmployeeSales VALUES (102, 'Bob', 'North', 80000);
INSERT INTO EmployeeSales VALUES (103, 'Charlie', 'South', 95000); -- Tie with Alice
INSERT INTO EmployeeSales VALUES (104, 'David', 'North', 75000);
INSERT INTO EmployeeSales VALUES (105, 'Eve', 'South', 88000);
INSERT INTO EmployeeSales VALUES (106, 'Frank', 'South', 80000); -- Tie with Bob

Our goal is to rank these employees based on their TotalSales in descending order (highest sales get the best rank).

Using the RANK() Function

The RANK() function assigns a rank to each row within a partition of a result set. If rows have the same value for the ordering columns (a tie), they receive the same rank. However, the next rank assigned will skip ranks based on the number of tied rows.

Syntax of RANK() Function

RANK() OVER ([PARTITION BY column1, ...] ORDER BY column2 [ASC|DESC], ...)

Let's apply RANK() to our sales data

SQL

SELECT
    EmployeeID,
    EmployeeName,
    Region,
    TotalSales,
    RANK() OVER (ORDER BY TotalSales DESC) as SalesRank
FROM
    EmployeeSales;

Output

EmployeeID	EmployeeName	Region	TotalSales	SalesRank
101	Alice	North	95000	1
103	Charlie	South	95000	1
105	Eve	South	88000	3
102	Bob	North	80000	4
106	Frank	South	80000	4
104	David	North	75000	6

Explanation

Alice and Charlie both have the highest sales (95000), so they both get Rank = 1.
Because there were two employees tied for rank 1, the next rank assigned is Rank = 3 (it skips rank 2). Eve gets rank 3.
Bob and Frank are tied with 80000 sales. They both receive the next available rank, which is Rank = 4.
Again, since two employees tied for rank 4, the next rank skips one place (rank 5 is skipped). David gets Rank = 6.

Note:-

The gaps in the ranking sequence (2 and 5 are missing). This is characteristic of RANK().

Using the DENSE_RANK() Function

The DENSE_RANK() function also assigns ranks to rows within a partition based on ordering column. Like RANK(), tied rows receive same rank . However, DENSE_RANK() assigns consecutive ranks without any gaps, regardless of ties

Syntax of DENSE_RANK() Function

DENSE_RANK() OVER ([PARTITION BY column1, ...] ORDER BY column2 [ASC|DESC], ...)

Let's apply DENSE_RANK() to our sales data

SQL

SELECT
    EmployeeID,
    EmployeeName,
    Region,
    TotalSales,
    DENSE_RANK() OVER (ORDER BY TotalSales DESC) as DenseSalesRank
FROM
    EmployeeSales;

Output

EmployeeID	EmployeeName	Region	TotalSales	DenseSalesRank
101	Alice	North	95000	1
103	Charlie	South	95000	1
105	Eve	South	88000	2
102	Bob	North	80000	3
106	Frank	South	80000	3
104	David	North	75000	4

Explanation

Alice and Charlie both have the highest sales (95000), so they both get DenseRank = 1.
Unlike RANK(), DENSE_RANK() assigns the very next rank to the next distinct sales amount. Eve (88000) gets DenseRank = 2.
Bob and Frank (80000) are tied for the next rank, so they both get DenseRank = 3.
David (75000) gets the next consecutive rank, DenseRank = 4.

Note:-

The DenseSalesRank column has no gaps; the ranks are consecutive (1, 2, 3, 4).

Difference Between RANK() and DENSE_RANK() in Oracle SQL

1. Handling Ties:

RANK: Assigns the same rank to tied rows. The subsequent rank number reflects the total number of preceding rows (including all tied rows), causing gaps in the sequence.
DENSE_RANK: Assigns the same rank to tied rows. The subsequent rank number is always the next consecutive integer, ensuring no gaps in the sequence.

2. Rank Sequence:

RANK: Can produce a non-consecutive sequence of ranks if ties exist (e.g., 1, 1, 3, 4, 4, 6).
DENSE_RANK: Always produces a consecutive sequence of ranks (e.g., 1, 1, 2, 3, 3, 4).

3. Use Cases:

RANK: Often used in competitive scenarios where the rank reflects the absolute position, including ties (like Olympic medal standings where two golds mean no silver is awarded immediately after). Useful when you need to know exactly how many rows are ranked above a given row.
DENSE_RANK: Ideal when you need a gapless ranking, often used for finding the "Nth" distinct value (e.g., find the employees with the 3rd highest distinct salary level). Useful when the exact count of preceding rows is less important than having a sequential rank identifier for each distinct value group.

Conclusion

Both RANK() and DENSE_RANK() are invaluable tools in your Oracle SQL toolkit for ordering and analyzing data. The choice between them depends entirely on how you want to handle ties and whether you require a consecutive ranking sequence. By understanding their distinct behaviors, you can ensure your queries produce meaningful and accurate results for your specific analytical needs.

Control Statement in Oracle PLSQL - Javainhand

2025-01-21T16:53:00.004+05:30

Hello Readers, This is another PLSQL Tutorial blog. In this blog we will discuss Control Statement in Oracle PLSQL.

Control Statements in Oracle PLSQL: Making Decisions in Your Code

Control Statements are elements in a program that controls the flow of program execution.

Let's understand with example, Imagine you're cooking. You follow a recipe, but sometimes you need to make choices. "If" the sauce is too thick, you add water. "For" each ingredient, you measure a specific amount. These choices are like "control statements" in PL/SQL – they help your code make decisions and execute different parts based on conditions.

1. IF Statement

• The "If" King: This is the most basic control statement. It checks if a condition is true.

Example:-

IF age > 18 THEN
   DBMS_OUTPUT.PUT_LINE('You are an adult.'); 
END IF;

• Explanation:-

o If the variable "age" is greater than 18, the message "You are an adult." will be displayed.

o If "age" is not greater than 18, nothing happens.

2. CASE Statement

• The "Multiple Choice" Master: When you have more than two choices, the CASE statement is your friend.

CASE 
    WHEN grade = 'A' THEN
        bonus := 1000;
    WHEN grade = 'B' THEN
        bonus := 500;
    ELSE
        bonus := 0;
END CASE;

• Explanation:

o If "grade" is 'A', the "bonus" variable gets 1000.

o If "grade" is 'B', the "bonus" gets 500.

o If "grade" is anything else, the "bonus" gets 0.

3. While Loop

• The "Conditional Repeater": This loop checks a condition before each iteration.

Example:-

WHILE age < 18 LOOP
    -- Some code to be executed 
    age := age + 1; 
END LOOP;

• Explanation:

o The code within the loop will execute as long as "age" is less than 18.

o In each iteration, the value of "age" is increased by 1.

4. FOR Loop

• The "Counter": This loop is perfect for repeating something a specific number of times.

Example:-

FOR i IN 1..10 LOOP
    -- Some code to be executed 
END LOOP;

• Explanation:

o The code within the loop will execute 10 times, with the variable "i" taking values from 1 to 10 in each iteration.

That's all for today, I think(Control Statement in Oracle PLSQL) post will be helpful for you. If you like the post then share your view in the comment box.

If you want to watch this topic then click here

%ROWTYPE in Oracle PLSQL with Example

2025-01-11T22:11:00.005+05:30

In this Post, We are going to explore %ROWTYPE in Oracle PL/SQL.

%ROWTYPE in Oracle PL/SQL: A Simplified Guide

In Oracle PLSQL, the %ROWTYPE attribute is a powerful tool that simplifies your code by allowing you to easily work with entire rows of data from a table.

What is %ROWTYPE?

Imagine a table in your database. Each row in that table represents a record with multiple columns (like name, age, address, etc.).

%ROWTYPE lets you create a variable that can hold the complete structure of a single row from that table.

Why use %ROWTYPE?

• Reduced Code: Instead of declaring individual variables for each column, you declare a single variable to hold the entire row. This significantly reduces code redundancy

• Improved Readability: Code becomes more concise and easier to understand, as you're dealing with rows as a whole.

• Flexibility: You can easily modify the structure of the variable if the table's structure changes, as %ROWTYPE automatically adapts.

Advantages of %ROWTYPE

• Efficiency: Reduces coding time and effort.

• Maintainability: Easier to modify code when table structures change

• Readability: Improves code clarity and understandability.

Syntax of %ROWTYPE

variable_name table_name%ROWTYPE;

Example of %ROWTYPE

Let's say you have a table named "EMPLOYEES" with columns like "EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "SALARY". Here's how you can use %ROWTYPE:

DECLARE
  v_employee EMPLOYEES%ROWTYPE; 
BEGIN
  -- Fetch a row from the EMPLOYEES table
  SELECT * 
  INTO v_employee 
  FROM EMPLOYEES 
  WHERE EMPLOYEE_ID = 100;
 -- Access individual columns within the row
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee.FIRST_NAME || ' ' || v_employee.LAST_NAME);
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_employee.SALARY); 
END;
/

In this example:

1. v_employee EMPLOYEES%ROWTYPE; declares a variable named v_employee that can hold a complete row from the "EMPLOYEES" table.

2. The SELECT ... INTO statement fetches a row from the table and stores it in the v_employee variable.

3. You can then access individual columns within the row using dot notation (e.g., v_employee.FIRST_NAME).

That's all for today, this simplified explanation of %ROWTYPE in Oracle PLSQL is helpful. If you like the post then share your view in the comment box.

You could also watch %rowtype in oracle plsql with example click here to watch

%TYPE in Oracle PL/SQL with Example

2024-12-28T15:33:00.010+05:30

In this Post, We are going to explore %TYPE in Oracle PL/SQL.

What is %TYPE in Oracle PLSQL?

%Type is a special data type in PLSQL which is used for declaring variables, fields, and parameters with the same data type as an existing database column or another variable. This mechanism enhances code maintainability and reduces the risk of data type-related errors.
%Type is also called dynamic datatype.

Syntax of %TYPE in Oracle PLSQL

v_identifier table.column_name%type;

Where:

v_identifier: The name you want to give to your variable.
table.column_name: The name of the column or variable whose data type you want to inherit from table.

Example of %TYPE in Oracle PLSQL

Let's say you have a table named EMPLOYEES with a column EMPLOYEE_ID of type NUMBER.

DECLARE

v_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;

BEGIN

-- ... your PL/SQL code ...

END;

In this example, the variable v_employee_id will automatically have the same data type as the EMPLOYEE_ID column in the EMPLOYEES table.

Real-World Use Case:

Imagine you're writing a procedure to update an employee's salary. Using %TYPE ensures that the variable holding the new salary has the exact same data type as the SALARY column in the EMPLOYEES table, preventing potential data type mismatch errors.

CREATE OR REPLACE PROCEDURE update_employee_salary (

p_employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,

p_new_salary IN EMPLOYEES.SALARY%TYPE

)

BEGIN

UPDATE EMPLOYEES

SET SALARY = p_new_salary

WHERE EMPLOYEE_ID = p_employee_id;

END;

Advantages of %TYPE in Oracle PLSQL

The primary benefit of using %TYPE lies in maintainability. If the data type of the referenced column ever changes (e.g., from NUMBER to DECIMAL), all variables declared with %TYPE based on that column will automatically inherit the new data type. This eliminates the need to manually update multiple variable declarations, significantly reducing the risk of errors and making your code more robust and adaptable to schema changes.

That's all for today, I hope this (%TYPE in Oracle PL/SQL with Example) post will be helpful for you. If you like the post then share your view in the comment box.

Show Image Instead of Icon in Oracle APEX Region Header- Javainhand Tutorial

2023-02-11T13:44:00.000+05:30

Show Image Instead of Icon in Oracle APEX Region Header: A Comprehensive Guide

Learn how to replace the standard icons in the Oracle APEX Region header with custom images in a few simple steps.

Step 1: Upload the Image to the Shared Components

The first step in this process is to upload the image that you want to display in the Region header. To do this, you need to access the Shared Components section of the Oracle APEX workspace and select the Static Application Files option. Next, click on the Create button and select the Image option. Finally, upload the image file and give it a meaningful name.

Step 2: Copy Existing Standard Region Template with New Name

Second step in this process is to copy existing standard region template with new name. To do this, You have to go in the shared components and find template section then find standard region template and Click on the copy icon and Save with the new name.

Step 3: Find the New Template and Done the Below Changes.

Third step in this process is to find the new template which have we created and click on the that template then go to the definition and find (<span class="t-Region-headerIcon"><span class="t-Icon #ICON_CSS_CLASSES#" aria-hidden="true"></span></span>) this line of statement. After that we have to change with the below statement and Save your template.

<span class="t-Region-headerIcon"><img src="#ICON_CSS_CLASSES#" aria-hidden="true"></img></span>

Step 4: Use the new template in your page.

Fourth step in this process is to create a new region and Change region template with new template then Go to template option and Enable the show region icon. After that paste your IMAGE URL in the ICON section. Please check the below screenshot.

Output

Conclusion: Showing an image instead of an icon in the Oracle APEX Region header is a simple and straightforward process that can enhance the visual appeal of your application. By following the steps outlined in this article, you can easily replace the default icon with a custom image in a few minutes. If you have any questions or concerns, please drop the comment in the comment section for additional information.

Procedure Related Interview Questions in Oracle PL/SQL

2022-11-23T18:56:00.004+05:30

In this Oracle PL/SQL Tutorial, We are sharing some Procedure Related Interview Questions in Oracle PL/SQL.

We hope this questions will help you in your Oracle PL/SQL Interview. In this post we are sharing most asked questions related to Procedure in Oracle PL/SQL.

Q1. What is Procedure in Oracle PL/SQL?
Q2. Syntax of Procedure in Oracle PL/SQL?
Q3. Example of Procedure in Oracle PL/SQL?
Q4. For which case, we used to Procedure in PL/SQL.
Q5. Can we use Out or InOUT Parameter in Procedure  in PL/SQL and For which Purpose, we use OUT and InOut Parameter?
Q6. Can we use RETURN Keyword in Procedure and What is the purpose of RETURN Keyword?
Q7. Can we use function in the procedure in PL/SQL?
Q8. Can we write DDL process in Procedure in PL/SQL? If yes then how?
Q9. Can we use Procedure as an Assignment Statement or Boolean Expression or SQL Statement?

That's all for today, I hope this (Procedure Related Interview Questions in Oracle PL/SQL.) post will be helpful for you. If you want to know the answer then drop your comments in comment section.

Function Related Interview Questions in Oracle PL/SQL

2022-11-21T13:29:00.008+05:30

In this Oracle PL/SQL Tutorial, We are sharing some Function Related Interview Questions in Oracle PL/SQL.

We hope this questions will help you in your Oracle PL/SQL Interview. In this post we are sharing most asked questions related to Function in Oracle PL/SQL.

Q1. What is Function in Oracle PL/SQL?
Q2. Syntax of Function in Oracle PL/SQL?
Q3. Example of Function in Oracle PL/SQL?
Q4. For which case, we used to Function in PL/SQL.
Q5. Can we use Out or InOUT Parameter in Function in PL/SQL?
Q6. Without using RETURN Keyword, can we create Function in PL/SQL?
Q7. Can we return multiple values through Function in PL/SQL? If yes then how?
Q8. Can we write DML process in Function in PL/SQL? If yes then how?
Q9. Can we write DDL process in Function in PL/SQL? If yes then how?
Q10. Can we use function as an Assignment Statement or Boolean Expression or SQL Statement?

That's all for today, I hope this (Function Related Interview Questions in Oracle PL/SQL.) post will be helpful for you. If you want to know the answer then drop your comments in comment section.

Preview Multiple Images Before Upload Using JavaScript in Oracle APEX

2022-06-26T11:51:00.003+05:30

In this Oracle APEX Tutorial, We will explore the Preview Multiple Images Before Upload Using JavaScript in Oracle APEX.

To Preview Multiple Images Before Upload using JavaScript in Oracle APEX

Step 1:- Create a Region with file Browse Page Item. See the below screenshot for the better understanding.

Step 2:- Create one more Region for Preview Images using below HTML code. See the below screenshot for the better understanding.

Step 3:- Use the below JavaScript code in the page function and Global Variable Declaration Section.

window.onload = function () {
    var fileUpload = document.getElementById("P33_UPLOAD");
    fileUpload.onchange = function () {
        if (typeof (FileReader) != "undefined") {
            var dvPreview = document.getElementById("image_preview");
            dvPreview.innerHTML = "";
            var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.jpg|.jpeg|.gif|.png|.bmp)$/;
            for (var i = 0;i<fileUpload.files.length; i++) {
                var file = fileUpload.files[i];
                if (regex.test(file.name.toLowerCase())) {
                    var reader = new FileReader();
                    reader.onload = function (e) {
                        var img = document.createElement("IMG");
                        img.height = "100";
                        img.width = "100";
                        img.src = e.target.result;
                        dvPreview.appendChild(img);
                    }
                    reader.readAsDataURL(file);
                } else {
                    alert(file.name + " is not a valid image file.");
                    dvPreview.innerHTML = "";
                    return false;
                }
            }
        } else {
            alert("This browser does not support HTML5 FileReader.");
        }
    }
};

Step 4:- Use below CSS code in the page inline section which added the space between the two or more images.

#image_preview img
{
    display: inline-block;
    margin: 0px 10px;
}

That's all for today, I hope this (Preview Multiple Images Before Upload Using JavaScript in Oracle APEX) post will be helpful for you. If you like the post then share your view in the comment box.

DEMO URL

Reference URL

Difference Between Function and Procedure in PL/SQL

2022-02-10T16:20:00.008+05:30

In this Tutorial, We will explore the Difference Between Function and Procedure in PL/SQL.

What is Difference Between Function and Procedure in Oracle PL/SQL

Function:-

Function must always return a value.
Function can be used in SQL statement.
Function used for computational purpose.
We can not write directly DML statements in Function(Using Autonomous transaction we can write DML statement in Function).
In function, We use RETURN keyword for return the value.

Procedure:-

Procedure can not return value but using IN and INOUT parameter procedure can return the value.
We can not run procedure in SQL statement.
Procedure is basically use for the bussiness logic.
In procedure, we can write DML statement.
In procedure, We can use RETURN keyword also but the behaviour of RETURN keyword is change in the procedure. RETURN keyword means you want to exit to program.

That's all for today, I hope this (Difference Between Function and Procedure in Oracle PL/SQL) post will be helpful for you. If you like the post then share your view in the comment box.

DECODE() Function in Oracle

2022-01-23T21:55:00.006+05:30

In this Oracle SQL Tutorial, We will expolre the DECODE() function in Oracle.

What is DECODE() Function in Oracle?

Oracle DECODE is a function in Oracle which help to implement if-then-else logic in SQL query.

Oracle DECODE is use for transform the data to one value to another value.

Oracle DECODE only use for equality check logic in Oracle SQL.

Oracle DECODE function is used in different database versions like Oracle 9i,Oracle 10g,Oracle 11g and Oracle 12c.

Syntax of DECODE() function in Oracle

DECODE(value,search_value,result,default_value)

Let's understand the syntax so we can easily use the DECODE() function in Oracle SQL.

In the DECODE() function, We pass four parameter. The first parameter is your main value or it can be your table value.Second parameter is search value which mean that second parameter value matchs with the first parameter value if it is match then third parameter return their value otherwise fourth parameter returns their value.

If it is not clear the let's understand with the DECODE() Examples.

Examples of DECODE() Function in Oracle

Example 1:-

SELECT DECODE('SQL','SQL','SQL TUTORIAL','JAVAINHAND TUTORIAL') AS DECODE_EXAMPLE_1 FROM DUAL

In this Example, I am showing when first parameter match or equal to with the second parameter then we found third parameter as return output.

Example 2:-

SELECT DECODE('SQL','SQL2','SQL TUTORIAL','JAVAINHAND TUTORIAL') AS DECODE_EXAMPLE_2 FROM DUAL

In this Example, I am showing when first parameter not match or not equal to with the second parameter then we found fourth parameter as return output.

NOTE:-

1. We can run DECODE function with three parameter like DECODE(value,search_value,result).

SELECT DECODE('SQL','SQL','SQL TUTORIAL') AS DECODE_EXAMPLE_3 FROM DUAL

2. In DECODE() function if null value match with null value then it's return true.

SELECT DECODE('','','SQL TUTORIAL') AS DECODE_EXAMPLE_4 FROM DUAL

That's all for today, I think(DECODE() Function in Oracle) post will be helpful for you. If you like the post then share your view in the comment box.

NVL2() Function in Oracle

2022-01-13T17:11:00.007+05:30

In this SQL Tutorial, We will explore the NVL2() Function in Oracle.

What is NVL2() function in Oracle?

NVL2() is another way to handling NULL value in Oracle.
It is advanced version of NVL function.
NVL2() function have functionality to handle both NULL Values and NOT NULL Values in Oracle.

Syntax of NVL2() function in Oracle

nvl2(value_to_check,value_to_not_null,value_to_null);

In NVL2() function used three parameter first parameter check the value if it return not null values then second parameter executed if first parameter return null values then third parameter executed.

Example of NVL2() function in Oracle

SELECT NVL2('Javainhand Tutorial','found','Not Found') as nvl2_example FROM DUAL;

SELECT NVL2('','found','Not Found') as nvl2_example FROM DUAL;

That's all for today, I think(NVL2() Function in Oracle) post will be helpful for you. If you like the post then share your view in the comment box.

5 Methods to Set Oracle APEX Page Item Value

2022-01-08T14:37:00.008+05:30

In this Oracle APEX Tutorial Session, We will explore the 5 Different Methods to Set Oracle APEX JavaScript Page Item Value.

5 Different Ways to Set Oracle APEX Page Item Value using Javascript

$("#P28_SECOND" ).val(20);
$x("P28_SECOND").value = 30;
document.getElementById('P28_SECOND').value=40;
apex.item('P28_SECOND').setValue(50);
apex.items.P28_SECOND.value=60;

I hope the above JavaScript Code will help you out to Set Oracle APEX page item values with different methods. If you learn something for this blog then please share this post to your social sites and others plateforms.

5 Methods to Get Oarcle APEX Page Item Value

2022-01-06T13:55:00.005+05:30

In this Oracle APEX Tutorial Session, We will explore the 5 Different Methods to Get Oracle APEX JavaScript Page Item Value.

5 Different Ways to Get Oracle APEX Page Item Value using Javascript

$("#P28_FIRST" ).val();
$v('P28_FIRST');
document.getElementById('P28_FIRST').value;
apex.item('P28_FIRST').getValue();
apex.items.P28_FIRST.value;

I hope the above JavaScript Code will help you out to get Oracle APEX page item values with different methods. If you learn something for this blog then please share this post to your social sites and others plateforms.

NVL() Function in Oracle

2021-12-25T18:04:00.003+05:30

In this SQL Tutorial, We will explore the NVL() Function in Oracle.

What is NVL() Function in Oracle?

NVL() is used to replace NULL string with another string or you can say in the simple words that NVL is a function for handling NULL values in the table.

Syntax of NVL() function in Oracle

NVL(Argument1,Argument2)

Example of NVL() function in Oracle

select NVL(SAL,0) from EMP

Let's compare, When we are not using NVL() function in the SQL statement and when we are using the NVL() function in SQL statement. The below image will show the difference to you.

Image 1

Image 2

Important Note:-

1)In the NVL function we have two arguments and both are required.if you missed any arguments in the NVL() function the oracle will return a syntax error(ORA-00936: missing expression).

Select NVL(,0) from dual---ORA-00936: missing expression--

2)In the NVL function, we have same datatype values otherwise oracle will return an error(ORA-01722: invalid number).

Select NVL(0,'A') from dual

3) If you don't want to above error the convert values in the same datatype.

Select NVL(to_char(0),'A') from dual

That's all for today, I think(NVL() Function in Oracle with Example) post will be helpful for you. If you like the post then share your view in the comment box.

PL/SQL Program to Print Half Pyramid Using Numbers-Javainhand Tutorial

2021-11-08T23:05:00.003+05:30

In this PL/SQL Tutorial, We will explore how to Print Half Pyramid Using Numbers in PL/SQL.

PL/SQL Code:-

declare
v_number number:=5;
begin
    for i in 1..v_number
    loop
            for j in 1..i
            loop
            dbms_output.put(j);
            end loop;
    DBMS_OUTPUT.NEW_LINE;
    end loop;
end;

That's all for today, I think(Print Half Pyramid Using Numbers in PL/SQL Program) post will be helpful for you. If you like the post then share your view in the comment box.

PL/SQL Program to Print the Inverted Right Triangle Star Pattern- Javainhand Tutorial

2021-11-08T14:31:00.005+05:30

In this PL/SQL Tutorial, We will explore how to print inverted right triangle start pattern in PL/SQL.

PL/SQL Code:-

declare
v_number number:=5;
begin
    for i in 1..v_number
    loop
            for j in 1..v_number-i+1
            loop
            dbms_output.put('*');
            end loop;
    DBMS_OUTPUT.NEW_LINE;
    end loop;
end;

That's all for today, I think(Print the Inverted Right Triangle Star Pattern in PL/SQL Program) post will be helpful for you. If you like the post then share your view in the comment box.

PL/SQL Program To Print Rectangular Star Pattern Part 2 - Javainhand Tutorial

2021-11-07T15:33:00.004+05:30

In this Tutorial, We will learn to make Rectangular Star in PL/SQL Program.

PL/SQL Code:-

declare
v_number number:=5;
begin
    for row in 1..v_number
    loop
            for col in 1..v_number 
            loop
            dbms_output.put('*');
            end loop;
    DBMS_OUTPUT.NEW_LINE;
    end loop;
end;

That's all for today, I think( Print Rectangular Star Pattern in PL/SQL Program) post will be helpful for you. If you like the post then share your view in the comment box.

PL/SQL Program To Print Star Pattern Part 1 - Javainhand Tutorial

2021-11-07T15:20:00.004+05:30

In this Tutorial, We will learn to make Star Triangle in PL/SQL Program.

PL/SQL Code:-

declare
v_number number:=5; //Pass Your Number
begin
    for row in 1..v_number
    loop
            for col in 1..row 
            loop
            dbms_output.put('*');
            end loop;
    DBMS_OUTPUT.NEW_LINE;
    end loop;

end;

That's all for today, I think( Print Star Pattern in PL/SQL Program) post will be helpful for you. If you like the post then share your view in the comment box.

How To Generate Text File in Oracle - Javainhand Tutorial

2021-10-21T22:15:00.006+05:30

In this Session, We will explore the Generate a Text File using UTL_FILE in Oracle PL/SQL.

The Following Steps to be needed for Generating Text file in Oracle.

Create Directory
Create Function (CLOB TO BLOB in Oracle)
Create Procedure for Generate TEXT file to the System Directory

How to Generate a Text File using UTL_FILE in Oracle PL/SQL

Step 1:- Create a Folder to your local system. In my case, I have created an image folder to E directory. The following screenshot will help.

Step 2:-Open SQL Developer or CMD connect to the database with SYS User and RUN the following commands.

create directory upload_image_dir as 'E:\upload_images';
grant read or write on directory upload_image_dir  to anonymous;
grant read or write on directory upload_image_dir  to admin;

Step3:- Create a function which convert clob data to blob file in Oracle. Use the following function.

create or replace FUNCTION clob_to_blob (p_data  IN  CLOB)
  RETURN BLOB
AS
  l_blob         BLOB;
  l_dest_offset  PLS_INTEGER := 1;
  l_src_offset   PLS_INTEGER := 1;
  l_lang_context PLS_INTEGER := DBMS_LOB.default_lang_ctx;
  l_warning      PLS_INTEGER := DBMS_LOB.warn_inconvertible_char;
BEGIN

  DBMS_LOB.createtemporary(
    lob_loc => l_blob,
    cache   => TRUE);

  DBMS_LOB.converttoblob(
   dest_lob      => l_blob,
   src_clob      => p_data,
   amount        => DBMS_LOB.lobmaxsize,
   dest_offset   => l_dest_offset,
   src_offset    => l_src_offset, 
   blob_csid     => DBMS_LOB.default_csid,
   lang_context  => l_lang_context,
   warning       => l_warning);

   RETURN l_blob;
END;

Step 4:- Create a SaveFile Procedure. Use the Following Procedure

create or replace PROCEDURE SaveFile(
     FileContent IN OUT NOCOPY BLOB
   , FolderName IN VARCHAR2
   , FileName IN VARCHAR2)
IS
    BUFFER RAW(1024);
    OFFSET PLS_INTEGER := 1;
    FileLength PLS_INTEGER;
    amount PLS_INTEGER := 1024;
    fhandle UTL_FILE.FILE_TYPE;
BEGIN
    FileLength := DBMS_LOB.GETLENGTH(FileContent);
    fhandle := UTL_FILE.FOPEN(FolderName, FileName,'wb', 32767);         
    LOOP
        EXIT WHEN OFFSET > FileLength;
        DBMS_LOB.READ(FileContent, amount, OFFSET, BUFFER);
        UTL_FILE.PUT_RAW(fhandle, BUFFER, TRUE);
        OFFSET := OFFSET + amount;
    END LOOP;
    UTL_FILE.FCLOSE (fhandle);
EXCEPTION
    WHEN OTHERS THEN
        IF UTL_FILE.IS_OPEN(fhandle) THEN
            UTL_FILE.FCLOSE(fhandle);
        END IF;
    RAISE;
END SaveFile;

Step 5:- Create a GENERATE_FILE procedure. Use following procedure.

create or replace PROCEDURE GENERATE_FILE
(P_TEXT IN VARCHAR2,P_DIRECTORY_NAME IN VARCHAR2, P_FILE_NAME IN VARCHAR2)
AS
V_FILE BLOB;
V_FILENAME VARCHAR2(500):=P_FILE_NAME||'.txt';
V_CLOB CLOB:=P_TEXT;
BEGIN
		BEGIN
		SELECT CLOB_TO_BLOB(V_CLOB) INTO V_FILE FROM dual;
		EXCEPTION WHEN OTHERS THEN
		V_FILE:=NULL;
		END;
        
	    IF V_FILE IS NOT NULL AND V_FILENAME IS NOT NULL THEN
		SAVEFILE(V_FILE,P_DIRECTORY_NAME,V_FILENAME);
		ELSE
		dbms_output.put_line('Text File Not Generate');
		END IF;
END;

Step 6:- Now call GENERATE_FILE procedure with the parameters. Use the following code.

BEGIN
GENERATE_FILE('This is text file','upload_image_dir','test');
END;

That's all for today, I think(Generate Text File in Oracle) post will be helpful for you. If you like the post then share your view in the comment box.

Please support our youtube channel so we will upload more Oracle APEX Tutorial videos and blogs.

Flip Region in Oracle APEX - Javainhand Tutorial

2021-10-08T15:55:00.015+05:30

In this Oracle APEX Tutorial, We will explore the Flip Region in Oracle APEX.

Actually I have received a comment on my flip region video which is uploaded on my youtube channel. One of our subscribers told us that this is not working so I have decided to write a post about Oracle APEX Flip Region.

How to Flip Region in Oracle APEX 21.1

You can try the following steps to Flip Region using Jquery in Oracle APEX.

Step 1:- Make a simple screen design which I have shared below.

Step 2:- Add the following Javascript library in your page file URLS section.

https://code.jquery.com/jquery-2.1.4.min.js
https://cdn.rawgit.com/nnattawat/flip/master/dist/jquery.flip.min.js

Step 3:- Add a static id (card) in the parent region. The following screenshot will help for better understanding.

Step 4:- Add (front) class to column CSS classes in the first subregion and Add (back) class to column CSS classes in second sub region. The following screenshot will help for better understanding.

Step 5:- Create click dynamic action with execute javascript code true action on both buttons.

Event:-Click
Selection Type:-Button
Button:-GO_TO_CHILD_2
Identification:- Execute JavaScript Code
Code:-$("#card").flip('toggle');

Step 6:- Follow step 5 again for the second dynamic action on the second button with the same JavaScript code.

Step 7:- Add the following JavaScript Code in Function and Global Variable Declaration Section.

$("#card").flip({
  trigger: 'manual'
});

That's all for today, I think(Flip Region in Oracle APEX) post will be helpful for you. If you like the post then share your view in the comment box.

Please support our youtube channel so we will upload more Oracle APEX Tutorial videos and blogs.

Click here for demo

Reference:-

http://nnattawat.github.io/flip/