Category PLSQL

PL/SQL (Procedural Language for SQL) is Oracle’s proprietary extension to standard SQL. It combines the data manipulation power of SQL with the procedural capabilities of a programming language. PL/SQL allows developers to write complex database-centric applications by incorporating variables, conditions, loops, error handling, and modular programming constructs like procedures and functions. This language is tightly integrated with Oracle databases, enabling efficient data processing and transaction management.

How to Compare Two Database Objects in Oracle?

In this tutorial, I am giving an example to compare two database table objects of different schemas using the DBMS_COMPARISON utility package in Oracle. Steps to Compare Two Table Objects in Oracle Using DBMS_COMPARISON Step-1 Create the comparison using DBMS_COMPARISON. In…

How to Get Current Date in PL/SQL?

The following are the examples to get the current date in PL Examples To Get The Current Date in PL/SQL 1. Using Sysdate Function SET SERVEROUTPUT ON; DECLARE d_current_date DATE; BEGIN d_current_date := SYSDATE; DBMS_OUTPUT.put_line (‘The Date today is: ‘…

Resolve PLS-00323 Error in Oracle

The reason for the PLS-00323 error in Oracle is a mismatch in procedure or function declaration in the package specification and the package body. To resolve this take the following actions. Resolve PLS-00323 Error in Oracle Check the package specification…

How to Create Function in PL/SQL?

To create a function in PL/SQL, use CREATE OR REPLACE FUNCTION statement. Below are the syntax details and an example. Syntax CREATE [OR REPLACE] FUNCTION function_name [(parameters)]  Return data_type is /* declare variables here */ Begin /* write program logic…

How to Split a String in PL/SQL?

In this article, I am giving an example of a function which I mostly use to split a string in PL Especially, when I am writing a program to import a delimited file. Here are that function and an example…

Java in Oracle Database Example

In this tutorial, I am giving an example to implement Java code in Oracle Database using the stored function. We can create Java programs in the Oracle database by using CREATE OR REPLACE AND COMPILE JAVA SOURCE statement. After that, we can…

Oracle FOR LOOP SELECT Statement Example

In this tutorial, I am giving an Oracle FOR LOOP SELECT statement example. You can use SELECT statement inside FOR LOOP in Oracle to loop through SELECT results using PL FOR LOOP IN SELECT Syntax FOR cursor_variable IN (select_statement) LOOP…

Oracle IF Condition Example

In Oracle PL/SQL, IF condition is used to perform a logical check on certain values. If the condition is TRUE or FALSE then executes the statements followed by that condition. In this blog post, I am giving an Oracle IF…

Oracle WHILE LOOP Example

In Oracle PL/SQL, WHILE LOOP statement executes the code written between WHILE LOOP and END LOOP until the condition is true. Below I am giving some examples for Oracle WHILE LOOP statement. Syntax WHILE logical_condition LOOP — some PL/SQL code END…

Oracle FOR LOOP REVERSE Example

In Oracle PL/SQL, FOR LOOP with REVERSE clause is used to repeat loop iteration in reverse order. The following are the syntax and examples for REVERSE FOR LOOP. Syntax FOR n IN REVERSE start_number .. end_number LOOP — statement to execute…

How to Compile All Invalid Packages in Schema?

Below is an example of PL/SQL program to compile all invalid package specifications and all invalid package bodies for the current user (SCHEMA) in Oracle. Also, if any of the invalid packages are not successfully compiled then it will print…

PL/SQL Program to Print Employee Details

In this tutorial, you will learn how to print employee details using PL/SQL program. PL/SQL Program to Print Employee Details In the following example, it will print the employee details for the employee number 7839, set in the declare section…

UTL_FILE.FREMOVE Example: Delete a File in Oracle

In Oracle PL/SQL, UTL_FILE.FREMOVE procedure is used to delete a file from the disk. This tutorial explains how to delete a file in Oracle PL/SQL using UTL_FILE.FREMOVE procedure with syntax and example. Syntax UTL_FILE.FREMOVE ( location IN VARCHAR2, filename IN…

Oracle PL/SQL: UTL_FILE.FCOPY Example

In Oracle PL/SQL, UTL_FILE.FCOPY procedure is used to copy a file. This article explains how to copy a file in PL/SQL using UTL_FILE.FCOPY procedure with syntax and examples. Syntax UTL_FILE.FCOPY ( src_location IN VARCHAR2, src_filename IN VARCHAR2, dest_location IN VARCHAR2, dest_filename…

How to Raise Exception in PL/SQL?

You can raise an error in PL/SQL using user-defined exception. Steps to Raise An Exception in PL/SQL Declare user-defined exception in the declare section of PL/SQL program unit. Raise it between the program upon some condition. Handle it in the…