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…

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…

In this tutorial, you will learn how to delete the records from a table in Oracle using PL In Oracle PL/SQL, we can delete the records using DELETE DML Statement and be using TRUNCATE DDL command. I am giving below…

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…

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…

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…

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…

In Oracle, For Loop is the easiest way to work with Cursor. It will open the cursor, fetch and closes by itself. Below are the examples to how to fetch data from Cursor in Oracle using For Loop. For Loop Cursor…

In Oracle utl_file.fopen is a function, which opens a file on the server by returning its file handle. Function utl_file.fopen parameters in Oracle are as follows: utl_file.fopen Syntax UTL_FILE.FOPEN ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize…

Below are the few quick examples to execute PL SQL block in Oracle. 1. Execute PL SQL Block in SQL Plus In SQL Plus, just put the forward slash (/) at the end of PL SQL block and press enter to…

An example is given here to raise application error (raise_application_error) in PL/SQL stored procedure if the certain condition does not match. The program will raise an exception if the commission (comm) is null in EMP table for given employee number…

In Oracle, you can execute a function with parameters via the following ways: 1. Execute The Function Using Select Statement SELECT get_emp_job (7566) FROM DUAL; Output GET_EMP_JOB(7566) ---------------------------------------- MANAGER 1 row selected. 2. Execute The Function Using PL/SQL Block SET…

Below is an example of a stored procedure in Oracle with IN OUT parameter. It will take employee number (i_empno) as IN parameter and will return employee name (o_ename) as OUT parameter. Stored Procedure IN-OUT Parameter Example CREATE OR REPLACE…

Executing a procedure inside a package in Oracle is just by giving package reference before the procedure. For example, your procedure name is Extract_Emp_Data in the package EMP_INTERFACE then execute it as follows: Execute a Procedure Inside a Package in…

In Oracle, you first need to declare the procedure in a package specification and then create the full procedure in the package body. Because if you will try to create procedure only in the package body without its declaration in…

CSV file is a comma delimited file in which fields are separated by the comma. In this article, I am giving an example to import CSV file into oracle table using stored procedure. The stored procedure in Oracle is a…

An example is given below to export data into Excel from Oracle table using PL SQL. You can do this task with the help of xlsx_builder_pkg package, which you can download from GITHUB using the following link click here. After…

PL SQL table types are actually collections, like an array. With the help of PL SQL collections, you can process bulk data efficiently. In this post, I will teach you very basic and most frequently used commands for PL SQL…